advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment