By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
| MY_ARRAY_STR |
|---|
| a,b,c |
| d,e |
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
| MY_ARRAY_STR |
|---|
| 'a','b','c' |
| 'd','e' |
| 'f','','g' |