Custom listagg function using cursor to bypass ORA-01489

When using build-in listagg function one should always consider that resulting string must have a length less or equal to 4000 characters. If does exceed then an error ‘SQL Error: ORA-01489: result of string concatenation is too long’ is thrown.
Here’s a package implementing a custom aggregate function that bypasses this restriction.


create or replace PACKAGE "PK_UTILS" AS

type ref_cur is ref cursor;

function LISTAGG_CLOB( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob;

END PK_UTILS;
/
create or replace PACKAGE BODY "PK_UTILS" AS

function listagg_clob( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob
as
v_single_value clob;
v_result clob;
begin
fetch a_cur into v_single_value;
if ( a_cur%NOTFOUND ) then
goto FIN;
end if;
v_result := v_single_value;
loop
fetch a_cur into v_single_value;
exit when a_cur%NOTFOUND;
v_result := v_result || a_delimiter || v_single_value;
end loop;
<<FIN>>
return v_result;
end;

END PK_UTILS;
/

And here is a small user test emulating ‘within group (order by …)’ functionality.


with prepared as (
select 1 a, 1 b from dual
union all
select 2 a, 2 b from dual
union all
select 3 a, 1 b from dual
union all
select 4 a, 2 b from dual
)
select q.b
, pk_utils.listagg_clob( cursor( select a from prepared where b = q.b order by a desc ) , ',' )
from (
select b
from prepared
group by b
) q
;/

B LISTAGG_
---------- ------------------
1 3,1
2 4,2

Leave a Reply

Your email address will not be published. Required fields are marked *