Transforming single-row result set into an associative array

Say, I have the following query:

select 1 foo, 2 bar, 3 baz from dual;

Basically, this query always returns me one row. I need to create an associative array from that, one of this kind:

arr('foo') = 1;
arr('bar') = 2;
arr('baz') = 3;

I know that result set is only o开发者_如何学JAVAne row. I don't know neither columns amount nor columns names.

Any ideas?

Thanks.

upd:

A friend of mine found a nice and elegant, XML-involving solution:

  SELECT
   XMLTYPE(EXTRACT(VALUE(T), '/*') .GETSTRINGVAL()) .GETROOTELEMENT() NODE,
   EXTRACTVALUE(COLUMN_VALUE, '/*') NODEVALUE
    FROM
   TABLE(XMLSEQUENCE(XMLTYPE((CURSOR
   (
      --this is the query that needs to be transformed
      SELECT
       *
        FROM
       some_table
       WHERE some_table.id = 123

   )
   )) .EXTRACT('/ROWSET/ROW/*'))) T;

Here is a simple package which populates an associative array from a key value pair table. This is the easy case.

create or replace package dynaa as
    procedure pop;
    procedure print;
end;
/

create or replace package body dynaa as

    type aa is table of number
        index by varchar2(30);
    this_aa aa;

    procedure pop
    is
    begin
        for r in ( select * from t42 )
        loop
            this_aa (r.id) := r.col1;
        end loop;
    end pop    ;

    procedure print
    is
        idx varchar2(30);
    begin
        idx := this_aa.first();
        while idx is not null
        loop
            dbms_output.put_line(idx ||'='||this_aa(idx));
            idx := this_aa.next(idx);
        end loop;
    end print;
end;
/

And it works quite neatly....

SQL> set serveroutput on
SQL>
SQL> exec dynaa.pop

PL/SQL procedure successfully completed.

SQL> exec dynaa.print
DAISY HEAD MAISIE=6969
FOX IN SOCKS=4242
MR KNOX=2323

PL/SQL procedure successfully completed.

SQL>

But what you want is more complicated: to dynamically populate the array without knowing the projection of the query. The following is a horribly simplistic implementation, which will work if the table in question contains a single row. It can easily be extended to handle a table with multiple rows.

We overload the POP() procedure thus:

procedure pop
    ( tabname user_tab_columns.table_name%type );

procedure pop
    ( tabname user_tab_columns.table_name%type );
is
    n number;
begin
    for r in ( select column_name 
               from user_tab_columns
               where table_name = tabname)
    loop
        execute immediate 'select '||r.column_name||' from '||tabname into n;
        this_aa (r.column_name) := n;
    end loop;
end pop    ;

So, this works too:

SQL> exec dynaa.pop('T23')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dynaa.print
COL1=2323
COL2=4242
COL3=6969

PL/SQL procedure successfully completed.

SQL>

I'm not too worried about the performance hit of initialising the array in such a shonky fashion. Given that the record stays in cache it isn't too bad. And if you're calling POP() so often that performance does become an issue then you probably shouldn't be using an associative array in the first place: either regular look-ups or resultset caching would be better options.

A very short answer is to look at old-style (pre Oracle 9) dynamic SQL using the DBMS_SQL package. You can use the DESCRIBE_COLUMNS to get the number (and data types) of the columns and COLUMN_VALUE for every entry in the PL/SQL table returned by DESCRIBE_COLUMNS to fetch each column value individually.

After that, it's pretty easy to turn a row into an associative array.