BULK COLLECT into nested table

Posted by

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

“The BULK COLLECT into 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

7 comments

  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 ?

  2. Hi Norm,
    There is a good article that I’ve referenced a few times on the Oracle blog, written by Steve Feuerstein that walks through BULK COLLECT and FORALL: https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall. In doing some refresher exercises recently I noticed in contained the truncate error with BULK COLLECT you mention (see Code Listing 6). Using your tip I rewrote it and got it to execute properly in the HR schema:
    DECLARE
    c_limit PLS_INTEGER := 10;

    CURSOR employees_cur
    IS
    SELECT employee_id
    FROM employees
    WHERE department_id = 80;

    TYPE employee_ids_t IS TABLE OF
    employees.employee_id%TYPE;

    l_employee_ids employee_ids_t;
    l_employee_ids_agg employee_ids_t := employee_ids_t();
    BEGIN
    OPEN employees_cur;

    LOOP
    FETCH employees_cur
    BULK COLLECT INTO l_employee_ids
    LIMIT c_limit;
    l_employee_ids_agg := l_employee_ids_agg MULTISET UNION ALL l_employee_ids;
    EXIT WHEN employees_cur%NOTFOUND;
    END LOOP;

    END;
    /

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.