Re: using a cursor for loop with bulk collect?
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Oct 2004 17:00:57 +0100 (BST)
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,
ie
loop
delete from ...
where <predicates>
and rownum < <size_per_batch>;
exit when sql%notfound or <other condition>;
commit;
end loop;
commit;
hth
connor
--- Greg Norris <Spikey.McMarbles@xxxxxxxxx> 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:
>
> 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?
> --
> //www.freelists.org/webpage/oracle-l
>
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
