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:
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…