New in Oracle 12c: Querying an Associative Array in PL/SQL Programs

I was aware that up to Oracle 11g, a PL/SQL program wasn’t allowed use an associative array in a SQL statement. This is what happens when I try to do it.

SQL> drop table test_array purge;

Table dropped.

SQL> create table test_array as
  2  select level num_col from dual
  3  connect by level <= 10;

Table created.

SQL> select * from test_array;

   NUM_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10                                                                      

10 rows selected.

SQL> drop package PKG_TEST_ARRAY;

Package dropped.

SQL> create or replace package PKG_TEST_ARRAY as
  2
  3    type tab_num is table of number index by pls_integer;
  4
  5  end PKG_TEST_ARRAY;
  6  /

Package created.

SQL> declare
  2    my_array pkg_test_array.tab_num;
  3  begin
  4    for i in 1 .. 5 loop
  5      my_array(i) := i*2;
  6    end loop;
  7
  8    for i in (
  9              select num_col from test_array
 10              where num_col in (select * from table(my_array))
 11             )
 12    loop
 13      dbms_output.put_line(i.num_col);
 14    end loop;
 15  end;
 16  /
            where num_col in (select * from table(my_array))
                                                  *
ERROR at line 10:
ORA-06550: line 10, column 51:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 45:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 9, column 13:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 26:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 13, column 5:
PL/SQL: Statement ignored

As you can see, the TABLE operator is expecting either a nested table or a varray.

The limitation has been removed in Oracle 12c. This is what happens now.

SQL> set serveroutput on
SQL>
SQL> drop table test_array purge;

Table dropped.

SQL> create table test_array as
  2  select level num_col from dual
  3  connect by level <= 10;

Table created.

SQL> select * from test_array;

   NUM_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10                                                                      

10 rows selected.

SQL> drop package PKG_TEST_ARRAY;

Package dropped.

SQL> create or replace package PKG_TEST_ARRAY as
  2
  3    type tab_num is table of number index by pls_integer;
  4
  5  end PKG_TEST_ARRAY;
  6  /

Package created.

SQL> declare
  2    my_array pkg_test_array.tab_num;
  3  begin
  4    for i in 1 .. 5 loop
  5      my_array(i) := i*2;
  6    end loop;
  7
  8    for i in (
  9              select num_col from test_array
 10              where num_col in (select * from table(my_array))
 11             )
 12    loop
 13      dbms_output.put_line(i.num_col);
 14    end loop;
 15  end;
 16  /

2
4
6
8
10                                                                              

PL/SQL procedure successfully completed.

Here’s another example using a slightly different query.

SQL> declare
  2    my_array pkg_test_array.tab_num;
  3  begin
  4    for i in 1 .. 5 loop
  5      my_array(i) := i*2;
  6    end loop;
  7
  8    for i in (
  9              select a.num_col, b.column_value
 10              from
 11                test_array a,
 12                table (my_array) b
 13              where
 14                a.num_col = b.column_value
 15             )
 16    loop
 17      dbms_output.put_line(i.num_col);
 18    end loop;
 19  end;
 20  /

2
4
6
8
10                                                                              

PL/SQL procedure successfully completed.

Very nice stuff.