casting - How to tweak LISTAGG to support more than 4000 character in select query?

Question

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

I have a table in the below format.

Name     Department
Johny    Dep1
Jacky    Dep2
Ramu     Dep1

I need an output in the below format.

Dep1 - Johny,Ramu
Dep2 - Jacky

I have tried the 'LISTAGG' function, but there is a hard limit of 4000 characters. Since my db table is huge, this cannot be used in the app. The other option is to use the

SELECT CAST(COLLECT(Name)

But my framework allows me to execute only select queries and no PL/SQL scripts.Hence i dont find any way to create a type using "CREATE TYPE" command which is required for the COLLECT command.

Is there any alternate way to achieve the above result using select query ?

Answer 1

You should add GetClobVal and also need to rtrim as it will return delimiter in the end of the results.

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') 
  ORDER BY colname).GetClobVal(),',') from tablename;