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-01403: no data found 
 - Append String to CLOB in Optimized way

27 comments:

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

    ReplyDelete
  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
  8. how would this work if I were to export my selection to a csv file? because I tried this fix on my select statement but now my csv file doesnt export anymore. thanks

    ReplyDelete
  9. To_CLOB('chunk1') || To_COLB('chunk2') solved my problem.Thanks

    ReplyDelete
  10. Hi all,
    could you please help me out of this issue
    Query:
    select nvl(t.tasksummary,'None')
    ||';'||CONCAT(CHR(39),e.id)
    ||';'||initcap(e.name)
    ||';'||CONCAT(CHR(39),e.coachid)
    from tasks t
    where id=11
    how can use CLOB over concat

    Thanks

    ReplyDelete
    Replies
    1. http://nimishgarg.blogspot.com/2014/11/append-string-to-clob-in-optimized-way.html

      Delete
  11. Thanks Nimish, solved my problem.

    ReplyDelete
  12. Hello there! I know this is kind of Uniraj BA 2nd Year Result (Private & Regular) website will surely helpful for every one.

    ReplyDelete
  13. Muchas gracias, me funciono

    ReplyDelete
  14. Such a simple solution, thanks!

    ReplyDelete
  15. pg ดีที่สุดเกมออนไลน์ PG SLOT สล็อตบนโทรศัพท์เคลื่อนที่ แบบใหม่ตอนนี้ ของโลก สมัครเล่น SLOT วันนี้รับโบนัส แรกเข้า 100% ในทันทีทันใด โบนัส 50% สำหรับสมาชิกใหม่

    ReplyDelete