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

Question

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?

score 2 · Accepted Answer · answered May 01 '13 at 06:31

2

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.

answered May 01 '13 at 06:31

haki

Fortunately for me, I use [the DBI](https://metacpan.org/module/DBI), and the [Oracle Driver knows](https://metacpan.org/module/DBD::Oracle#OBJECT-COLLECTION-DATA-TYPES) to convert collections to arrays. Makes me wonder, though: Can I define a column as storing the result of a `COLLECT()`? Or must it be a VARRAY? – theory May 01 '13 at 16:05
have you tried casting .... `select cast(collect(col) as my_array) from ... ` ? – haki May 01 '13 at 17:19
Yes, that works fine. I meant not having to create a type, but just the object returned by `collect()` as the column type. That does not appear to be do-able. – theory May 02 '13 at 16:00
you have two build in array types in `dbms_utility` - see it you can use them. – haki May 03 '13 at 06:16

1 Answers1