Sunday, April 08, 2012

ORA-01732

This was tested on Oracle 11. Sometimes you can update a view and sometimes you can't. In view1 below, there is a one to one correspondence between the rows in the view and the rows in the base table. If you update the view, Oracle goes to the underlying table and makes the appropriate changes there: 
 
SQL> create table table_list
  2  as select * from dba_tables
  3  /
 
Table created.
 
SQL> create view view1
  2  as select * from table_list
  3  /
 
View created.
 
SQL> update view1 set owner = 'ANDREW'
  2  /
 
3063 rows updated.
 
SQL> select owner, count(*) from table_list
  2  group by owner
  3  /
 
OWNER                            COUNT(*)
------------------------------ ----------
ANDREW                               3063
 
SQL> delete table_list
  2  /
 
3063 rows deleted.
 
SQL> insert into table_list
  2  select * from dba_tables
  3  /
 
3064 rows created.

SQL>

However, view2 includes a group by statement. Each row in this view has data collected from one or more rows in the base table. If you try to update it, Oracle has no idea how to carry any changes through to the base table so it generates an error message:
 
SQL> create view view2
  2  as select owner, count(*) number_present
  3  from table_list
  4  group by owner
  5  /
 
View created.
 
SQL> update view2 set number_present = 0
  2  /
update view2 set number_present = 0
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on
this view
 
SQL>

2 comments:

Anonymous said...

what info do I get, other than getting the error, please provide solution. Thanks

Andrew Stuart Reid said...

Good point. I will provide some solutions and put links to them from this post.