Use TABLE Operator with Associative Arrays in Oracle Database 12c

Starting with 12.1, you can now use the TABLE operator with associative arrays whose types are defined in a package specification. One really sweet application of this feature is to order the contents of your collection. Let's take a look

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;
/

with:

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;
/

Check out my LiveSQL script that demonstrates all of the above and then some.