Semi-Autonomous?


The previous tip showed how 8.1.7 is now (correctly) enforcing the prohibition of commits within a 'for update' cursor (or more accurately, stopping you from fetching any further records from the cursor.

Interestingly, if you're considering the workaround of possibly using an autonomous transaction to save away some information, you will STILL get a 'fetch out of sequence' error when you attempt to do so. The autonomous transaction itself still works, but it obviously has an impact on the 'parent' transaction, hence the coined term "semi-autonomous". Whether this behaviour is correct or not is still with the Oracle Support people.

SQL> create table blah as select rownum r1 from sys.tab$ where rownum < 10;

 Table created.

SQL> create table dummy ( x number );

 Table created.

SQL> create procedure autonomous_proc is
  2    pragma autonomous_transaction;
  3  begin
  4    insert into dummy values (1);
  5    commit;
  6  end;
  7  /

SQL> declare
  2    cursor c is select * from blah for update;
  3  begin
  4    for i in c loop
  5      update blah set r1 = r1 * 2 where current of c;
  6      autonomous_proc;
  7    end loop;
  8  end;
  9  /

ORA-1002: Fetch out of sequence