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

Home -> Community -> Mailing Lists -> Oracle-L -> using a cursor for loop with bulk collect?

using a cursor for loop with bulk collect?

From: Greg Norris <Spikey.McMarbles_at_gmail.com>
Date: Fri, 1 Oct 2004 10:13:21 -0500
Message-ID: <cf0ca3160410010813787e5976@mail.gmail.com>


I'm setting up a procedure to purge old data from an application table. For keep the run-time manageable, I'm fetching the relevant rowids with an explicit cursor, using the BULK COLLECT clause. Once the relevant rows have been identified, I'm using FORALL to perform a bulk delete. This seems to be working well, but it got me curious...

Is it possible to do bulk collections via a CURSOR FOR loop, or is the open/fetch/close sequence strictly required? If yes, can you provide an example of the syntax? I can't seem to find any examples in the 8.1.7 or 9.2.0 documentation, but I also can't find anything which says it isn't allowed.

A simplified example of my current scheme is:

loop

   fetch c1 bulk collect into rowlist limit rows_per_set;    exit when c1%NOTFOUND;

   forall i in rowlist.FIRST..rowlist.LAST

      delete from mytab where rowid = rowlist(i);    commit;
end loop;

Thoughts?

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 01 2004 - 10:09:41 CDT

Original text of this message

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