RE: COMMIT's within cursor for loops

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Wed, 25 Jun 2003 13:06:01 -0700
Message-ID: <F001.005B9B6E.20030625125111@fatcity.com>

        You've got the most important point correct. When you issue that commit in the middle of a cursor all locks on the data tables are released. To be technically correct the cursor is also invalid and with any other database management system you'd have to close and reopen the cursor. If you've included the "for update" clause in the cursor declaration then you will have to close and reopen the cursor no matter what. Oracle tries to be a nice rdbms by allowing us issue a commit across a cursor, but beware of the ORA-01555 error that you've caused on yourself.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Wednesday, June 25, 2003 1:40 PM
To: Multiple recipients of list 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: Goulet, Dick
  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). Received on Wed Jun 25 2003 - 15:06:01 CDT