Find out if a collection was populated by bulk collect

Question

I created an oracle Object Type like this:

CREATE OR REPLACE TYPE DFBOWNER."RPT_WIRE_IMPORT_ROWTYPE" AS OBJECT

(
REC_VALUE_DATE   DATE
)
/

And then a collection based on this type:

CREATE OR REPLACE TYPE DFBOWNER."RPT_WIRE_IMPORT_TABLETYPE" IS TABLE OF RPT_WIRE_IMPORT_RowType;
/

Now I populate the collection using oracle bulk collect into syntax inside a procedure. So now i want to test if the collection actually got populated, and i am not sure how to do it. I tried looking it up:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#autoId17 but I am not able to find what I need.

I also have another question. When the procedure bulk collects data into collections, does the data in the collection become permanent as in a table? Or is it semi-permanent...i.e. only lives for the session...as in a temp table.

Answer 1

I suspect you are looking for the COUNT method, i.e.

DECLARE
  l_local_collection dbfowner.rpt_wire_import_tabletype;
BEGIN
  SELECT sysdate + level
    BULK COLLECT INTO l_local_collection
    FROM dual
 CONNECT BY level <= 10;
  dbms_output.put_line( 'l_local_collection contains ' || 
                           l_local_collection.count || 
                           ' elements.' );
END;

Like any local variable, l_local_collection will have the scope of the block in which it is declared. The data is stored in the PGA for the session. The data in a collection is not permanent.

You can select from the local collection

SQL> create type some_object as object (
  2    rec_value_date date
  3  );
  4  /

Type created.

SQL> create type some_coll
  2      as table of some_object;
  3  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_local_collection some_coll;
  3  begin
  4    select some_object( sysdate + numtodsinterval( level, 'day' ) )
  5      bulk collect into l_local_collection
  6      from dual
  7   connect by level <= 10;
  8    for x in (select * from table( l_local_collection ))
  9    loop
 10      dbms_output.put_line( x.rec_value_date );
 11    end loop;
 12* end;
SQL> /
20-AUG-12
21-AUG-12
22-AUG-12
23-AUG-12
24-AUG-12
25-AUG-12
26-AUG-12
27-AUG-12
28-AUG-12
29-AUG-12

PL/SQL procedure successfully completed.

but it generally doesn't make sense to go through the effort of pulling all the data from the SQL VM into the PL/SQL VM only to then pass all of the data back to the SQL VM in order to issue the SELECT statement. It would generally make more sense to just keep the data in SQL or to define a pipelined table function to return the data.

If you merely want to iterate over the elements in the collection

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_local_collection some_coll;
  3  begin
  4    select some_object( sysdate + numtodsinterval( level, 'day' ) )
  5      bulk collect into l_local_collection
  6      from dual
  7   connect by level <= 10;
  8    for i in 1 .. l_local_collection.count
  9    loop
 10      dbms_output.put_line( l_local_collection(i).rec_value_date );
 11    end loop;
 12* end;
SQL> /
20-AUG-12
21-AUG-12
22-AUG-12
23-AUG-12
24-AUG-12
25-AUG-12
26-AUG-12
27-AUG-12
28-AUG-12
29-AUG-12

PL/SQL procedure successfully completed.

It would make much more sense to iterate over the elements in the collection, which keeps everything in PL/SQL, than to SELECT from the collection, which forces all the data back into the SQL VM.