How to Overcome ORA-01489 Error When Using LISTAGG in Oracle SQL?

Oracle SQL LISTAGG Function and ORA-01489 Error

The LISTAGG function, useful for concatenating strings within Oracle SQL Developer, can generate the "ORA-01489: result of string concatenation is too long" error. This limitation stems from LISTAGG's output being restricted to a maximum of 4000 characters.

Solution using XMLAGG

To overcome this 4000-character limit, employ the XMLAGG function as a more flexible alternative:

<code class="language-sql">SELECT RTRIM(XMLAGG(XMLELEMENT(E, colname, ',').EXTRACT('//text()') ORDER BY colname).GetClobVal(), ',') AS LIST
FROM tablename;</code>

XMLAGG returns a CLOB data type, capable of handling significantly larger string lengths, thus eliminating the ORA-01489 error. This approach effectively aggregates extensive datasets without the constraints imposed by LISTAGG.

The above is the detailed content of How to Overcome ORA-01489 Error When Using LISTAGG in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!