with emp as
(select name
from (select 'Motorman' name from dual
union
select 'Captain' from dual
union
select 'Wilma' from dual
)
order by name
)
select ltrim(extract(XMLAgg(XMLElement ("NAME" ,','||name)) ,'NAME/text()') ,',') name_csv
from emp
Result will be
Captain,Motorman,Wilma
and the reverse....
select column_value name
from table (sys.odcivarchar2list ('Captain' ,'Motorman' ,'Wilma'))
returns
Name
Captain
Motorman
Wilma