Just ran into this inconsistent (I think) behavior. According to SQL Reference Manual (Data Conversion):
- During concatenation operations, Oracle converts from noncharacter data types to
CHAR or NCHAR.
Now what are character data types? Same SQL Reference Manual states CLOB isn't character data type. And PL/SQL works as expected:
SQL> declare
2 v_clob clob := lpad('x',32768,'x');
3 begin
4 v_clob := v_clob || 1;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2
SQL>
However, no exception in SQL:
SQL> select to_clob(lpad('x',32768,'x')) || 1 from dual;
TO_CLOB(LPAD('X',32768,'X'))||1
--------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SQL>
SY.
P.S I like SQL behavior much better. It makes much more sense to treat CLOB as character data type.