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

Is it clear now ?

Coskan Gundogar

http://coskan.wordpress.com

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
 

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>