Hi Tom. First of all, I just want to say thank you for creating such a forum. I have just started using Oracle and have learnt a lot from you here.

Now, my question is as follows:

Let's say I have a table with 3 columns:
create table T (col1 number, col2 number, col3 varchar2(10));

Column col1 is the primary key in this table.

Now after writing PLSQL code for a few months now, I always find myself in a situation where I like to query a table (like table T above) and load the columns in an associate array of records with col2 and col3 as the member of the record and col1 as the index.
example:
array(col1).col2 := 3;
array(col1).col3 := 'abc';

With this data structure in place, I can make cache of such table in PLSQL.

Right now, what I do is I bulk collect into an array of records of 3 member (col1, col2, col3) and then use another FOR LOOP to construct the associative array that i wanted. The code is simple, but it's just not as convenient and elegant.

My question is is there a new syntax in 10G for building such associate array using SQL query (bulk collect) in one shot? If there is no such syntax yet, do you think you can get this as an enhancement request to the next release of Oracle? I think it will be a very convenient and nice feature.

Thank You and Best Regards!

SQL> declare
  2    type rec is record ( x int, y int, z int );
  3    type reclist is table of rec index by pls_integer;
  4    r reclist;
  5  begin
  6    r := reclist(
  7          1=>rec(1,2,3),
  8          5=>rec(4,5,6),
  9          7=>rec(7,8,9)
 10          );
 11  end;
 12  /

PL/SQL procedure successfully completed.