On Nov 13, 10:32 pm, yuri.de
...@gmail.com wrote:
> Hello,
> Oracle throws the error when trying to retrieve first 4000 characters
> from a CLOB column
> SELECT DBMS_LOB.SUBSTR(clob_column,4000, 1) FROM tbl
> ORA-06502: PL/SQL: numeric or value error: character string buffer too
> small
> ORA-06512: at line 1
> Depends on the row it works fine only when I reduce the chunk size
> from 4000 to 3550 or even less.
> I run it on Oracle 11G
> Please advice what is a problem.
> Thank you,
> Yuri
The SQL VARCHAR2 size is limited to 4000 BYTES, and you are trying to
fit 4000 CHARS into it, which, depending on the CLOB encoding and
content, may occupy more than 4000 bytes (for example when the
encoding is UTF-8 or anything like that and content features national
characters.) Reduce the amount to 2000 chars and there's good chance
you will never see ORA-6502 from DBMS_LOB.SUBSTR() again. Reduce it to
1000 chars and you definitely will never see it.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
