Search code examples
arraysoracle-databasesubqueryaggregatevarray

How do I select an array from the results of a query?

I've created a VARRAY type:

CREATE TYPE my_array AS varray(1024) OF VARCHAR2(512);

I know I can create an instance of my_array from a list of values using my_array():

SELECT my_array('foo', 'bar');

But is there a way to create an instance from the results of a query? I'm thinking something like this:

SELECT my_array(SELECT table_name FROM all_tables WHERE owner = 'SCOTT')
  FROM dual;

Or, failing that, an aggregate that returns a my_array:

SELECT my_array_agg(table_name) FROM all_tables WHERE owner = 'SCOTT';

Is this do-able in Oracle?

Solution

  • Bulk collect in plsql 

    declare 
arr my_array;
begin 
SELECT table_name bulk collect into arr FROM all_tables WHERE owner = 'SCOTT';
end;

    or 

    select collect(table_name) from all_tables where owner = 'SCOTT';

    but you cant do anything with a collection like that in sqlplus.