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

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.

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.

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.

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; – ocdcoder Mar 3 at 16:55.

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.

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 3 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! – ocdcoder Mar 3 at 21:15 @Jokke, the select is just called once to generate the vars which is good enough for me.

Right? – ocdcoder Mar 3 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 4 at 14:20 ocdcoder, I meant the second select where table function is used. – Jokke Heikkilä Mar 4 at 19:09.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.