ORA-01489: result of string concatenation is too long

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

select rpad(' ',4000)||'A' as S
from dual

ORA-01489: result of string concatenation is too long

Whilst this error message is pretty clear in its meaning, the casual SQL user might not remember or even don't know that in Oracle, strings cannot exceed 4000 bytes inside SQL statements. This restriction however changes when we move from SQL to PL/SQL, where the limit for VARCHAR2 strings is somewhat bigger (32K bytes - 1).

declare
s   varchar2(32767);
begin
s := rpad(' ',4000)||'A'
end;

The string concatenation above will succeed without problems.
Note however that the restriction continues to apply to SQL inside PL/SQL:

declare
s   varchar2(32767);
begin
select rpad(' ',4000)||'A'
 into s
 from dual;
end;

Error report:
ORA-01489: result of string concatenation is too long
ORA-06512: at line 4

In conclusion, if you need to deal with strings larger than 4000 bytes in SQL, you should go for CLOBs, you can always split the string into smaller (4000 bytes) chunks later on, by means of CLOB functions like DBMS_LOB.SUBSTR.


See message translations for ORA-01489 and search additional resources.