| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL- cursors and commits
Thanks for all the responses. My research has revealed that
while Oracle had warned that you "should not fetch across commit's
from a cursor that contained a FOR UPDATE......in many
versions of Oracle, this erroneous practice went unpunished because
the code would actually compile and run successfully..." but in
8.1.7, it was fixed for good. I've found an Oracle suggested 'workaround'-
omit the FOR UPDATE and fetch the rowid within the cursor. Now,
my original intent was to find a way to reduce the amount of rollback required
when updating a large table. So, does the use of rowid accomplish this,
or does my cursor still need a read consistent view of the data
until closed? Thanks.
Kurt
.......
fetch c_select
into local_f1,
local_f2,
local_rowid;
-----Original Message-----
Sent: Monday, April 07, 2003 4:09 PM
To: Multiple recipients of list ORACLE-L
I'm not going to guarantee that my memory is correct, but I have a vague idea that this appeared in 8.1.5 as a bug which was subsequently fixed. Cursors are always supposed to become invalid on commit (even without the "for update") but Oracle only enforced this rule for cursors with "for update", raising ORA-01002 if you continued using the cursor after the commit.
There have been some bugs with ORA-01002 being raised incorrectly when mixing cursor loops and autonomous transactions, but your example doesn't give any indication that you are doing anything that might be related to that problem.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
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;
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: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk 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). -- 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 Mon Apr 07 2003 - 15:45:42 CDT
 |
 |