Get varray values as concatenated text list

Question

In db<>fiddle for Oracle 18c:

If I select a varray, then db<>fiddle returns an empty resultset, which is misleading.

with data as (select sys.odcivarchar2list('a', 'b', 'c') as my_array from dual)
select my_array from data

Result:

MY_ARRAY
--------   [resultset is empty]

db<>fiddle

@JackDouglas said this in a related post:

PHP’s oci_fetch_all doesn’t like abstract data types like SDO_GEOMETRY, I’m getting errors like this in the logs:

PHP Warning:  oci_fetch_all(): ORA-00932: inconsistent datatypes: expected CHAR got ADT PHP 
Warning:  oci_fetch_all(): ORA-00932: inconsistent datatypes: expected CHAR got ADT

Source: Return a value/row when selecting SDO_GEOMETRY

---

As a workaround, I want to return the varray's values as a concatenated text list (the same way it works in SQL Developer).

Like this:

with data as (select sys.odcivarchar2list('a', 'b', 'c') as my_array from dual)
select varray_list(my_array) from data
--          ^^^ A fake function.

Result:

MY_ARRAY
--------   
SYS.ODCIVARCHAR2LIST('a', 'b', 'c')
--or
'a', 'b', 'c'
--or 
a,b,c

---

Question:

In a query, is there a way to get a varray's values as a concatenated text list?

Answer 1

LATERAL join a table collection expression and then use LISTAGG:

with data (my_array) as (
  select sys.odcivarchar2list('a', 'b', 'c') from dual union all
  select sys.odcivarchar2list('d', 'e') from dual
)
select s.my_array_str
from   data d
       CROSS JOIN LATERAL (
         SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY ROWNUM)
                  AS my_array_str
         FROM   TABLE(d.my_array)
       ) s

Which outputs:

MY_ARRAY_STR
a,b,c
d,e

or, if you want the surrounding quotes:

with data (my_array) as (
  select sys.odcivarchar2list('a', 'b', 'c') from dual union all
  select sys.odcivarchar2list('d', 'e') from dual union all
  select sys.odcivarchar2list('f', NULL, 'g') from dual
)
select s.my_array_str
from   data d
       CROSS JOIN LATERAL (
         SELECT LISTAGG(
                  '''' || column_value || '''',
                  ','
                ) WITHIN GROUP (ORDER BY ROWNUM)
                  AS my_array_str
         FROM   TABLE(d.my_array)
       ) s

Which outputs:

MY_ARRAY_STR
'a','b','c'
'd','e'
'f','','g'

db<>fiddle here