[SOLVED] How to tweak LISTAGG to support more than 4000 character in select query?

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

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 ?

Solution

if you cant create types (you can't just use sql*plus to create on as a one off?), but you're OK with COLLECT, then use a built-in array. There's several knocking around in the RDBMS. run this query:

select owner, type_name, coll_type, elem_type_name, upper_bound, length 
 from all_coll_types
 where elem_type_name = 'VARCHAR2';

e.g. on my db, I can use sys.DBMSOUTPUT_LINESARRAY which is a varray of considerable size.

select department, 
       cast(collect(name) as sys.DBMSOUTPUT_LINESARRAY) 
  from emp 
 group by department;