Re: DBMS_LOB.SUBSTR failed to retrieve 4000 characters

From: <rahul.rdec06_at_gmail.com>
Date: Thu, 5 Sep 2013 08:52:32 -0700 (PDT)
Message-ID: <d4ac147d-bfec-44e2-b91c-3a57b7d81296_at_googlegroups.com>


On Friday, November 14, 2008 11:40:42 AM UTC, Vladimir M. Zakharychev wrote:
> 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

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!! Received on Thu Sep 05 2013 - 17:52:32 CEST

Original text of this message