SQL & PL/SQL

Hi All,

I am using Oracle 11.2.0.3.   I was using LISTAGG to generate CSV values for names but  as some of my values are greater than 4000 characters, it is throwing ORA-01489: result of string concatenation is too long.

I tried to used XMLAGG to resolve it . However , it espace special character.  (e.g. & will be escaped to & the xml safe equivalent)

eg

with t as

( SELECT 'A''PPLE' as fname from dual

union all

SELECT 'BALL&'  as fname from dual

union all

select 'CAT!'  as fname from dual )

select  rtrim(xmlagg(xmlelement( e ,   fname,',').extract('//text()') order by fname).GetClobVal(),',') AS NAME from t

RETURNS: A'PPLE,BALL&,CAT!

EXPECTED A'PPLE, BALL&,CAT!

Is it possible to disable escaping while using XMLAGG?

Is there any other recommended way to use string aggregation for more than 4000 characters?

Many Thanks!