"Thou Shalt Not Fetch Across Commits" was ingrained in me many, many moons ago. It is
poor practice
and can cause multitudes of problems.
Just my 2 cents. YMMV.
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan
[EMAIL PROTECTED]
.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: COMMIT's within cursor
for loops
.com
06/26/2003 01:49
PM
Please respond to
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
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: Ron Thomas
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).
