Fetch across Commits


Pretty much for as long as PL/SQL has been around, the manuals have stressed that you could/should not fetch across commit's from a cursor that contained a FOR UPDATE in the declaration. This of course is common sense, since issuing the commit would release any locks that the FOR UPDATE would have taken. But "unfortunately" in many versions of Oracle, this erroneous practice went unpunished because the code would actually compile and run successfully....

...not any more. In 8.1.7, the PL/SQL engine will throw (as it should) a "ORA-1002: Fetch out of sequence" error on the first fetch that is attempted after the commit is encountered.


In 8.0, 8.1.5 and 8.1.6

SQL> create table blah as 
  2  select rownum r , 'dummy' v 
  3  from user_objects 
  4  where rownum < 20;

 Table created.

SQL> declare
  2    cursor p is select r,v from x for update;
  3  begin
  4    for i in p loop
  5      update x set v = 'x' where current of p;
  6      commit;
  7    end loop;
  8  end;
  9  /

 PL/SQL procedure completed successfully.

In 8.1.7

SQL> declare
  2    cursor p is select r,v from x for update;
  3  begin
  4    for i in p loop
  5      update x set v = 'x' where current of p;
  6      commit;
  7    end loop;
  8  end;
  9  /

 ORA-01002: Fetch out of sequence

The workaround is to use the method as documented in the manual - omit the FOR UPDATE and fetch the rowid within the cursor.

SQL> declare
  2    cursor p is select rowid rwid, r,v from x;
  3  begin
  4    for i in p loop
  5      update x set v = 'x' where rowid = i.rwid;
  6      commit;
  7    end loop;
  8  end;
  9  /