ORA-01733: virtual column not allowed here

Hi all,

I try something rather simple and are stunned that this doesn't work.

Here is a quick test setup:

Create a table called cm_test with one column (varchar2[50]) and call it "Another Test".

Then create a view like this:
select 'Another Test' as Another_Test from cm_test

This works perfectly till trying to update this view in anyway (update/insert/delete), where I get the error stated (ORA-01733: virtual column not allowed here). I understand that I can't insert values into a view that uses "calculated" fields, but this mapps 1 to 1 back to the table. Taking the space out of the table column name solves (obviously) the problem, but that is not a option for me as I have to work with 3rd party tables. Is there any way to persuede Oracle to do what I want?

Cheers, Jonny...
Who is Participating?
KirilloffConnect With a Mentor Commented:
I think it is because of single qoute marks instead of double quote marks in the view definition. Your view contains single column that contains constant 'Another Test'. It is expression and Orace cannot modify it. Try to change quote marks.
jonnyfiveAuthor Commented:
Sorry no joy. I already had double quotes in my test view, so I tried it with single qoutes: Same result.

By the way, do not answer a question if you just make a suggestion. I'm happy to give you the points even on a comment if it worked :)

Cheers, Jonny...

I don't know why you were not able to INSERT/UPDATE/DELETE from the view.  It worked for me here; either with Oracle 7.3.x and Oracle8i

SQL> create table thomas_test ("Thomas Test" varchar2(30));

Table created.

SQL> create view v_thomas_test as select "Thomas Test" as Thomas_Test from thomas_test;

View created.

SQL> desc v_thomas_test
 Name                            Null?    Type
 ------------------------------- -------- ----
 THOMAS_TEST                              VARCHAR2(30)

SQL> insert into v_thomas_test values ('this is a test');

1 row created.

SQL> rollback;

Rollback complete.

SQL> drop view v_thomas_test;

View dropped.

SQL> drop table thomas_test;

Table dropped.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Try this:

create or replace the_view as
select cmt."Another Test" Another_Test from cm_test cmt;

watching your progess on this, I researched this and could not find a workaround, I am curious if one is suggested -)
Hi Jonny,

I agree with fbiagent. As long as the column name is specified in " ( double quotes ) wherever it is referenced update/insert/delete statements it should work fine. I'll paste a sample update statement :

SQL> create table sudhi("sudhi col" varchar2(20));

Table created.

SQL> create view sudhi_view as select "sudhi col" from sudhi;

View created.

SQL> desc sudhi_view;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 sudhi col                                          VARCHAR2(20)

SQL> insert into sudhi_view values ('hello');

1 row created.

SQL> commit;

Commit complete.

SQL> update sudhi_view set "sudhi col"='hell';

1 row updated.

If I have not understood the Q correctly, then would like to see your complete set of statements which errors out with ora-1733

Same to me (Oracle 8.1.7):
Using single qoutes I get ORA-01733 because a constant seems to be like a calculation.

But using " ( double quotes ) - no problems with all DML&Select

Have you really tested double quotes? Test it again:
create or replace view TEST as select "Another Test" Another_Test from cm_test;
-- Sven
If works perfectly for me. Here it is

GSM521D5:TSARADA>Create table cm_test ("Another Test" VARCHAR2(50));

Table created.


View created.

GSM521D5:TSARADA>select * from cm_test_view;

no rows selected

GSM521D5:TSARADA>insert into cm_test_view values ('sarada');

1 row created.


View dropped.

GSM521D5:TSARADA>CREATE VIEW CM_TEST_VIEW AS SELECT "Another Test" as Another_Test from cm_test
  2  /

View created.

GSM521D5:TSARADA>insert into cm_test_view values ('sarada');

1 row created.

 Name                            Null?    Type
 ------------------------------- -------- ----
 Another Test                             VARCHAR2(50)

 Name                            Null?    Type
 ------------------------------- -------- ----
 ANOTHER_TEST                             VARCHAR2(50)


You refer the column always with double quotes, it should be fine.

jonnyfiveAuthor Commented:
Hi everyone,

the problem is finally solved and here is what I found:

The problem seems to be really due to the single quotes. The reason why I used them instead of doubles was, that Oracle told me that it couldn't find the specified column name. I.e. if the column name was "Another Test" (Entered this way into the table designer in DBA Studio), it turns out that all column names have been converted into all capitals ("ANOTHER TEST"). So saying 'select "Another Test" from cm_test"' gave the error that the column wasn't found, while 'select 'Another Test' from cm_test' worked (well, for the select, but not for the insert).

Since it was Kirilloff who pointed this out first, I decided to give him the points. Thanks everyone for your support.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.