Blog

ORA-01489: 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.

Example:

SQL> SELECT LPAD('x',4000,'x') || LPAD('x',4000,'x')  || LPAD('x',4000,'x') FROM DUAL;
SELECT LPAD('x',4000,'x') || LPAD('x',4000,'x')  || LPAD('x',4000,'x') FROM DUAL
                                                                                                   *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Problem Description:

The problem with this query is with the use of CONCAT operator (||).

e.g.: select char1 || char2 from dual Concat operator returns char1 concatenated with char2. The string returned is in the same character set as char1. So here concat operator is trying to return varchar2, which has limit of 4000 characters and getting exceeded.

This problem may also come when we try to CONCAT a VARCHAR2 with CLOB. e.g.: select char1 || clob from dual

So here we can simply convert its first string to CLOB and avoid this error. After converting first string to CLOB, CONCAT operator will return string of CLOB type

Solution:

SELECT TO_CLOB(LPAD('x',4000,'x')) || LPAD('x',4000,'x')  || LPAD('x',4000,'x') 
FROM DUAL

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.