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
Solved

ORA-01733: virtual column not allowed here

Posted on 2001-07-25
9
17,959 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
'G_F01' is not a procedure or is undefined 3 34
Component is listed with a Protocol more than once 3 42
grant user/role question 11 32
Help with Oracle IF statment 5 25
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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