Skip to main content

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.

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
  11. Hi Steven

    I found this interesting post last week. As I don't have access to Oracle 12c (only 11g), will this work?

    DECLARE
    l_employees plch_arrays.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;
    /

    ReplyDelete
    Replies
    1. Ah, Volker, but you DO have access to 12c....at: livesql.oracle.com. And I should have included a script on LiveSQL to go with this blog post (which I have now done), and it shows this working just fine. https://livesql.oracle.com/apex/livesql/file/content_FHFIHMCNJ8HRSN9IJ8K800LOP.html

      Delete
  12. Thanks, finally a long waited feature!
    Btw, is there a way to see a value of INDEX in select?

    ReplyDelete
    Replies
    1. Unfortunately, you do not have access to that information.

      Delete
  13. I tried to replicate the above but got issues. The setup information is as follows i.e.

    DB Details
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    PL/SQL Release 12.1.0.2.0 - Production

    1. Package A spec
    TYPE t_my_rec is record
    (keycol1 varchar2(9)
    ,keycol2 varchar2(12)
    ,keycol3 date
    ,keycol4 varchar2(9)
    ,keycol5 varchar2(9)
    ,keycol6 number);

    TYPE t_my_tab is table of t_my_rec
    index by varchar2(80);


    2. Package B Body
    Procedure myproc
    Declaration
    t_my_rec_cv pkg_a.t_my_tab;
    Begin End

    t_my_rec_cv := pkg_a.get_my_rec(Return data type is t_my_tab);
    FOR rec IN ( SELECT *
    FROM TABLE (t_my_rec_cv)
    )
    LOOP
    DBMS_OUTPUT.put_line (rec.keycol1);
    END LOOP;

    Error details

    Error(1909,22): PL/SQL: SQL Statement ignored
    Error(1910,25): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
    Error(1910,32): PLS-00382: expression is of wrong type
    Error(1912,7): PL/SQL: Statement ignored
    Error(1912,29): PLS-00364: loop index variable 'REC' use is invalid

    ReplyDelete
    Replies
    1. Deepak, please download the code from the LiveSQL script referenced in this post. Run that in your 12.1 environment. Does it work? I just did that and it worked fine.

      Also in terms of what you provided above, it is always SO much better to provide all the actual code, not fragments as you have done here.

      Thanks!

      Delete
  14. I have used this concept widely in my new codes and it is easier to check/retrieve data from an array with simple code, but now i am facing issues with one scenario , please find the below examples.

    CREATE OR REPLACE PACKAGE test_nested_table IS
    TYPE check_row_id IS RECORD
    (
    unique_id VARCHAR2(100),
    l_rowid rowid
    );

    TYPE check_row_id_array IS TABLE OF check_row_id;
    l_check_row_id_array check_row_id_array;
    END;

    create or replace package body test_nested_table is

    PROCEDURE chk_table IS

    BEGIN
    FOR rec IN ( SELECT *
    FROM TABLE (l_check_row_id_array)
    )
    LOOP
    DBMS_OUTPUT.put_line (rec.l_rowid);
    END LOOP;
    END;

    end test_nested_table;

    create or replace package body test_nested_table is

    PROCEDURE chk_table IS

    BEGIN
    FOR rec IN ( SELECT *
    FROM TABLE (l_check_row_id_array)
    )
    LOOP
    DBMS_OUTPUT.put_line (rec.l_rowid);
    END LOOP;
    END;

    end test_nested_table;
    create or replace package body test_nested_table is

    PROCEDURE chk_table IS

    BEGIN
    FOR rec IN ( SELECT *
    FROM TABLE (l_check_row_id_array)
    )
    LOOP
    DBMS_OUTPUT.put_line (rec.l_rowid);
    END LOOP;
    END;

    end test_nested_table;

    When i try to compile it returns below error
    Error: PLS-00382: expression is of wrong type
    Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
    Error: PL/SQL: SQL Statement ignored
    Error: PLS-00364: loop index variable 'REC' use is invalid
    Error: PL/SQL: Statement ignored

    This is because of the type i created with a column having rowid as datatype, if i change the datatype then it works , does any one having a workaround or a known solution for this issue ?

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel