My Oracle Support Banner

Update Statement or DML On a View Fails in 10g with ORA-01732 (Doc ID 420777.1)

Last updated on AUGUST 10, 2018

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2
This problem can occur on any platform.

Symptoms

Update statement which used to work fine in Oracle 8i Fails in 10gR2 ORA-01732

.
As user A
~~~~~~~~~~

-> Create a view
SQL> create view emp_dept as
2 select empno, ename, e.deptno, dname from emp e, dept d
3 where e.deptno=d.deptno
4 /


-> Create a function
SQL> create or replace
2 function to_upper (ename in varchar2(200)
3 return varchar2
4 ret_str varchar2(200);
5 is
6 begin
7 ret_str:=upper(ename);
8 return ret_str;
9 end;
10 /

-> Grant privileges on the objects to user B

SQL> grant select, update on emp_dept to B;


SQL> grant execute on to_upper to B;


As user B
~~~~~~~~~
SQL> update B.emp_dept set empno=empno where ename=B.to_upper(ename);
update B.emp_dept set empno=empno where ename=B.to_upper(ename)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.