Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: COMMIT's within cursor for loops

RE: COMMIT's within cursor for loops

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Thu, 26 Jun 2003 12:36:40 -0700
Message-ID: <F001.005BAAAC.20030626121939@fatcity.com> 






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


Received on Thu Jun 26 2003 - 14:36:40 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US