PL/SQL: Selecting from a table into an assoc array

Question

I am trying to select data into a pl/sql associative array in one query. I know I can do this with a hardcoded key, but I wanted to see if there was some way I could reference another column (the key column) instead.


DECLARE
TYPE VarAssoc IS TABLE OF varchar2(2) INDEX BY varchar2(3);
vars VarAssoc;
BEGIN
SELECT foo, bar INTO vars(foo) FROM schema.table;
END;

I get an error saying foo must be declared when I do this. Is there some way to create my associate array in a single query or do I need to fall back on a FOR loop?

I just read about "BULK COLLECT" (http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/13_elems045.htm) which works if I didn't care anything about the key, but I want to set the key and it didn't show a way to do that. — Seaux, Mar 03 '11 at 16:20

score 19 · Accepted Answer · answered Mar 03 '11 at 18:22

Just read your comment on APC's answer, it sounds like you figured this out on your own. But I figured I'd put the answer in anyway for future searchers.

This is simpler code, but does not have the speed advantage of using BULK COLLECT. Just loop through the rows returned by the query and set the elements in the associative array individually.

DECLARE
  TYPE VarAssoc IS TABLE OF varchar2(200) INDEX BY varchar2(30);
  vars VarAssoc;
BEGIN
  FOR r IN (SELECT table_name,tablespace_name FROM user_tables) LOOP
    vars(r.table_name) := r.tablespace_name;
  END LOOP;

  dbms_output.put_line( vars('JAVA$OPTIONS') );
END;

score 6 · Answer 2 · answered Mar 03 '11 at 16:46

It would be neat if it were possible but that isn't a straightforward way of acheiving this.

What we can do is load the data into a regular PL/SQL collection and then load that into an associative array. Whethter this is faster than just looping round the table is a matter of tatse: it probably doesn't matter unless we're dealing with loads of data.

Given this test data ...

SQL> select * from t23
  2  order by c1
  3  /

C1 C2
-- ---
AA ABC
BB BED
CC CAR
DD DYE
EE EYE
ZZ ZOO

6 rows selected.

SQL>

...we can populate an associative array in two steps:

SQL> set serveroutput on
SQL>
SQL> declare
  2      type varassoc is table of varchar2(3) index by varchar2(2);
  3      vars varassoc;
  4
  5      type nt is table of t23%rowtype;
  6      loc_nt nt;
  7
  8  begin
  9      select * bulk collect into loc_nt from t23;
 10      dbms_output.put_line('no of recs = '||sql%rowcount);
 11
 12      for i in loc_nt.first()..loc_nt.last()
 13      loop
 14          vars(loc_nt(i).c1) := loc_nt(i).c2;
 15      end loop;
 16
 17      dbms_output.put_line('no of vars = '||vars.count());
 18
 19      dbms_output.put_line('ZZ = '||vars('ZZ'));
 20
 21  end;
 22  /
no of recs = 6
no of vars = 6
ZZ = ZOO

PL/SQL procedure successfully completed.

SQL>

The real question is probably whether populating an associative array performs better than just selecting rows in the table. Certainly if you have 11g Enterprise edition you should consider result set caching instead.

Hey APC, Thanks for the explanation! Unfortunately I'm not on 11g or I would consider result set caching. I ended up just using a for loop cursor and something like: vars(cursor.foo) := cursor.bar; — Seaux, Mar 03 '11 at 16:55

score 0 · Answer 3 · answered Mar 03 '11 at 17:55

0

are you absolutely married to associative arrays? And I assume that you are doing this because you want to be able to do a lookup against the array using a character key.

If so, have you considered implementing this as a collection type instead?

e.g.

CREATE OR REPLACE TYPE VAR_ASSOC as OBJECT(
  KEYID   VARCHAR2(3),
  DATAVAL VARCHAR2(2)
)
/

CREATE OR REPLACE TYPE VAR_ASSOC_TBL AS TABLE OF VAR_ASSOC
/

CREATE OR REPLACE PROCEDURE USE_VAR_ASSOC_TBL
AS
  vars Var_Assoc_tbl; 
  -- other variables...
BEGIN 
    select cast ( multiset (
                        select foo as keyid,
                               bar as dataval
                        from   schema.table
                           ) as var_Assoc_tbl
                )
     into vars
     from dual;   
     -- and later, when you want to do your lookups
     select  ot.newfoo 
            ,myvars.dataval
            ,ot.otherval
     into   ....       
     from   schema.other_Table ot
     join   table(vars) as myvars
     on     ot.newfoo = myvars.keyid;
end;
/

This gives you the lookup by character key value and lets you do everything in bulk.

answered Mar 03 '11 at 17:55

Michael Broughton

4,045
14
12

I don't think this is a good practice and it's not so efficient way to achieve the goal. You are selecting the whole "vars" nested table every time when this select is done. Associative array with exists function is very efficient and good practice in everywhere in your application. – Jokke Heikkilä Mar 03 '11 at 19:44
@Michael, thanks for the foresight in trying to more efficiently solve the larger problem. You're right, except for how I intend to use the array (collection). But this is a really cool way to do it. I'll keep it in mind! Thanks! – Seaux Mar 03 '11 at 21:15
@Jokke, the select is just called once to generate the vars which is good enough for me. Right? – Seaux Mar 03 '11 at 21:20
Jokke, yes if you were going to repeatedly call the lookup select you could have some efficiency issues. On the other hand, if you can also do a bulk fetch of all of the data you are going to be matching to the collection then this will work very efficiently to do it all as a set-based operation whereas you do not have this option if using the array. – Michael Broughton Mar 04 '11 at 14:20
ocdcoder, I meant the second select where table function is used. – Jokke Heikkilä Mar 04 '11 at 19:09

PL/SQL: Selecting from a table into an assoc array

3 Answers3

Linked