Re: DBMS_LOB.SUBSTR failed to retrieve 4000 characters

On Thursday, September 5, 2013 8:52:32 AM UTC-7, rahul...._at_gmail.com wrote:
> 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!!

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

jg

-- 
_at_home.com is bogus.
http://packetstormsecurity.com/files/123079/paypalaksession-fail.txt