Workaround for ORA-01489

08 February 2015

Just saw this issue while concatenating fields in Oracle.

Issue

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) alias FROM table_name;

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

Workaround

SELECT RTRIM(XMLAGG(XMLELEMENT(e, column_name, ',').EXTRACT('//text()') ORDER BY column_name).GETCLOBVAL(), ',') alias FROM table_name;