Solved

how to solve this below issue ?

Posted on 2014-11-24
9
130 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 76

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 51

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 34

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:HainKurt
ID: 40462961
increase your buffer

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

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 51

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 51

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now