Friday, February 26, 2016

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

10 comments:

  1. Ooh, this will be useful.

    Just a question: in this sample code, where is the definition for g_employees? Did you mean to refer to l_employees? And is the reference plch_arrays to the package name?

    DECLARE
    l_employees plch_arrays.employees_t;
    BEGIN
    FOR rec IN ( SELECT *
    FROM TABLE (plch_arrays.g_employees)
    ORDER BY last_name)
    ...

    ReplyDelete
  2. Scratch that, I see you're referring to the package global you had defined earlier. Then the local variable is not required there I think.

    ReplyDelete
  3. Jeff, g_employees is defined in the plch_arrays package specification. I will remove the unnecessary declaration of l_employees. That's confusing!

    ReplyDelete
  4. You state that "the collection type must be defined in the package specification", and that would be employees_t.

    But to be precise you should also add that the (global) package variable (g_employees) must be declared in the package spec.

    - Morten

    ReplyDelete
  5. Morten, not true, though I did not give an example. This works just fine as well:

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

    I will add this to the post now.

    ReplyDelete
  6. Great, that makes more sense!

    BTW, since you prefix local variables with l ("l_employees") and globals with g ("g_employees"), you really should start prefixing types with t ("t_employees"), instead of putting it at the end. I know, I know, everybody has their own coding style and all, but don't you think there is a certain harmony in naming these related items l_employees, g_employees, t_employees? :-)

    - Morten

    ReplyDelete
  7. OK, that's IT. I am not talking to you anymore, Morten. I am sick and tired of people criticizing my naming conventions.

    :-)

    You make what appears on the surface to be a very logical point, but actually it is completely irrational.

    I just can't explain why. Have a fine weekend.

    ReplyDelete
  8. But would "t_employees" indicate a global type or a local type? ;)

    The _t suffix is doing a different job to the g_ and l_ scope indicators, so it would be confusing to put them in the same position.

    ReplyDelete
  9. Yes, what he said! :-) That is roughly how I see it. Specification of type is different from scope.

    ReplyDelete
  10. Each to his own I guess, but at least the Apex developers agree with me on the t_ prefix:

    https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_plugin.htm#AEAPI1234

    :-)

    - Morten

    ReplyDelete