This discussion is archived
3 Replies Latest reply: Feb 22, 2013 5:43 AM by APC RSS

Bulk collect and nested tables

926318 Newbie
Currently Being Moderated
Hello,
i have some queries regarding bulk collect and nested table.
Normally whenever we use nested tables in pl/sql programming we have to first initialize the constructor and then before assigning any value in the nested table we need to use extend.
but during bulk collect i have seen ( also used ) in many programs that ,we dont initialize the constructor and dont use extend.
so can you please confirm that these is no need to initialize and extend when we use bulk collect and nested table?

but i have seen that when we use bulk insert we need to initialize constructor and use the extend functionality.

thanks in advance.

regards,
Prashant
  • 1. Re: Bulk collect and nested tables
    Solomon Yakobson Guru
    Currently Being Moderated
    923315 wrote:
    so can you please confirm that these is no need to initialize and extend when we use bulk collect and nested table?
    Correct. PL/SQL will re-initialize it and extend it. And re-initializing means you will lose all prior collection content. There is no option to bulk collect append (too bad).

    SY.
  • 2. Re: Bulk collect and nested tables
    701278 Explorer
    Currently Being Moderated
    Bulk collecting into a collection variable automatically:

    1) deletes any data present (if any) in that collection
    2) initializes the collection if needed (nested tables)
    3) extends the collection by the number of rows fetched (if needed again)
    4) fills the collection starting with index 1 to the number of rows being fetched.

    I haven't found a precise description of this behaviour in the doc, but the examples clearly show that you don't need to initialize the collection:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#i49139
  • 3. Re: Bulk collect and nested tables
    APC Oracle ACE
    Currently Being Moderated
    Yes, BULK COLLECT implicitly initialises and extends the receiving nested table.
    but i have seen that when we use bulk insert we need to initialize constructor and use the extend functionality.
    Not sure what you're talking about. Bulk insert (i.e. FORALL) reads from a nested table, it has nothing to do with getting data into one.

    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points