ORA-01489: result of string concatenation is too long using listagg function oracle

ORA- Error: 

1.
SQL>select listagg(description,',' ) within group (order by description) from mis_report_2020;

select listagg(description,',' ) within group (order by description) from mis_report_2020
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long




2.
SQL> select listagg(description,',' ON OVERFLOW TRUNCATE) within group (order by description) from mis_report_2020;

select listagg(description,',' ON OVERFLOW TRUNCATE) within group (order by description)  from mis_report_2020

*
ERROR at line 1:
ORA-00907: missing right parenthesis




CAUSE: 

description column output is over 4000 bytes and it is exceeding limit to list the results as comma separated lines.


As a solution to ORA-01489: result of string concatenation is too long , "ON OVERFLOW TRUNCATE" can be used only if database is oracle 12c , prior to which this clause is not supported and would lead to ORA-00907: missing right parenthesis



With desupport of WM_CONCAT function in oracle 12c,  LISTAGG function is available for list output processing. 

Although LISTAGG function is available since 11g but "on overflow truncate" feature is added from oracle 12c on-wards ,  which ignores the output ranging above limit without reporting the error and may also cause deselection of desired string.


Solution: 

As a solution to ORA-01489/ORA-00907 in oracle 11g below floor function can be used to calculate the exact max length and separate the output to next line accordingly.




SQL> with RESINTO as (select
floor(10000/(max(length(description)+LENGTH(',')))) as MAX_FIELD_LENGTH
from mis_report_2020)
select LISTAGG(description,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), description)
from sp_domain_roles, RESINTO
group by floor(rownum/MAX_FIELD_LENGTH)

Alternatively , to reduce the query size max length can be pre calculated and used within to get same result.

SQL> select
floor(10000/(max(length(description)+LENGTH(',')))) as MAX_FIELD_LENGTH
from mis_report_2020;

MAX_FIELD_LENGTH

----------------

114

1 row selected

SQL> select LISTAGG(description,',') WITHIN GROUP(ORDER BY floor(rownum/114), description)
from mis_report_2020
group by floor(rownum/114);

Solved !!