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]
@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
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?
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