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
============================================================================================================
[EMAIL PROTECTED]
WUSA.COM To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] 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
[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;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
