How to Handle the 'ORA-01489' Error When Using LISTAGG in Oracle SQL?

Cleverly solve the Oracle LISTAGG function string too long error

In Oracle SQL Developer, when using the LISTAGG function to aggregate data, you may encounter the "ORA-01489: result of string concatenation is too long" error. This error occurs when the concatenated output exceeds an internal limit (usually 4000 characters).

To work around this limitation, the XMLAGG function can be used as an alternative. This function allows aggregation of data into an XML document, thereby bypassing character restrictions.

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

This query uses the XMLAGG function to aggregate the values ​​of the specified column (colname) into an XML document. Then, use the GetClobVal() method to convert the XML document to the CLOB data type, thereby removing the character limit.

The final output is a comma-separated string of aggregate values ​​stored as CLOB values, allowing processing of large join outputs.

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