Re: DBMS_LOB.SUBSTR failed to retrieve 4000 characters

On Nov 13, 10:32 pm, yuri.de..._at_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 Received on Fri Nov 14 2008 - 05:40:42 CST