help understanding an ORA-01732 error with inline view

From: Adric Norris <landstander668_at_gmail.com>
Date: Fri, 21 Oct 2011 11:39:13 -0500
Message-ID: <CAJueESpF6egPztNy35ACR0z0c1GsAO4dKHwUwMtsEjePnyJGkw_at_mail.gmail.com>

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_at_testdb> create user test identified by test   2 quota unlimited on users;

User created.

SYSTEM_at_testdb> grant create session, create table to test;

Grant succeeded.

• Setup the test schema.

TEST_at_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_at_testdb> create index status_priority_ix on stuff_to_process (status, priority);

Index created.

TEST_at_testdb> insert into stuff_to_process (owner, table_name, partition_name)
  2 values ('BELLDANDY', 'CONTRACTS', 'KEIICHI');

1 row created.

TEST_at_testdb> insert into stuff_to_process (owner, table_name, partition_name)
  2 values ('SNIDELY', 'WHIPLASH', 'TRAINTRACKS');

1 row created.

TEST_at_testdb> insert into stuff_to_process (owner, table_name)   2 values ('BILL', 'BORED');

1 row created.

TEST_at_testdb> commit;

Commit complete.

• This is the point where the error manifests itself.

TEST_at_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_at_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_at_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_at_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


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 21 2011 - 11:39:13 CDT