help understanding an ORA-01732 error with inline view
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 21 Oct 2011 11:39:13 -0500
I was recently adding some enhancements to an in-house, DBA Utility
application, which uses numerous sessions to process large numbers of table
segments concurrently. While I was doing this, I noticed that it used LOCK
TABLE IN EXCLUSIVE MODE to serialize access to the pseudo-queue of objects
to be processed... the logic being, I assume, that it would remain locked
only for a very brief period. The original processing looked something like
this:
lock table TAB in exclusive mode;
select KEY_COL, STUFF_I_NEED, ...
into KEY_VAR, VAR1, ...
from TAB
where ... and rownum = 1
order by PRIORITY;
update TAB
set STATUS = 'Yoiks, and away!'
where KEY_COL = KEY_VAR;
commit;
-- go do the real work now
"Challenge accepted!", quoth I, and immediately swore to purge the dragon of
gratuitous locking from the codebase. <g> I decided to replace it with:
update TAB ...
returning STUFF_I_NEED, ... into VAR1, ...;
commit;
While trying to make this work, however, I kept encountering an ORA-01732
error which I don't fully understand. I already have a viable workaround,
so the question is purely academic at this point, but I was hoping that you
all can help to explain it to me. It might well be a case of "expected
behaviour... don't do it like that!", but thus far I haven't found anything
which explicitly covers the scenario.
Here's my simplified test case, which was run under 11.2.0.2 (Linux x86-64):
- Create a test account.
SYSTEM@testdb> create user test identified by test
2 quota unlimited on users;
User created.
SYSTEM@testdb> grant create session, create table to test;
Grant succeeded.
- Setup the test schema.
TEST@testdb> create table stuff_to_process (
2 owner varchar2(30) not null,
3 table_name varchar2(30) not null,
4 partition_name varchar2(30),
5 priority number(2) default 1 not null,
6 status varchar2(15),
7 constraint priority_ck check (priority > 0),
8 constraint stuff_to_process_uk unique (owner, table_name,
partition_name)
9 );
Table created.
TEST@testdb> create index status_priority_ix on stuff_to_process (status,
priority);
Index created.
TEST@testdb> insert into stuff_to_process (owner, table_name,
partition_name)
2 values ('BELLDANDY', 'CONTRACTS', 'KEIICHI');
1 row created.
TEST@testdb> insert into stuff_to_process (owner, table_name,
partition_name)
2 values ('SNIDELY', 'WHIPLASH', 'TRAINTRACKS');
1 row created.
TEST@testdb> insert into stuff_to_process (owner, table_name)
2 values ('BILL', 'BORED');
1 row created.
TEST@testdb> commit;
Commit complete.
- This is the point where the error manifests itself.
TEST@testdb> update (select * from stuff_to_process x
2 where x.status is NULL
3 order by x.priority asc
4 ) t
5 set t.status = 'ThunderQuack'
6 where rownum = 1;
update (select * from stuff_to_process x
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
- Apparently it's triggered by the ORDER BY clause of the inline view.
Removing it isn't really a viable workaround, however, since I need to
process items by priority.
TEST@testdb> update (select * from stuff_to_process x
2 where x.status is NULL
3 -- order by x.priority asc
4 ) t
5 set t.status = 'ThunderQuack'
6 where rownum = 1;
1 row updated.
- Since this appears to be a key-preserved resultset, why does the
ordering even matter?
TEST@testdb> select nvl(status, 'NULL') status, rowid
2 from (select * from stuff_to_process x
3 where x.status is NULL
4 order by x.priority asc
5 )
6 where rownum = 1;
STATUS ROWID
--------------- ------------------
NULL AAAmHwAAEAAAACDAAA
For the curious, the working version is:
TEST@testdb> update (select * from stuff_to_process x
2 where x.status is NULL
3 and x.priority = (select min(x2.priority) from
stuff_to_process x2
4 where x2.status is NULL)
5 ) t
6 set t.status = 'ThunderQuack'
7 where rownum = 1;
1 row updated.
--
"I'm too sexy for my code." -Awk Sed Fred
--
//www.freelists.org/webpage/oracle-l
Other related posts:
