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).