RETURNING BULK COLLECT INTO

The previous post took a look at the RETURNING INTO clause for DML to obtain details about the row modified. However, the simple RETURNING INTO clause can only handle a single row modification and will throw an exception if the DML affects multiple rows:

SQL>DECLARE
  2     l_child_name child.child_name%TYPE;
  3  BEGIN
  4
  5     DELETE
  6     FROM   child
  7     RETURNING child_name INTO l_child_name;
  8
  9     dbms_output.put_line ('Deleted child ' || l_child_name);
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

RETURNING INTO contains a BULK COLLECT option for handling this situation. Rather than populating simple scalar variables the returned values are stored into collections:

SQL>SELECT * FROM child;

  CHILD_ID  PARENT_ID CHILD_NAME
---------- ---------- --------------------------------------------------
         1          1 CHILD_1
         2          2 CHILD_2
         3          2 CHILD_3

SQL>DECLARE
  2     TYPE t_child_name_tab IS TABLE OF child.child_name%TYPE;
  3     l_child_name_tab t_child_name_tab;
  4  BEGIN
  5
  6     DELETE
  7     FROM   child
  8     WHERE  child_id IN (SELECT child_id FROM child)
  9     >RETURNING child_name BULK COLLECT INTO l_child_name_tab;
 10
 11     FOR i IN 1..l_child_name_tab.LAST
 12     LOOP
 13        dbms_output.put_line ('Deleted child ' || l_child_name_tab(i));
 14      END LOOP;
 15  END;
 16  /
Deleted child CHILD_1
Deleted child CHILD_2
Deleted child CHILD_3

PL/SQL procedure successfully completed.

In the above example, we started off with 3 rows in the CHILD table (taken from the previous post). Our PL/SQL defined a collection variable, L_CHILD_NAME_TAB, of a simple scalar type. It then deleted the entries from the table, collecting the names of the deleted entries into this variable.

A few things to note with regard to the BULK COLLECT option:

• Any existing data in the collection that is BULK COLLECTed into is lost
• The indices of the collection start at 1 and are incremented by 1, i.e. it’s a dense collection
• If the DML operation fails to affect any rows then the returned collection is empty

Multiple collections can be used to retrieve different columns:

SQL>DECLARE
  2     TYPE t_child_id_tab IS TABLE OF child.child_id%TYPE;
  3     l_child_id_tab t_child_id_tab;
  4     TYPE t_child_name_tab IS TABLE OF child.child_name%TYPE;
  5     l_child_name_tab t_child_name_tab;
  6  BEGIN
  7
  8     DELETE
  9     FROM   child
 10     RETURNING child_id, child_name BULK COLLECT INTO l_child_id_tab,l_child_name_tab;
 11
 12     FOR i IN 1..l_child_id_tab.LAST
 13     LOOP
 14        dbms_output.put_line ('Deleted child ' || TO_CHAR(l_child_id_tab(i)) ||
 15                              ' - ' || l_child_name_tab(i));
 16      END LOOP;
 17  END;
 18  /
Deleted child 1 - CHILD_1
Deleted child 2 - CHILD_2
Deleted child 3 - CHILD_3

PL/SQL procedure successfully completed.

However, it might be easier to collect the values into a single record type collection:

SQL>DECLARE
  2     TYPE t_child_tab IS TABLE OF child%ROWTYPE;
  3     l_child_tab t_child_tab;
  4  BEGIN
  5
  6     DELETE
  7     FROM   child
  8     RETURNING child_id, parent_id, child_name
  9     BULK COLLECT INTO l_child_tab;
 10
 11     FOR i IN 1..l_child_tab.LAST
 12     LOOP
 13        dbms_output.put_line ('Deleted child ' || TO_CHAR(l_child_tab(i).child_id) ||
 14                              ' - ' || l_child_tab(i).child_name);
 15      END LOOP;
 16  END;
 17  /
Deleted child 1 - CHILD_1
Deleted child 2 - CHILD_2
Deleted child 3 - CHILD_3

PL/SQL procedure successfully completed.

All collection types are supported by BULK COLLECT; nested table, varray and associative array. It’s also possible to mix and match in the one operation (although I’m not sure exactly why you might want to do this…):

SQL>DECLARE
  2     TYPE t_child_id_tab IS TABLE OF child.child_id%TYPE
  3        INDEX BY PLS_INTEGER;
  4     l_child_id_tab t_child_id_tab;
  5
  6     TYPE t_child_name_tab IS TABLE OF child.child_name%TYPE;
  7     l_child_name_tab t_child_name_tab;
  8
  9
 10     TYPE t_parent_id_tab IS VARRAY(20) OF child.parent_id%TYPE;
 11     l_parent_id_tab t_parent_id_tab;
 12  BEGIN
 13
 14     DELETE
 15     FROM   child
 16     RETURNING child_id, parent_id, child_name
 17     BULK COLLECT INTO l_child_id_tab, l_parent_id_tab, l_child_name_tab;
 18
 19     FOR i IN 1..l_child_id_tab.LAST
 20     LOOP
 21        dbms_output.put_line ('Deleted child ' || TO_CHAR(l_child_id_tab(i)) |
 22                              ' - ' || l_child_name_tab(i) ||
 23                              ' - parent ' || TO_CHAR(l_parent_id_tab(i)));
 24      END LOOP;
 25  END;
 26  /
Deleted child 1 - CHILD_1 - parent 1
Deleted child 2 - CHILD_2 - parent 2
Deleted child 3 - CHILD_3 - parent 2

PL/SQL procedure successfully completed.

Keep in mind however that the collected values are stored in the PGA. Collecting millions of entries will consume memory. In days where we have gigabytes of memory on our mobile devices it is easily to overlook such concerns but since the database is typically a shared resource we need to be aware of what our programs are doing. If many sessions all tried to consume large amounts of memory then we can easily impact the overall operation of the database server.

Advertisement