Solved

how to solve this below issue ?

Posted on 2014-11-24
9
143 Views
Last Modified: 2015-01-19
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
0
Comment
Question by:deve_thomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40462903
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.
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 40462907
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?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40462914
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:deve_thomos
ID: 40462917
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
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 40462961
increase your buffer

SET BUFFER 10000;
your query here...
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40462962
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) ...
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 40462963
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...
0
 
LVL 56

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40462969
did you try

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

or

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

???? 4K is max which is 2000 bytes... so you should use max 2000
0
 

Author Closing Comment

by:deve_thomos
ID: 40558141
Thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question