What causes ORA-01489?

Pipeline builds a long SQL or JSON string via concatenation and exceeds the VARCHAR2 limit. Aggregation function such as LISTAGG produces a result longer than 4000 characters. Multiple text columns are concatenated together producing an oversized result

How do I fix ORA-01489?

Step 1: Use CLOB instead of VARCHAR2 for the result: `CAST(col AS CLOB) || other_col`.. Step 2: For LISTAGG, use `ON OVERFLOW TRUNCATE` to limit the result: `LISTAGG(col, ',') WITHIN GROUP (ORDER BY id) ON OVERFLOW TRUNCATE`.. Step 3: Limit the number of concatenated rows or add a WHERE clause to reduce the set.. Step 4: Move the concatenation to the application layer where string size limits are more flexible.. Step 5: Use `XMLAGG` + `XMLELEMENT` + `GETCLOBVAL` for unlimited aggregation.

Low severitydata

Oracle Database Error:
ORA-01489

What does this error mean?

A string concatenation operation in Oracle produces a result that exceeds the maximum length for the data type (4000 bytes for VARCHAR2, 32767 in PL/SQL).

Common causes

1Pipeline builds a long SQL or JSON string via concatenation and exceeds the VARCHAR2 limit
2Aggregation function such as LISTAGG produces a result longer than 4000 characters
3Multiple text columns are concatenated together producing an oversized result

How to fix it

1Step 1: Use CLOB instead of VARCHAR2 for the result: `CAST(col AS CLOB) || other_col`.
2Step 2: For LISTAGG, use `ON OVERFLOW TRUNCATE` to limit the result: `LISTAGG(col, ',') WITHIN GROUP (ORDER BY id) ON OVERFLOW TRUNCATE`.
3Step 3: Limit the number of concatenated rows or add a WHERE clause to reduce the set.
4Step 4: Move the concatenation to the application layer where string size limits are more flexible.
5Step 5: Use `XMLAGG` + `XMLELEMENT` + `GETCLOBVAL` for unlimited aggregation.

Example log output

ORA-01489: result of string concatenation is too long
SQL: SELECT LISTAGG(tag, ',') WITHIN GROUP (ORDER BY id) FROM product_tags GROUP BY product_id

Frequently asked questions

What is the VARCHAR2 limit in Oracle SQL vs PL/SQL?

In SQL, VARCHAR2 is limited to 4000 bytes (or 32767 in extended string mode with MAX_STRING_SIZE=EXTENDED). In PL/SQL, the limit is 32767 bytes. For larger strings, use CLOB.

Source · docs.oracle.com/error-help/db/ora-01489