13 Replies Latest reply: Jun 8, 2009 5:32 AM by 566761 RSS

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

    566761
      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
          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
            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
              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
                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
                  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
                    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
                      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....
                        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
                          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
                            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
                              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
                                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
                                  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