Indeed. Here's how I like to handle that type of thing.
-- not tested. may not compile, but the concept is there.
declare
cursor eupd ( rowid_in rowid )
is
select *
from emp e
where rowid = rowid_in;
eupdrec eupd%rowtype;
commit_count integer := 1;
commit_interval integer := 10000;
begin
for erec in select e.rowid, e.* from emp e;
loop
-- do lots of calculations or something here
open eupd(erec.rowid);
fetch eupd into eupdrec;
update emp
set something = something_else
where current of eupd;
close eupd;
commit_count := commit_count + 1;
if commit_count >= commit_interval then
commit_count := 1;
commit;
end if;
end loop;
commit;
end;
No fetch across commits, no ORA-1555's.
Jared
"Steve McClure" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/26/2003 12:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: COMMIT's within cursor for loops
>I prefer to create an inner block using BEGIN and END inside the loop.
This
>isolates the DML statements. The COMMIT is issued inside the BEGIN and
END
>block.
>
>RWB
That allows you to avoid invalidating the cursor established "for update"?
I dont' see how the two are related. You just created an exception block
within the cursor loop. Or am i misunderstanding what you are saying
here?
Regarding the original question commiting within a cursor for loop. It is
allowable if you do not create the cursor "for update". If you created
the
cursor using "for update", you will not only NOT retain a lock on the
record
set, you will get an error indicating an invalid cursor.
Steve McClure
-----Original Message-----
[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L
I prefer to create an inner block using BEGIN and END inside the loop.
This
isolates the DML statements. The COMMIT is issued inside the BEGIN and END
block.
RWB
============================================================================
================================
Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
============================================================================
================================
[EMAIL PROTECTED]
ic.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: COMMIT's within
cursor
for loops
ity.com
06/25/2003
12:40 PM
Please respond
to ORACLE-L
Hi All,
Can somebody please clear up some issues about issuing commits during
CURSOR
FOR LOOPS
I have done some research within Metalink and the ORACLE-L FAQ but am
still
a tad bit confused.
Are the following statements regarding cursors TRUE
1. If you issue a commit within a for cursor loop you release all locks
regardless of whether you're cursor statement has a FOR UPDATE statement
in
it or there is a WHERE CURRENT statement within the loop.
2. If you are using OPEN CURSOR...FETCH INTO statements to get the data
the
same rule applies.
Thanks,
N.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuala Cullen
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).
--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steve McClure
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).
--
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).
