Not in v9 as far as I know.
In v10, a cursor-for-loop is automatically transformed to bulk collect (very very cool that
feature) so it might save you the effort.
The other option (is the evaluation of the predicates isn't too expensive) is to loop via rownum,
delete from ...
and rownum <;
exit when sql%notfound or;
Greg Norris wrote:
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:
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);
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com