BULK COLLECT into nested table

I had an observation come to me last week about PL/SQL and populating nested tables.

“The BULK COLLECT into <nested table> statement cannot be used repeatedly to append results into a table. 
Instead, it silently truncates the target table each time. “

This is true.  However, if you need to use BULK COLLECT to append results into a single nested table data structure, it is trivial to come up with a workaround using the MULTISET syntax.


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> drop table t2 purge;

Table dropped.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    type ntt is table of t1%rowtype;
  3    r1 ntt := ntt();
  4    r2 ntt := ntt();
  5  begin
  6    select * bulk collect into r1 from t1 ;
  7    select * bulk collect into r2 from t2 ;
  8    dbms_output.put_line('T1 count= '||r1.count);
  9    dbms_output.put_line('T2 count= '||r2.count);
 10
 11    r1 := r1 MULTISET UNION ALL r2;
 12
 13    dbms_output.put_line('TOTAL = '||r1.count);
 14  end;
 15  /
T1 count= 99250
T2 count= 99250
TOTAL = 198500

PL/SQL procedure successfully completed.

Read more about the multiset syntax here

3 thoughts on “BULK COLLECT into nested table

  1. sorry, not getting you on this
    “The BULK COLLECT into statement cannot be used repeatedly to append results into a table.
    Instead, it silently truncates the target table each time. “

    Could you please elaborate or show a demo/testcase ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s