listagg function throwing error ORA-01489: result of string concatenation is too long

Question

I am using listagg after cte in stored proc. However, it is returning error ' ORA-01489: result of string concatenation is too long'. Below is my stored procedure.

CREATE OR REPLACE PROCEDURE sp_market
IS
    Names VARCHAR2(32767);
BEGIN
    WITH CTE(sqql) As 
    (
      SELECT   field_name sqql FROM   pld_medicare_config  
    )  
    SELECT listagg(sqql, ' UNION ALL ') within GROUP (ORDER BY 1) into Names   from CTE;      
END sp_market;

In select query, field_name returns long texts. I believe this is because listagg is not able to handle long characters that is result of select statement. How is work around for this issue?

Answer 1

Try XMLAGG instead, e.g.

with cte as (select id, field_name sqql from pld_medicare_config)
select rtrim (
          xmlagg (xmlelement (e, sqql || ', ') order by null).extract (
             '//text()'),
          ', ')
  into names
  from cte;