declare r clob; begin r := lpad('0',32768,'0'); end;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"
So this tells me that a CLOB data type in PL/SQL can only be as big as a VARCHAR2, correct? Any one variable in PL/SQL can be bigger than this?
a quick test to demonstrate that the limitation is with lpad as rp0428 says, not with the clob:
r clob := '';
for i in 1..8
r := r || lpad('0',32767,'0');
dbms_output.put_line(length(r)); -- length will be 8*32767 = 262136
A quick look in the manual would have shown you that LPAD returns the same datatype as its first argument : LPAD
The string returned is of
VARCHAR2data type if
expr1is a character data type,
expr1is a national character data type, and a LOB if
expr1is a LOB data type.
So, this works :
2 r clob;
4 r := lpad(to_clob('0'), 32768, '0');
PL/SQL procedure successfully completed