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