deve_thomos
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
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
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?
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?
"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?
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
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...
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) ...
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
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.