PL/SQL- cursors and commits

From: Wiegand, Kurt <Kurt.Wiegand_at_CWUSA.COM>
Date: Fri, 04 Apr 2003 06:39:02 -0800
Message-ID: <F001.0057A545.20030404063902@fatcity.com>

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: Wiegand, Kurt
  INET: Kurt.Wiegand_at_CWUSA.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 - 08:39:02 CST