RE: PL/SQL- cursors and commits

From: Ashish <ash03_at_att.net>
Date: Fri, 04 Apr 2003 08:18:53 -0800
Message-ID: <F001.0057A774.20030404081853@fatcity.com>

PL/SQL- cursors and commitsAs 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: root_at_fatcity.com [mailto:root_at_fatcity.com]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
  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: Ashish
  INET: ash03_at_att.net

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 - 10:18:53 CST