You Asked

Can you join a PL/SQL collection like an index-by table/associative array to a regular table? If not, what is a good technique for matching data in both sets?

Thanx,
Don

and Connor said...

Not in 11, but yes in 12.

v11

SQL> create or replace
  2  package pkg is
  3    type t is table of number index by pls_integer;
  4    procedure p;
  5  end;
  6  /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3
  4  procedure p is
  5    r t;
  6  begin
  7    r(1) := 10;
  8    r(2) := 20;
  9
 10    for i in ( select * from table(r)
 11              )
 12    loop
 13      null;
 14    end loop;
 15  end;
 16
 17  end;
 18  /

Warning: Package Body created with compilation errors.

SQL>
SQL>
SQL> sho err
Errors for PACKAGE BODY PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/14     PL/SQL: SQL Statement ignored
9/28     PL/SQL: ORA-22905: cannot access rows from a non-nested table
         item

9/34     PLS-00382: expression is of wrong type

v12

SQL> create or replace
  2  package pkg is
  3    type t is table of number index by pls_integer;
  4    procedure p;
  5  end;
  6  /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3
  4  procedure p is
  5    r t;
  6  begin
  7    r(1) := 10;
  8    r(2) := 20;
  9
 10    for i in ( select * from table(r)
 11              )
 12    loop
 13      null;
 14    end loop;
 15  end;
 16
 17  end;
 18  /

Package body created.

In v11, you would need to transfer the data to a nested table type, or global temporary table.

Hope this helps.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, August 12, 2016 - 3:09 pm UTC

Ok, thanx.

Column names

henryfg42, August 23, 2016 - 4:07 am UTC

Pardon my ignorance, but how do you refer to the individual columns of implicit cursor i?
In other words, replace the statement 'null;' with something like:
dbms_output.put_line('INFO: r('||<ref1>||') = '||i.<ref2>);
and I don't know what to put in for <ref1> and <ref2>

cheers

August 23, 2016 - 8:16 am UTC

They're the same name as the columns in the query itself:

begin
  for c in (select 1 c1, 'a' c2 from dual) loop
    dbms_output.put_line(c.c1 || ',' || c.c2);
  end loop;
end;
/

1,a

Chris

Column names - 2

henryfg42, August 23, 2016 - 11:50 pm UTC

Appreciate your feedback, but...
in this case what ARE the column names in the query itself:
select * from table(r)
r is of type t
and t is a table of number index by pls_integer

No column names are ever assigned (to the best of my knowledge)
Is there an 'implicit name' like col1 or c1?

Alternatively is there a way to DESCRIBE implicit cursor 'i' so the column names are exposed?

Many thanks

Cheers

August 24, 2016 - 1:00 am UTC

If it is a simple scalar, the column is COLUMN_VALUE. If it's record structure, it is as per the record definition, eg


SQL> create or replace
  2  package pkg is
  3    type t is table of number index by pls_integer;
  4    procedure p;
  5  end;
  6  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body pkg is
  3  procedure p is
  4    r t;
  5  begin
  6    r(1) := 10;
  7    r(2) := 20;
  8
  9    for i in ( select * from table(r)
 10              )
 11    loop
 12      dbms_output.put_line(i.column_value);
 13    end loop;
 14  end;
 15
 16  end;
 17  /

Package body created.

SQL>
SQL> set serverout on
SQL> exec pkg.p;
10
20

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> create or replace
  2  package pkg is
  3    type recs is record ( x1 number, y1 number );
  4    type t is table of recs index by pls_integer;
  5    procedure p;
  6  end;
  7  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body pkg is
  3  procedure p is
  4    r t;
  5  begin
  6    r(1).x1 := 10;
  7    r(1).y1 := 11;
  8
  9    r(1).x1 := 20;
 10    r(1).y1 := 21;
 11
 12    for i in ( select * from table(r)
 13              )
 14    loop
 15      dbms_output.put_line(i.x1||','||i.y1);
 16    end loop;
 17  end;
 18
 19  end;
 20  /

Package body created.

SQL>
SQL> set serverout on
SQL> exec pkg.p;
20,21

PL/SQL procedure successfully completed.

SQL>

Jess, January 24, 2019 - 12:35 pm UTC

Hi Connor,

How can you join to the associative array if you want to join by index, not value?

What I mean is,

declare
    type t_my_array is table of number index by varchar2(30);
    v_tables t_my_array;

begin

   -- hardcode number for simplicity, as logic of deriving it is not relevant to the question)
   for rec in (select table_name, 22 as ct from user_tables where rownum < 4) loop
      v_tables(rec.table_name) := rec.ct;
   end loop;


   for rec in (select tabcol.table_name, tabcol.column_name from user_tab_columns tabcol, table(v_tables)
                where v_tables(????) = tabcol.table_name order by tabcol.table_name, tabcol.column_name) loop

   -- do stuff
   end loop;

end;
/

What's the correct syntax to have a regular db table join to the associative array on all of its rows based on the *index* column?

Many thanks!

February 06, 2019 - 1:48 am UTC

There's nothing native to do that, so you'd need to do it via an additional attribute, eg

SQL> create or replace
  2  type my_data as object ( idx varchar2(30), n number);
  3  /

Type created.

SQL>
SQL> create or replace
  2  type my_list as table of my_data
  3  /

Type created.

SQL>
SQL> create or replace
  2  package PKG is
  3
  4    type t_my_array is table of number index by varchar2(30);
  5
  6    function array_to_nt return my_list pipelined;
  7
  8    procedure my_proc;
  9
 10  end;
 11  /

Package created.

SQL>
SQL> create or replace
  2  package body PKG is
  3
  4    p t_my_array;
  5
  6    function array_to_nt return my_list pipelined is
  7      i varchar2(30) := p.first;
  8    begin
  9      loop
 10        exit when i is null;
 11        pipe row ( my_data(i,p(i)));
 12        i := p.next(i);
 13      end loop;
 14      return;
 15    end;
 16
 17    procedure my_proc is
 18       v_tables t_my_array;
 19    begin
 20       for rec in (select table_name, 22 as ct from user_tables where rownum < 4) loop
 21          v_tables(rec.table_name) := rec.ct;
 22       end loop;
 23
 24       p := v_tables;
 25       for i in ( select * from pkg.array_to_nt())
 26       loop
 27         dbms_output.put_line(i.idx||' '||i.n);
 28       end loop;
 29    end;
 30
 31  end;
 32  /

Package body created.

SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> exec pkg.my_proc;
CODE18_SESSIONS 22
EXT 22
MSG 22

PL/SQL procedure successfully completed.

SQL>

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Column names

Column names - 2

More to Explore

PL/SQL demos

PL/SQL docs