Posted by A common criticism of PLSQL is that the “original” array datatype, now called associative arrays are perfect for passing stuff back and forth to 3GL environments (for example .Net), but canno be used within SQL natively, for example:
SQL> create or replace
2 package BLAH is
3 type num_list is table of number index by pls_integer;
4 type str_list is table of varchar2(30) index by pls_integer;
5
6 procedure ODP_PROC(n out num_list, s out str_list);
7
8 procedure USE_IN_QUERY(n num_list);
9
10 end;
11 /
Package created.
SQL> create or replace
2 package body BLAH is
3
4 procedure ODP_PROC(n out num_list, s out str_list) is
5 begin
6 select rownum, 'mystring'||rownum
7 bulk collect into n,s
8 from dual
9 connect by level sho err
Errors for PACKAGE BODY BLAH:
LINE/COL ERROR
-------- -----------------------------------------------------------------
14/3 PL/SQL: SQL Statement ignored
19/13 PL/SQL: ORA-22905: cannot access rows from a non-nested table
item
19/19 PLS-00382: expression is of wrong type
So we then look at using nested tables, but of course, a lot of 3GL’s will not understand such type – sometimes needing some sort of type translation. Hence people complain about the incompatibility etc. But its not all that hard – why not use both to have the best of both worlds….Use associative arrays to pass externally to 3GL’s, and convert to a nested table if you want to use within SQL, eg
SQL> create or replace
2 type nt_num_list is table of number;
3 /
Type created.
SQL> create or replace
2 package BLAH is
3 type num_list is table of number index by pls_integer;
4 type str_list is table of varchar2(30) index by pls_integer;
5
6 procedure ODP_PROC(n out num_list, s out str_list);
7
8 procedure USE_IN_QUERY(n num_list);
9
10 end;
11 /
Package created.
SQL> create or replace
2 package body BLAH is
3
4 procedure ODP_PROC(n out num_list, s out str_list) is
5 begin
6 select rownum, 'mystring'||rownum
7 bulk collect into n,s
8 from dual
9 connect by level < 100;
10 end;
11
12 procedure USE_IN_QUERY(n num_list) is
13 x int;
14
15 local_copy nt_num_list := nt_num_list();
16 begin
17 local_copy.extend(n.count);
18 for i in 1 .. n.count loop
19 local_copy(i) := n(i);
20 end loop;
21
22 select count(*)
23 into x
24 from user_objects
25 where object_id in (
26 select column_value
27 from table(local_copy)
28 );
29 end;
30
31 end;
32 /
Package body created.
That wasn’t that hard was it ? 🙂
Learning is not a spectator sport 
One comment