FAQ
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?

Search Discussions

  • Ryan_gaffuri_at_comcast.net at Oct 1, 2004 at 10:20 am
    through 9.2 open,fetch, close is required. haven't checked version 10.
    -------------- Original message --------------
    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Connor McDonald at Oct 1, 2004 at 10:56 am
    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
    and rownum <;

    exit when sql%notfound or;
    commit;
    end loop;
    commit;

    hth
    connor

    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:

    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
    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_at_yahoo.com

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 1, '04 at 10:09a
activeOct 1, '04 at 10:56a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase