Suppose I create these database objects:
CREATE TABLE plch_employees ( employee_id INTEGER PRIMARY KEY, last_name VARCHAR2 (100) UNIQUE, salary NUMBER ) / BEGIN INSERT INTO plch_employees VALUES (100, 'Apramy', 1000); INSERT INTO plch_employees VALUES (175, 'Shipo', 2500); INSERT INTO plch_employees VALUES (242, 'Inkul', 500); END; / CREATE OR REPLACE PACKAGE plch_arrays IS TYPE employees_t IS TABLE OF plch_employees%ROWTYPE INDEX BY PLS_INTEGER; TYPE emps_by_name_t IS TABLE OF plch_employees%ROWTYPE INDEX BY plch_employees.last_name%TYPE; g_employees employees_t; END; / CREATE OR REPLACE PACKAGE BODY plch_arrays IS BEGIN SELECT * BULK COLLECT INTO g_employees FROM plch_employees; END; /
I want to display employee names in alphabetical order. Of course, if I am simply selecting data from the table directly, no worries, just write:
SELECT last_name FROM plch_employees
ORDER BY last_name
But suppose that data has already been placed into an associative array for processing. How can I sort the contents of the array?In the old days, I could have created another array with a string index and then "copied" the data to that array, using the last name as the index value:
DECLARE l_emps_by_name plch_arrays.emps_by_name_t; l_index VARCHAR2(100); BEGIN FOR indx IN 1 .. plch_arrays.g_employees.COUNT LOOP l_emps_by_name (plch_arrays.g_employees (indx).last_name) := plch_arrays.g_employees (indx); END LOOP; l_index := l_emps_by_name.FIRST; WHILE (l_index IS NOT NULL) LOOP DBMS_OUTPUT.put_line ( l_emps_by_name (l_index).last_name); l_index := l_emps_by_name.NEXT (l_index); END LOOP; END; /
OK fair enough, and string-indexing of collections is undoubtedly a very cool feature. But in the meantime, I have written a bunch of code and used extra Process Global Area memory. I'd really rather not. Now, in 12.1, life becomes easier, simpler, faster.
BEGIN FOR rec IN ( SELECT * FROM TABLE (plch_arrays.g_employees) ORDER BY last_name) LOOP DBMS_OUTPUT.put_line (rec.last_name); END LOOP; END; /
Short and sweet. Just nestle that array inside TABLE and then use ORDER BY to do all the heavy lifting. Note: collection type must be defined in the package specification. As a result, this approach fails:
DECLARE TYPE employees_t IS TABLE OF plch_employees%ROWTYPE INDEX BY PLS_INTEGER; l_employees employees_t; BEGIN SELECT * BULK COLLECT INTO l_employees FROM plch_employees; FOR rec IN ( SELECT * FROM TABLE (l_employees) ORDER BY last_name) LOOP DBMS_OUTPUT.put_line (rec.last_name); END LOOP; END; /
PLS-00382: expression is of wrong type ORA-06550: line 12, column 25: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
which in actuality is telling you that in order to reference an associative array inside TABLE, the collection type must be defined in the package specification.
The collection, however, does not have to be declared in the package specification. This approach works just fine:
DECLARE l_employees plch_arrays.employees_t := plch_arrays.g_employees; BEGIN FOR rec IN ( SELECT * FROM TABLE (l_employees) ORDER BY last_name) LOOP DBMS_OUTPUT.put_line (rec.last_name); END LOOP; END; /