select rpad(' ',4000)||'A' as S
ORA-01489: result of string concatenation is too longWhilst 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).
declareThe string concatenation above will succeed without problems.
s := rpad(' ',4000)||'A'
Note however that the restriction continues to apply to SQL inside PL/SQL:
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.
select rpad(' ',4000)||'A'
ORA-01489: result of string concatenation is too long
ORA-06512: at line 4
See message translations for ORA-01489 and search additional resources.