Learning is not a spectator sport

July 14, 2014

Associative arrays and Nested tables

Filed under: Uncategorized — connormcdonald @ 7:52 pm

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 < 100;
 10  end;
 11
 12  procedure USE_IN_QUERY(n num_list) is
 13    x int;
 14  begin
 15    select count(*)
 16    into   x
 17    from   user_objects
 18    where object_id in (
 19       select column_value
 20       from   table(n)
 21       );
 22  end;
 23
 24  end;
 25  /

Warning: Package Body created with compilation errors.

SQL> 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 ? :-)

About these ads

1 Comment »

  1. […] was going to the be the immediate follow up to my previous post, but 12.1.0.2 came out and I got all excited about that and forgot to post this one […]

    Pingback by 12c – Nested tables vs Associative arrays | Learning is not a spectator sport — August 9, 2014 @ 9:31 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: