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?
Add a comment
|
1 Answer
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.