RE: COMMIT's within cursor for loops

From: <Jared.Still_at_radisys.com>
Date: Thu, 26 Jun 2003 14:07:18 -0700
Message-ID: <F001.005BABED.20030626132443@fatcity.com>

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

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.
If you are using OPEN CURSOR...FETCH INTO statements to get the data the same rule applies.

Thanks,

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

Received on Thu Jun 26 2003 - 16:07:18 CDT