Updating a table through a view having ROWNUM check
Reader, March 08, 2007 - 8:55 am UTC
Hi,
If we want to update the sal by 1000, of the employee having the highest empid (i.e. the employee who joined the company latest).
CREATE TABLE emp AS SELECT 1 empid, 1000 sal FROM dual;
INSERT INTO emp (2, 3000);
We wrote the following update query and it worked as desired:
Query1:
UPDATE (SELECT * FROM emp ORDER BY empid DESC)
SET sal = sal + 1000
WHERE ROWNUM = 1;
However, this didn't work as desired when we inserted another record into emp:
INSERT INTO emp (3, 5000);
and fire the same update query from the same session.
So, we tried to change the query as below:
Query2:
UPDATE (SELECT * FROM (SELECT * FROM emp ORDER BY empid DESC) WHERE ROWNUM = 1)
SET sal = sal + 1000;
The above throws an error, indicating that this update is illegal.
In this context, the Oracle documentation (9i and 10gR1) says:
"If an inherently updatable view contains pseudocolumns or expressions, you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions".
Taking this into consideration, both the ROWNUM forms of the query should have been allowed:
Query1: Here the view doesn't contain the pseudocolumn ROWNUM. But, the update doesn't work faithfully always.
Query2: Here the view does contain the pseudocolumn ROWNUM, but the UPDATE statement doesn't refer to the pseudocolumn. But, this query throws error.
Our intention could well be solved by re-writing the query like below:
UPDATE emp
SET sal = sal + 1000
WHERE empid = (SELECT MAX(empid) FROM emp);
However, we want to understand why the ROWNUM queries are not working. How is Oracle reacting to these queries internally?
March 08, 2007 - 11:07 am UTC
sorry - but your example is wrong. That update will not work regardless of the number of rows in the table
ops$tkyte%ORA10GR2> CREATE TABLE emp (empid number, sal number);
Table created.
ops$tkyte%ORA10GR2> UPDATE (SELECT * FROM emp ORDER BY empid DESC)
2 SET sal = sal + 1000
3 WHERE ROWNUM = 1;
UPDATE (SELECT * FROM emp ORDER BY empid DESC)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this viewYou cannot update that result set. It would be rather meaningless and ambiguous if you could. You would be updating in effect "a random row" - the same update (if it worked) against the same data - could end up affecting DIFFERENT ROWS (eg: it is not deterministic)
You cannot update that view with rownum as rownum is not deterministically assigned to rows.
ops$tkyte%ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 2, 1 );
1 row created.
ops$tkyte%ORA10GR2> select x, rownum
2 from (select * from t order by y)
3 /
X ROWNUM
---------- ----------
2 1
1 2
ops$tkyte%ORA10GR2> truncate table t;
Table truncated.
ops$tkyte%ORA10GR2> insert into t values ( 2, 1 );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> select x, rownum
2 from (select * from t order by y)
3 /
X ROWNUM
---------- ----------
1 1
2 2
Reader, March 08, 2007 - 11:47 pm UTC
Hi,
We are not sure why our first query didn't work for you. We think it could well be the DB version that matters here.
We tried this on SQL*Plus: Release 9.0.1.0.1 and it worked without throwing an error saying the DML is not legal, although the UPDATE was not always happening to the row desired (i.e. the one with MAX(empid)) in the base table. It was just as random:
UPDATE (SELECT * FROM emp ORDER BY empid DESC)
SET sal = sal + 1000
WHERE ROWNUM = 1;
However, we faced the error saying the DML is illegal with the second query:
UPDATE (SELECT * FROM (SELECT * FROM emp ORDER BY empid DESC) WHERE ROWNUM = 1)
SET sal = sal + 1000;
Could you please kindly check this on 9i and kindly confirm? Probably, from 10g onwards Oracle had incorporated a block on such operation (query1).
Now, if the 1st query works (like it did for us), our question is why it doesn't work faithfully, although the following (Top-N SELECT) always fetches for us the correct row:
SELECT * FROM (SELECT * FROM emp ORDER BY empid DESC)
WHERE ROWNUM = 1;
Apart from performing an UPDATE (as against just fetch in the above query), our query1 should first fetch the row just like this one does. Isn't it?
Kindly rectify us if our understanding is incorrect.
March 09, 2007 - 11:07 am UTC
that query should never have worked in any release. If it did, that is a bug.
scott%ORA9IR2> update (select * from emp order by sal desc)
2 set sal = sal
3 where rownum = 1;
update (select * from emp order by sal desc)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
Update through view
Reader, March 12, 2007 - 1:07 am UTC
Hi,
Thanks a lot for your explanation. This is where it worked for us:
SQL*Plus: Release 9.0.1.0.1 - Production on Mon Mar 12 11:21:25 2007
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table emp_ex (empid number primary key, sal number not null);
Table created.
SQL> insert into emp_ex values(1, 1000);
1 row created.
SQL> insert into emp_ex values(2, 3000);
1 row created.
SQL> select * from emp_ex;
EMPID SAL
---------- ----------
1 1000
2 3000
SQL> update (select * from emp_ex order by empid desc)
2 set sal = sal + 1000
3 where rownum = 1;
1 row updated.
SQL> select * from emp_ex;
EMPID SAL
---------- ----------
1 2000
2 3000
So, the row with highest empid was not updated in this case as per our desire.
However, when we tried the same, this time inserting the rows in a different order, it worked differently:
SQL*Plus: Release 9.0.1.0.1 - Production on Mon Mar 12 11:27:36 2007
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table emp_ey (empid number primary key, sal number not null);
Table created.
SQL> insert into emp_ey values (2, 3000);
1 row created.
SQL> insert into emp_ey values (1, 1000);
1 row created.
SQL> select * from emp_ey;
EMPID SAL
---------- ----------
2 3000
1 1000
SQL> update (select * from emp_ey order by empid desc)
2 set sal = sal + 1000
3 where rownum = 1;
1 row updated.
SQL> select * from emp_ey;
EMPID SAL
---------- ----------
2 4000
1 1000
So, the ROWNUM is not deterministically assigned in this case, as you said, even though it does in the following:
SQL> select * from (select * from emp_ey order by empid desc)
2 where rownum = 1;
EMPID SAL
---------- ----------
2 4000
Thanks.
March 12, 2007 - 8:24 pm UTC
like I said - if it ever worked - that was the bug. that it "worked", it doesn't make sense to do this.
basically - if it worked - given the same data in two different table (same sets of rows) the same update could update DIFFERENT ROWS. Placing this into the category of a horribly bad idea.
Updating through view
Reader, March 12, 2007 - 1:33 am UTC
Am sorry, we didn't notice that we actually tried this on 10g and not 9i.
We had mistaken the SQL Plus version for the DB version. Apologies.
March 12, 2007 - 8:27 pm UTC
oh, and in your last example, it is NOT deterministically assigned!!!!
ops$tkyte%ORA10GR2> create table t1( empid number, sal number );
Table created.
ops$tkyte%ORA10GR2> create table t2( empid number, sal number );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values ( 4000, 123 );
1 row created.
ops$tkyte%ORA10GR2> insert into t1 values ( 4000, 567 );
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values ( 4000, 567 );
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values ( 4000, 123 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from (select * from t1 order by empid desc) where rownum = 1;
EMPID SAL
---------- ----------
4000 123
ops$tkyte%ORA10GR2> select * from (select * from t2 order by empid desc) where rownum = 1;
EMPID SAL
---------- ----------
4000 567
see - same "data" in both tables - different rows selected
both result sets - absolutely correct however, just different.
same inputs, different outputs - not deterministic.
Updating through view
Reader, March 13, 2007 - 1:23 am UTC
Hi,
Thanks a lot for your such wonderful explanation.
Just a bit more curiosity - Would like to know what you feel about the statement in the Oracle documentation regarding this that I quoted in my first post on this thread?
"If an inherently updatable view contains pseudocolumns or expressions, you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions."
Following our discussion in length so far, don't you feel the above statement is probably not complete or rather misleading due to the reasons quoted in my first post here?
Don't know if this was a silly question! Probably, I misinterpreted the statement, did I?
After going through various texts on ROWNUM, what my understanding says is somehwat like this:
I go to a large bookstore (TABLE) to buy some 10 costly books (ROWS) out of lacs of books on display. Each book is kept in a particular location (ROWID), which is pretty well known to the store-keeper. When I start seeking for the books I want, I don't know in what order I will get them (if they are not already ordered). Probably, I will get first the book 5th in my list. I could have got the books deterministically in the order I desired (without the books ordered already as per my desire), if I would have known their locations (ROWIDs).
Now, as and when, I get a book of my choice, I mark them as 1,2,3... and so on. These numbers I assign only after I pick up one book after the other and not when they were there in the shelf. So, I won't say, this is the second book that I will buy, if I have not yet picked up (or at least, chosen) the first I want to buy.
Now, after picking up all my books, say, I had a bad bad intention suddenly in my mind, that I will quietly replace these books with cheap copies I have with me rather than to pay so much for them. I will, then, surely have a problem to find out exactly wherefrom I had picked up each of the books and I might end up putting the copies just as randomly into the locations I had emptied.
March 13, 2007 - 11:20 am UTC
you are "a reader"
I have no clue what "your" first post is.
I will say the quote is misleading - it should say "referenced in the set clause", I can put a where clause on an expression just fine.
Update through a view
Reader, March 13, 2007 - 9:10 am UTC
Hi Tom,
Just noticed in the documentation, that an updatable view must not contain an ORDER BY clause (among other constructs). So, that eliminates all confusions.
Such a query shouldn't have been allowed on the first place.
Thanks.
Update through a view
Reader, March 14, 2007 - 1:20 am UTC
Hi Tom,
The documentation also says that an inherently updatable view should not contain an aggregate or analytic function. However, this query works for us:
UPDATE (SELECT * FROM emp WHERE empid = (SELECT MAX(empid) FROM emp))
SET sal = sal + 1000;
just as identically and deterministically as this one does:
UPDATE (SELECT * FROM emp)
SET sal = sal + 1000
WHERE empid = (SELECT MAX(empid) FROM emp);
SQL*Plus: Release 9.0.1.0.1 - Production on Wed Mar 14 10:48:40 2007
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from emp_ex;
EMPID SAL
---------- ----------
1 4000
2 3000
SQL> select * from emp_ey;
EMPID SAL
---------- ----------
2 4000
1 1000
SQL> update (select * from emp_ex where empid = (select max(empid) from emp_ex))
2 set sal = sal + 500;
1 row updated.
SQL> select * from emp_ex;
EMPID SAL
---------- ----------
1 4000
2 3500
SQL> update (select * from emp_ex)
2 set sal = sal + 500
3 where empid = (select max(empid) from emp_ex);
1 row updated.
SQL> select * from emp_ex;
EMPID SAL
---------- ----------
1 4000
2 4000
SQL> update (select * from emp_ey where empid = (select max(empid) from emp_ey))
2 set sal = sal + 500;
1 row updated.
SQL> select * from emp_ey;
EMPID SAL
---------- ----------
2 4500
1 1000
SQL> update (select * from emp_ey)
2 set sal = sal + 500
3 where empid = (select max(empid) from emp_ey);
1 row updated.
SQL> select * from emp_ey;
EMPID SAL
---------- ----------
2 5000
1 1000
Thanks.
March 14, 2007 - 7:40 am UTC
the view itself of emp
SELECT * FROM emp WHERE empid = (SELECT MAX(empid) FROM emp))
did not contain any aggregates. The predicate - different story all together, the subquery is rather "not relevant" to the selected datas updatability.
Why does this produce an ORA-1732?
Raj, May 11, 2011 - 3:13 pm UTC
Hi Tom,
When I create a view and modify it using a utl_raw where clause as the schema owner, I don't see an issue (10.2.0.4.0 AIX5.3):
connect /as sysdba
create user user1 identified by user1 default tablespace admin quota unlimited on admin;
grant connect to user1;
grant create table to user1;
grant create view to user1;
connect user1/user1
create table user1.r (id number(5),iraw raw(10));
insert into user1.r values (1,utl_raw.cast_to_raw('abc'));
insert into user1.r values (2,utl_raw.cast_to_raw('def'));
insert into user1.r values (3,utl_raw.cast_to_raw('ghi'));
commit;
create or replace view user1.r_v as select * from user1.r;
update user1.r_v set id = '99' where to_number(utl_raw.bit_and(iraw,'2')) != 2;
3 rows updated.
rollback;
But if I create another user and grant it permissions to update one column of the same table, while able to select both, the 2nd user receives an ORA-1732:
connect /as sysdba
create user user2 identified by user2 default tablespace admin quota unlimited on admin;
grant connect to user2;
grant select on user1.r_v to user2;
grant update (id) on user1.r_v to user2;
connect user2/user2
update user1.r_v set id = '99' where to_number(utl_raw.bit_and(iraw,'2')) != 2;
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
Can you help me to understand what update is being attempted by the utl_raw function that I can't see?
May 12, 2011 - 7:34 am UTC
See support note: (Doc ID 420777.1)
It describes this - it'll happen when you use a user defined function in the view. It could be a security hole if you have a user defined function on or in a view. The user defined function in the where clause might "see" rows that it should not see if it is merged into the view itself.
The support note discusses this further and offers up ways to disable the new behavior if you desire.