Solved

ORA-01733: virtual column not allowed here

Posted on 2001-07-25
9
18,331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 

Accepted Solution

by:
Kirilloff earned 100 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

628 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