Thursday, April 12, 2012

ORA-01779

Here is another example, tested on Oracle 11, where you cannot update a view. The view definition includes a join between two tables. If you were able to update it, the row concerned could be dropped from the view altogether as the join condition might no longer be satisfied. In this case, an ORA-01779 is returned:

SQL> create table table_list
  2  as select * from dba_tables
  3  /
 
Table created.
 
SQL> create table object_list
  2  as select * from dba_objects
  3  /
 
Table created.
 
SQL> create view view1 as
  2  select t.owner, t.table_name, o.object_id
  3  from table_list t, object_list o
  4  where t.owner = o.owner
  5  and t.table_name = o.object_name
  6  /
 
View created.
 
SQL> update view1 set owner = 'ANDREW'
  2  /
update view1 set owner = 'ANDREW'
                 *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non
key-preserved table
 
SQL>

No comments: