Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 18966
  • Last Modified:

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...
0
jonnyfive
Asked:
jonnyfive
1 Solution
 
KirilloffCommented:
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.
0
 
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...
0
 
FBIAGENTCommented:
JONNYFIVE,

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.

SQL>
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fvaCommented:
Try this:

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

F.
0
 
stmontgoCommented:
watching your progess on this, I researched this and could not find a workaround, I am curious if one is suggested -)
0
 
sudhi022299Commented:
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

Regards,
Sudhi.
0
 
saschekCommented:
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
0
 
tvspriyaCommented:
If works perfectly for me. Here it is


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

Table created.

GSM521D5:TSARADA>CREATE VIEW CM_TEST_VIEW AS SELECT "Another Test" from cm_test;

View created.

GSM521D5:TSARADA>select * from cm_test_view;

no rows selected

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

1 row created.

GSM521D5:TSARADA>drop view CM_TEST_VIEW;

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.

GSM521D5:TSARADA>desc CM_TEST;
 Name                            Null?    Type
 ------------------------------- -------- ----
 Another Test                             VARCHAR2(50)

GSM521D5:TSARADA>desc CM_TEST_VIEW;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ANOTHER_TEST                             VARCHAR2(50)

GSM521D5:TSARADA>

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

SP
0
 
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.

Jonny...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now