DBMS_LOB.SUBSTR failed to retrieve 4000 characters

[yuri....@gmail.com]

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

[Vladimir M. Zakharychev]

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

[rahul....@gmail.com]

Hi Vladimir,

I am using 2000 in my sql query and there is no error but I am not getting full row data which is there in as blob. how can we have the full data if we cannot increase the length more than 2000. I have also tried with 3000 but getting same error again.
Please help.

Thanks in advance!!

[joel garry]

You are responding to a five year old thread. Good luck.

jg
--
@home.com is bogus.
http://packetstormsecurity.com/files/123079/paypalaksession-fail.txt