Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to solve this below issue ?

Posted on 2014-11-24
9
Medium Priority
?
152 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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 78

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 61

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 61

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 61

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 61

Accepted Solution

by:
HainKurt earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

972 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