Perhaps try a custom aggregate then
SQL> create or replace
2 type listagg_clob
3 as object
4 (
5 l_result clob,
6
7 static function odciaggregateinitialize( ctx in out listagg_clob ) return number,
8 member function odciaggregateiterate( self in out listagg_clob, value in clob ) return number,
9 member function odciaggregateterminate( self in listagg_clob, returnvalue out clob, flags in number ) return number,
10 member function odciaggregatemerge( self in out listagg_clob, ctx2 in listagg_clob ) return number
11 );
12 /
Type created.
SQL>
SQL>
SQL> create or replace
2 type body listagg_clob
3 is
4
5 static function odciaggregateinitialize( ctx in out listagg_clob ) return number is
6 l_tmp clob;
7 begin
8 dbms_lob.createtemporary( l_tmp, true, dbms_lob.call );
9 ctx := listagg_clob( l_tmp );
10 return odciconst.success;
11 end;
12
13 member function odciaggregateiterate( self in out listagg_clob, value in clob ) return number is
14 begin
15 if dbms_lob.getlength( self.l_result ) > 0 then
16 dbms_lob.append( self.l_result, ',' );
17 end if;
18 dbms_lob.append( self.l_result, value );
19 return odciconst.success;
20 end;
21
22 member function odciaggregateterminate( self in listagg_clob, returnvalue out clob, flags in number) return number is
23 begin
24 returnvalue := self.l_result;
25 return odciconst.success;
26 end;
27
28 member function odciaggregatemerge( self in out listagg_clob, ctx2 in listagg_clob ) return number is
29 begin
30 dbms_lob.append( self.l_result, ctx2.l_result );
31 return odciconst.success;
32 end;
33 end;
34 /
Type body created.
SQL>
SQL>
SQL> create or replace
2 function listaggc( input clob )
3 return clob
4 parallel_enable aggregate
5 using listagg_clob;
6 /
Function created.
SQL>
SQL> select listaggc(ename) from emp;
LISTAGGC(ENAME)
--------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL
1 row selected.
SQL>