ORA-01489: result of string concatenation is too long

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.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e900.htm


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


Related Posts:
 - PLS-00172: string literal too long
 - ORA-02303: cannot drop or replace a type with type or table dependents
 - ORA-01460 unimplemented or unreasonable conversion requested
 - ORA-01723: zero-length columns are not allowed
 - ORA-06502: PL/SQL: numeric or value errorstring
 - ORA-01722: invalid number

16 comments:

  1. Thanks for sharing this, This solved my problem.

    ReplyDelete
    Replies
    1. Nice to know, that It was useful for you !!!

      Delete
  2. It is ok now but it becomes very slow when table has huge number of row

    ReplyDelete
    Replies
    1. Speed is directly proportional to the amount of your data

      Delete
    2. You are say opposite of what it actually is.

      Delete
    3. Let me re-phase it Speed is directly proportional to the amount of your data and the plan of the query

      Delete
  3. Hero of the day! Saved my life! =)

    ReplyDelete
    Replies
    1. Nice to know that I could help you, keep visiting for more useful articles..

      Delete
  4. Is the a way to do this in "separate" columns?

    ReplyDelete
    Replies
    1. Hi Tammy, Did not get properly what you want to ask

      Delete
  5. Very Helpful Post!!!!

    Thanks and best...Rich

    ReplyDelete
  6. Thanks!! It solved the issue..

    ReplyDelete
  7. Thanks!! It solved the issue...

    ReplyDelete