Re: PL/SQL- cursors and commits

Kurt:

It appears that you are committing inside the loop in the same inner block that the update is in. The commit is freeing up the rows. I find it a good practice sometimes to do commits inside the inner block like that, on long transactions.

Reginald W. Bailey
Your Friendly Neighborhood DBA

                                                                                                                               
                    Kurt.Wiegand_at_C                                                                                             
                    WUSA.COM             To:     ORACLE-L_at_fatcity.com                                                          
                    Sent by:             cc:                                                                                   
                    root_at_fatcity.c       Subject:     PL/SQL- cursors and commits                                              
                    om                                                                                                         
                                                                                                                               
                                                                                                                               
                    04/04/2003                                                                                                 
                    08:39 AM                                                                                                   
                    Please respond                                                                                             
                    to 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: 
  INET: Reginald.W.Bailey_at_jpmorgan.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).