New in Oracle 12c: Querying an Associative Array in PL/SQL Programs
Posted: August 2, 2014 Filed under: 12C, PL/SQL | Tags: 12C, PL/SQL 9 CommentsI 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.