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;