ORA-01732: data manipulation operation not legal on this view

Problem Description:
Oracle view DML operation fails with following error.
SQL> update vw_emp set ename ='xyz';
update vw_emp set ename ='xyz'
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> explain plan for update vw_emp set ename ='xyz';
explain plan for  update vw_emp set ename ='xyz'

                         *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
Solution Description
Below mentioned are the rules for the DML operations in Oracle views. If you try to violate these thumb rules you will get above error message.
1.   To allow UPDATE on View rows
a.   There should not be ROWNUM pseudo column in the view definition
b.   There should not be any group by, Aggregate function in the view definition
c.   There should not be DISTINCT key word in the view definition
d.   There should not be any expressions as columns.
2.   To allow DELETE on View
a.   There should not be ROWNUM pseudo column in the view definition
b.   There should not be any group by, Aggregate function in the view definition
c.   There should not be DISTINCT key word in the view definition
3.   To allow INSERT on a view
a.   There should not be ROWNUM pseudo column in the view definition
b.   There should not be any group by, Aggregate function in the view definition
c.   There should not be DISTINCT key word in the view definition
d.   There should not be any expressions as columns.

e.   There should not be any NOT NULL columns in the base tables that are not selected in the view definition
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google