how to solve this below issue ?

Posted on 2014-11-24
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"

Question by:deve_thomos
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
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.
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40462907

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


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

do you get same error?
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:"


"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?
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.


Author Comment

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.

LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40462961
increase your buffer

your query here...
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) ...
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40462963
markgeer: parameters are these:

   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)

He uses correctly...
LVL 53

Accepted Solution

Huseyin KAHRAMAN earned 500 total points
ID: 40462969
did you try

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


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

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

Author Closing Comment

ID: 40558141

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 51
Excess Redo 3 49
Oracle - BLOB Extract Line 2 24
Oracle perfomance tuneing. 3 47
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

710 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