Oracle-L: RE: PL/SQL- cursors and commits

From: Ganesh Raja <gans_r_at_rediffmail.com>
Date: Fri, 04 Apr 2003 19:48:59 -0800
Message-ID: <F001.0057B2F4.20030404194859@fatcity.com>

This is the last thng u will be writing .. a Commit inside a Loop for every n records processed.

First Like Ashish Said u will get ORA-01002 and apart from this u will hit by a bigger problem.. ORA-01555 on long running quries..

HTH Best Regards,
Ganesh R
DID : +65-6215-8413
HP : +65-9067-8474

-----Original Message-----
Sent: Saturday, April 05, 2003 12:19 AM
To: Multiple recipients of list ORACLE-L

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-----
Kurt
Sent: Friday, April 04, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L

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
kurt.wiegand_at_cwusa.com

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;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Ganesh Raja
INET: gans_r_at_rediffmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Apr 04 2003 - 21:48:59 CST