Oracle equivalent of PostgreSQL ARRAY_AGG

Question

I'm looking for an aggregate function supported by Oracle SQL to collect values into arrays, something similar to PostgreSQL ARRAY_AGG. I already use listagg for strings, I use cast() with collect() and multiset() but it requires to declare a table type. Is there a more straightforward way of emulating ARRAY_AGG with Oracle SQL?

Answer 1

It's possible to emulate ARRAY_AGG with Oracle SQL without declaring new types only in very simple cases when build-in types can do the job, for example sys.odcivarchar2list (varray of varchar2) and sys.odcinumberlist (varray of number), with cast() and collect() or multiset():

select 
 cast(multiset(select id from my_pretty_table order by id) as sys.odcinumberlist)
from 
 dual;
select
 cast(collect(id order by id) as sys.odcinumberlist)
from
 my_pretty_table;

Note that this solution is still a lot less flexible than PostgreSQL ARRAY_AGG.