How to Handle ORA-01489: Result of String Concatenation is Too Long in Oracle?

ORA-01489: Result of String Concatenation is Too Long

When trying to concatenate fields and use the LISTAGG function in a single view, you may encounter the error "ORA-01489: result of string concatenation is too long."

Cause:

This error indicates that you have exceeded the SQL limit of 4000 bytes, which applies to the LISTAGG function.

Workaround 1: XMLAGG

To work around this limitation, you can use the XMLAGG function instead of LISTAGG. XMLAGG allows for larger concatenated results. For example:

SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()')
.GetClobVal(),',') very_long_text
FROM
(SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
)

Workaround 2: Limit Concatenated Output

If your concatenated result is large, you can limit it using a combination of rtrim and substr functions. For example:

SELECT rtrim(substr(text1 || '-' || text2, 1, 3999)) || ', '
FROM source

Workaround 3: Concatenate XMLAGG Results

If you need to concatenate multiple columns that each have 4000 bytes, concatenate the XMLAGG output of each column. For example:

SELECT ID,
rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()') ).GetClobVal(), ',')
||
rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()') ).GetClobVal(), ',')
AS very_long_text
FROM DATA
GROUP BY ID
ORDER BY ID;

The above is the detailed content of How to Handle ORA-01489: Result of String Concatenation is Too Long in Oracle?. For more information, please follow other related articles on the PHP Chinese website!