Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-01733: virtual column not allowed here

Posted on 2001-07-25
9
Medium Priority
?
18,762 Views
Last Modified: 2011-08-18
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
Comment
Question by:jonnyfive
9 Comments
 

Accepted Solution

by:
Kirilloff earned 300 total points
ID: 6317819
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
 

Author Comment

by:jonnyfive
ID: 6317936
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
 
LVL 5

Expert Comment

by:FBIAGENT
ID: 6317991
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Expert Comment

by:fva
ID: 6317999
Try this:

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

F.
0
 
LVL 2

Expert Comment

by:stmontgo
ID: 6318020
watching your progess on this, I researched this and could not find a workaround, I am curious if one is suggested -)
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 6319326
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
 
LVL 1

Expert Comment

by:saschek
ID: 6319962
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
 
LVL 1

Expert Comment

by:tvspriya
ID: 6320761
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
 

Author Comment

by:jonnyfive
ID: 6321486
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question