Link to home
Start Free TrialLog in
Avatar of deve_thomos
deve_thomosFlag for India

asked on

how to solve this below issue ?

Hi Expert,
I have below query
select DBMS_LOB.SUBSTR (description, 4000, 1) from test;
description  column is clob datatype.
it is showing me  below error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"

Regards
Thomos
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I don't think the select itself will cause that error.

What else are you doing?

My guess is using the results of the select on an insert into another table.  It is likely the column you are inserting into is causing the error.

Even if it is defined as a varchar2(4000), if you are running a multibyte characterset it may not be able to handle 4000.
try

select DBMS_LOB.SUBSTR (description, 2000, 1) from test;

or

select DBMS_LOB.SUBSTR (description, 100, 1) from test;

do you get same error?
One quick comment on English word order.  Yes, most speakers of English understand what you mean by this:
"I have below query...".  But, that is not considered correct word order in English.  Standard English word would be either:

"I have the query below:"

Or,

"I have the following query:"

Why?  The word "below" is considered an adverb in English, and we never but adverbs between "the" and a noun.  We put adverbs following the noun.  Adjectives, including: big, little, upper, lower, red, blue, etc., can be used there.

Can you explain a bit more what you are trying to do with the CLOB value?  Are you trying to get just one character from this, the character that is at position 4000?
Avatar of deve_thomos

ASKER

hi slightwv,
just i am selecting the data from the test table,
i have multiple records.
it is a clob dta type, i want to restrict up to  4000 charecters
it is showing me error, can you suggest me what can i do for this.

Regards
Thomos
increase your buffer

SET BUFFER 10000;
your query here...
This is not clear to me: "i want to restrict up to  4000 characters".

If you want to retrieve the first 4000 characters, I think you need syntax more like this:
select DBMS_LOB.SUBSTR (description, 1, 4000) ...
markgeer: parameters are these:

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

He uses correctly...
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks