RE: PL/SQL- cursors and commits

[Ashish]

Title: PL/SQL- cursors and commits

As the book says, it fails with following error (9.2.0.1 on Win2k).

 

declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 12

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, Kurt
Sent: Friday, April 04, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L
Subject: PL/SQL- cursors and commits

I've been 'experimenting' with the following code in 8.1.5 and it seems to work fine.  However,
my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein Bill Pribyl 1997) leads me to
believe that it should not work.  They state "As soon as a cursor with a FOR UPDATE is OPENed,
all rows...are locked. When [a COMMIT]..occurs, the locks...are released. As a result, you
cannot execute another FETCH against a FOR UPDATE cursor after you COMMIT......"  They
go further to suggest an ORA-01002 would be returned.

Any comments? Thanks.

Kurt Wiegand
[EMAIL PROTECTED]

declare
  local_f1 ctest.f1%TYPE := 0;
  local_f2 ctest.f2%TYPE := 0;
  batch_count number(6) := 0;
  cursor c_select is
  select f1,f2 from ctest
  for update;

begin
  open c_select;
  loop
      fetch c_select
              into local_f1,
                     local_f2;
      exit when c_select%NOTFOUND;
      update ctest
                 set f2 = f2 + 1
                 where current of c_select;

     batch_count := batch_count + 1;

     if batch_count > 99 then
        batch_count := 0;
        commit;
     end if;
 
  end loop;
  close c_select;
  commit;
end;