This discussion is archived
13 Replies Latest reply: Jun 8, 2009 3:32 AM by 566761 RSS

ORA-01733- virtual column not allowed here  - Insert using inline view

566761 Journeyer
Currently Being Moderated
Does anyone know why I am getting ORA-01733- virtual column not allowed here

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production



---no error without WITH CHECK option

SQL> INSERT INTO
2 (SELECT
3 location_id,
4 city,
5 l.country_id
6 FROM countries c, locations l,regions r
7 where l.country_id = c.country_id
8 and r.region_id=c.region_id
9 and r.region_name = 'Asia')
10 VALUES (5500, 'Wansdworth Common', 'UK');

1 row created.

SQL> rollback;

Rollback complete.


-----error with WITH CHECK OPTION


SQL> INSERT INTO
2 (SELECT
3 location_id,
4 city,
5 l.country_id
6 FROM countries c, locations l,regions r
7 where l.country_id = c.country_id
8 and r.region_id=c.region_id
9 and r.region_name = 'Asia' WITH CHECK OPTION)
10 VALUES (5500, 'Wansdworth Common', 'UK');
INSERT INTO
*
ERROR at line 1:
ORA-01733: virtual column not allowed here




I was expecting

ORA-01402: view WITH CHECK OPTION where-clause violation

for the second one. Is there anything I am missing here ?
  • 1. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    673777 Explorer
    Currently Being Moderated
    Hi,

    This may help you

    [http://www.error-code.org.uk/view.asp?e=ORACLE-ORA-01733]

    [http://www.dba-oracle.com/t_with_check_option.htm]

    Thanks
  • 2. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    566761 Journeyer
    Currently Being Moderated
    Thank you for the reply but the link you send are not the solution. Problem is why it works without with check option and why it doesnt work with check option.
  • 3. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    SomeoneElse Guru
    Currently Being Moderated
    I don't have an answer for you, but I can confirm the exact same behavior in 10.2.0.4.

    The example in the SQL Reference manual (under SELECT) works fine though.
  • 4. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    617150 Newbie
    Currently Being Moderated
    Hi Coskan,
    You use inline complex view in your insert statement so you may check view definition:

    If you want a join view to be updatable, then all of the following conditions must be true:
    * The DML statement must affect only one table underlying the join.
    * For an INSERT statement, the view must not  be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
    * For an UPDATE statement, the view must not be created WITH CHECK OPTION, and all columns updated must be extracted from a key-preserved table.

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_8004.htm#sthref7766

    Edited by: KAYSERI on Mar 30, 2009 1:39 PM
  • 5. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    566761 Journeyer
    Currently Being Moderated
    Thanks Kayseri

    but the problem is the one below works but mine isnt and it fits everything you referenced (taken from doc modified by adding hr.locations)


    SQL> INSERT INTO (SELECT d.department_id, d.department_name, d.location_id
    2 FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000 WITH CHECK OPTION)
    3 VALUES (9999, 'Entertainment', 2500);
    FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000 WITH CHECK OPTION)
    *
    ERROR at line 2:
    ORA-01402: view WITH CHECK OPTION where-clause violation


    this is the errror I am expection but couldnt get yet


    I think this is a bug
  • 6. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    617150 Newbie
    Currently Being Moderated
    Hi again,
    Let's look at "With Check Option"definition from same link above:
    Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery.

    Let me give an example, first we will insert one record without "WITH CHECK OPTION"
    SQL> INSERT INTO (SELECT d.department_id, d.department_name, d.location_id
    2 FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000)
    3 VALUES (9999, 'Entertainment', 2500);

    1 row inserted

    After inserting record, let's query the inline view, and check if new record can be retrieved;
    SQL> SELECT d.department_id, d.department_name, d.location_id
    2 FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000
    3 and d.location_id=9999;

    no rows selected

    If we won't be able to select, then we can't insert using "WITH CHECK ONLY", let's see:
    rollback;
    SQL> INSERT INTO (SELECT d.department_id, d.department_name, d.location_id
    2 FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000 WITH CHECK OPTION)
    3 VALUES (9999, 'Entertainment', 2500);

    ORA-01402: view WITH CHECK OPTION where-clause violation


    Let's insert a record that can be retrieved:

    SQL> INSERT INTO (SELECT d.department_id, d.department_name, d.location_id
    2 FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000 WITH CHECK OPTION)
    3 VALUES (9999, 'Entertainment', 1400);

    1 row created.

    SQL> SELECT d.department_id, d.department_name, d.location_id
    2 FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000
    3 and d.department_id=9999;

    DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID

    9999 Entertainment 1400

    I hope this helps,
    A.D
  • 7. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    566761 Journeyer
    Currently Being Moderated
    sorry mate it couldnt help because my main problem is Why getting ORA-01733- instead of ORA-01402:


    Is it clear now ?

    Coskan Gundogar

    http://coskan.wordpress.com
  • 8. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    Aman.... Oracle ACE
    Currently Being Moderated
    Coskan,

    I ran the above statement in 10201 , Win Xp Proff , and I did get the error as mentioned.
    SQL> conn hr/hr
    Connected.
    SQL>  INSERT INTO (SELECT d.department_id, d.department_name, d.location_id
      2   FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000 WITH CHECK OPTION
      3   VALUES (9999, 'Entertainment', 2500);
     FROM hr.departments d,hr.locations l WHERE l.location_id=d.location_id and d.location_id < 2000 WITH CHECK OPTION)
             *
    ERROR at line 2:
    ORA-01402: view WITH CHECK OPTION where-clause violation
    
    
    SQL> select * from V$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL>
    I guess , in the original question, some how the miss of the coutry_id is causing the error message to come. I need to check back but this error, virtual column not allowed, does come up with one more situation. And surely the error message doesn't make much sense. I shall try to chase it and let you know if I shall find something useful.

    I am reading your notes on sql expert. SQL is harder than oracle kernel too for me :( .

    regards
    Aman....
  • 9. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    afd Explorer
    Currently Being Moderated
    a possible workaround is:
    SQL>
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    PL/SQL Release 11.1.0.6.0 - Production
    CORE    11.1.0.6.0      Production
    TNS for Linux: Version 11.1.0.6.0 - Production
    NLSRTL Version 11.1.0.6.0 - Production
    
    SQL> INSERT INTO
      2  (SELECT
      3   l.location_id,
      4   l.city,
      5   l.country_id
      6   FROM  locations l
      7   where
      8    exists (select null from regions r, countries c
      9           where
     10           c.country_id = l.country_id
     11           and r.region_id = c.region_id
     12           and r.region_name = 'Asia'
     13           )
     14   with check option
     15   )
     16   VALUES (5500, 'Wansdworth Common', 'UK');
     FROM  locations l
           *
    ERRORE alla riga 6:
    ORA-01402: violazione nella clausola WHERE della vista con WITH CHECK OPTION
    hope this helps,
    Tony
  • 10. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    coskan wrote:
    sorry mate it couldnt help because my main problem is Why getting ORA-01733- instead of ORA-01402:
    Coskan,

    I think your issue could be that you're attempting to manipulate one of the columns of a joined table that has "repeated" values, and that doesn't seem to be allowed when using the WITH CHECK OPTION with join views. I don't think that this is specific to an inline view, but a general limitation of WITH CHECK OPTION join views.

    Unfortunately the error message is a bit misleading.

    That's probably the reason why your second example that is only selecting columns from department gets the "expected" error message (ORA-01402).

    See e.g. here, chapter 3.5.6: http://etutorials.org/SQL/Mastering+Oracle+SQL/Chapter+3.+Joins/3.5+DML+Statements+on+a+Join+View/

    What do you get if you create a view and check the USER_UPDATABLE_COLUMNS dictionary view for the view created?

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 11. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    566761 Journeyer
    Currently Being Moderated
    Randolf

    Thank you very much for the update to this old question
    After reading the link I think I should ignore this error and accept it as ORA-01402

    The information you asked me to check did not lead me an understanding of different error types.
    SQL> ----view for ORA-01733
    SQL> create view test_v_1
      2  as
      3  SELECT
      4  location_id,
      5  city,
      6  l.country_id
      7  FROM countries c, locations l,regions r
      8  where l.country_id = c.country_id
      9  and r.region_id=c.region_id
     10  and r.region_name = 'Asia' WITH CHECK OPTION;
    
    View created.
    
    SQL>
    SQL>
    SQL>
    SQL> select * from user_updatable_columns where table_name='TEST_V_1';
    
    OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
    ------------------------------ ------------------------------ ------------------------------ --- --- ---
    HR                             TEST_V_1                       CITY                           YES YES YES
    HR                             TEST_V_1                       COUNTRY_ID                     NO  NO  NO
    HR                             TEST_V_1                       LOCATION_ID                    YES YES YES
    
    SQL>
    SQL> ----view for ORA-01402
    SQL>
    SQL> create view test_v_2
      2  as
      3  SELECT
      4  d.department_id,
      5  d.department_name,
      6  d.location_id
      7  FROM hr.departments d,hr.locations l
      8  WHERE l.location_id=d.location_id
      9  and d.location_id < 2000
     10  WITH CHECK OPTION;
    
    View created.
    
    SQL>
    SQL> select * from user_updatable_columns where table_name='TEST_V_2';
    
    OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
    ------------------------------ ------------------------------ ------------------------------ --- --- ---
    HR                             TEST_V_2                       DEPARTMENT_ID                  YES YES YES
    HR                             TEST_V_2                       DEPARTMENT_NAME                YES YES YES
    HR                             TEST_V_2                       LOCATION_ID                    NO  NO  NO
    
    SQL>
    SQL>
    SQL> ----INSERT STILL FAILING WITH DIFFERENT ERROR DESPITE THE SAME UPDATABLE COLUMN STRUCTURE
    SQL> insert into test_v_1 values  (5500, 'Wansdworth Common', 'UK');
    insert into test_v_1 values  (5500, 'Wansdworth Common', 'UK')
    *
    ERROR at line 1:
    ORA-01733: virtual column not allowed here
    
    
    SQL> insert into test_v_2 values  (9999, 'Entertainment', 2500);
    insert into test_v_2 values  (9999, 'Entertainment', 2500)
                *
    ERROR at line 1:
    ORA-01402: view WITH CHECK OPTION where-clause violation
    
    
    SQL>
    A. Coskan GUNDOGAR

    Oracle DBA

    http://coskan.wordpress.com

    “A man's errors are his portals of discovery.”

    James Joyce
  • 12. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    coskan wrote:
    Randolf

    Thank you very much for the update to this old question
    After reading the link I think I should ignore this error and accept it as ORA-01402

    The information you asked me to check did not lead me an understanding of different error types.
    Coskan,

    I didn't realize that is an old thread that somehow got updated by someone else.

    Regarding your particular issue: You're right that the output from the script is not really that helpful.

    I've just run this on 10.2.0.4 and in general it looks like the output from USER_UPDATABLE_COLUMNS is incorrect with regards to join views using the WITH CHECK OPTION.

    E.g. although the column LOCATION_ID of TEST_V_2 is shown as non-modifiable (probably due to the rule that "columns used in the join expression" can not be modified in a join view when using the WITH CHECK OPTION) I can successfully run your insert statement if I choose a LOCATION_ID less than 2000.

    It looks like that it seems to boil down to the difference that if you join more than two tables you'll always get the "ORA-01733" error when attempting to insert into the join view with the CHECK OPTION enabled. E.g. adding a third table to TEST_V_2 that doesn't change the original meaning of the view, but simply joins e.g. COUNTRIES to the LOCATIONS, will show the same behaviour of throwing a ORA-01733, it works however fine when omitting the WITH CHECK OPTION.

    So all in all I tend to say this is really a implementation limitation, and it's not actually an ORA-01402 but it looks like Oracle simply tries tell you: No INSERT modification to this view possible. Updates however seem to work, at least I can find some working examples.

    There seem to be other implementation restrictions with the WITH CHECK OPTION in place even when joining only two tables, e.g. when attempting to join COUNTRIES and LOCATIONS but selecting only from LOCATIONS and using a filter predicate on the COUNTRY_ID it fails with ORA-01733 when this predicate is applied to COUNTRIES.COUNTRY_ID but it works if this predicate is applied to LOCATIONS.COUNTRY_ID.

    I could imagine that Oracle silently has added some of these restrictions with each patch set due to corner cases/side effects encountered. I've seen this for other features, too.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 13. Re: ORA-01733- virtual column not allowed here  - Insert using inline view
    566761 Journeyer
    Currently Being Moderated
    It looks like that it seems to boil down to the difference that if you join more than two tables >you'll always get the "ORA-01733" error when attempting to insert into the join view with the >CHECK OPTION enabled. E.g. adding a third table to TEST_V_2 that doesn't change the original >meaning of the view, but simply joins e.g. COUNTRIES to the LOCATIONS, will show the same >behaviour of throwing a ORA-01733, it works however fine when omitting the WITH CHECK >OPTION.
    I think you are right about the third table join issue.
    I have my question answered

    Thank you very much Randolf

    A. Coskan GUNDOGAR

    Oracle DBA

    http://coskan.wordpress.com

    “Thinking is more interesting than knowing, but less interesting than looking”

    Johann Wolfgang von Goethe

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points