TABLE function with BULK COLLECT

A quick post about a coding scenario that cropped up at work the other day. As developers we’re quite used to performing some calculation using a variable and assigning the result back to the very same variable used in the calculation. For example, the classic case of incrementing the value of a variable in PL/SQL would be written as:

l_variable := l_variable + 1;

Many modern languages even have specific operators for handling this very scenario, such as the C approach of:

l_variable++

which will use the current value of the variable and increment it immediately afterwards.

Anyway, the situation at work involved a nested table collection that the developer wanted sorted. The developer used the following within their PL/SQL code:

SELECT t.column_value
BULK COLLECT INTO l_collection
FROM   TABLE(l_collection) t
ORDER  BY
       t.column_value;

where l_collection was a nested table of numbers. Logically the above can be read as “take the nested table and treat it as if it were a database table, sort the values of that table and assign the resulting set of values back into the original collection.

The code compiled and executed without error but produced incorrect results. A quick test using an anonymous block shows what was happening:

CREATE OR REPLACE TYPE t_number_tab IS TABLE OF NUMBER
/

DECLARE
   l_number_tab t_number_tab := t_number_tab(2,4,5,1,3);
   
   -- outputs the collection details to dbms_output
   PROCEDURE display (p_number_tab t_number_tab)
   AS
   BEGIN
      IF (p_number_tab.COUNT = 0) THEN
         dbms_output.put_line ('Collection is empty');
      ELSE
         FOR i IN 1..p_number_tab.COUNT
         LOOP
            dbms_output.put_line ('Element ' || TO_CHAR(i) || ' is ' || TO_CHAR(p_number_tab(i)));
         END LOOP;
      END IF;
   END display;   
BEGIN

   dbms_output.put_line ('Initial collection:');
   display (l_number_tab);
   
   SELECT t.column_value
   BULK COLLECT INTO l_number_tab
   FROM TABLE (l_number_tab) t
   ORDER BY
         t.column_value;
         
   dbms_output.put_line ('Final collection:');
   display (l_number_tab);
END;
/

The result of the above script, in Oracle 11.2, is:

   
Initial collection:
Element 1 is 2
Element 2 is 4
Element 3 is 5
Element 4 is 1
Element 5 is 3
Final collection:
Collection is empty

So, the collection is being wiped out by the SQL statement. It seems the collection used in the TABLE expression cannot be the same as the collection that is being BULK COLLECTed into. Once this problem was identified then the solution was to simply BULK COLLECT into a new collection, i.e.:

SELECT t.column_value
BULK COLLECT INTO l_sorted_collection
FROM   TABLE(l_collection) t
ORDER  BY
       t.column_value;

Problem solved and another lesson on the finer points of Oracle development learned…

Advertisement