PL/SQL Associative Array 1

[spperl]

Hi,

Is it possible to populate an associative array (or similar) based on the results of a query that combines multiple tables.

Pseudo code:

DECLARE

TYPE combinedTab IS TABLE OF combinedTab%ROWTYPE
INDEX BY BINARY_INTEGER;
combined_tab combinedTab;

BEGIN

SELECT tab1.col1, tab2.col2, tab3.col3
INTO  combined_tab(tab1.col1)
FROM tab1, tab2, tab3
WHERE << EQUI-JOINS >>

END;

Cleary 'combinedTab' is not a physical table so I was wondering if this is possible?

Any help or pointers would be appreciated.

Thanks.

 

[carp]

No, you cannot name a table and say its structure is based on its structure. You need to have a real table in order to use the tablename%ROWTYPE technique.

You will also need at least three columns in your table if you wish to store three values in it (as indicated by your query).

For what you appear to be trying to do, you might want to read about REF CURSORs.

Also, you might want to read about BULK COLLECT for loading tables from a query.

 

[spperl]

Hi,

Thanks for the quick reply, guidance and pointers to alternative solutions - this gives me something concrete to look into.

Thanks again.