Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small (Oracle 10g)
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #423311] Wed, 23 September 2009 05:41 Go to next message
antenc
Messages: 13
Registered: September 2009
Location: UK
Junior Member
Hi there,

I wonder if anyone can be of assistance with this problem that I am getting:

SELECT
UNIQUE UPPER(C.CRIME_URN) CRIME_NUMBER,
UPPER(C.SECURITY_FLAG) SECURITY_FLAG,
DBMS_LOB.SUBSTR(C.CRIME_TEXT) CRIME_TEXT
FROM
CRIME_INTERIM C

When running the SQL above I get the following error. If I limit the number of records returned using a condition in the WHERE clause, this seems to be ok, however I need to be able to query every record in the table. I am not sure of another way of dealing with CLOBs, which seems to be the issue here.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

If I can resolve this issue, I will need to access this table remotely using a DBLINK, and so will be using the following query:

SELECT
UNIQUE UPPER(C.CRIME_URN) CRIME_NUMBER,
UPPER(C.SECURITY_FLAG) SECURITY_FLAG,
DBMS_LOB.SUBSTR(C.CRIME_TEXT) CRIME_TEXT
FROM
CRIME_INTERIM@CRISP C

Although this seems to cause a different issue:

ORA-22992: cannot use LOB locators selected from remote tables

If anyone can help on either issue it would be much appreciated.

Kind regards,

Carl

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #423313 is a reply to message #423311] Wed, 23 September 2009 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You must copy and paste the WHOLE SQL*Plus session and/or code
2/ You should please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.
3/ Always post your Oracle version with 4 decimals.
4/
ORA-22992: cannot use LOB locators selected from remote tables
 *Cause:  A remote LOB column cannot be referenced.
 *Action:  Remove references to LOBs in remote tables.

5/ Search before posting:
http://www.orafaq.com/forum/?SQ=17a55c4b883af2ec7cc531e7afc7a794&t=search&srch=cannot+use+LOB+locators+selected+from+remote+t ables&btn_submit=Search&field=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #423316 is a reply to message #423311] Wed, 23 September 2009 05:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Your lob length is more than 4000 bytes and hence you are getting the error.

XE@SQL> select dbms_lob.getlength(y) from testclob;

DBMS_LOB.GETLENGTH(Y)
---------------------
                32000

XE@SQL> select dbms_lob.substr(y) from testclob;
select dbms_lob.substr(y) from testclob
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

XE@SQL> select dbms_lob.substr(y,4000,1) from testclob;

DBMS_LOB.SUBSTR(Y,4000,1)
--------------------------------------------------------------------------------

********************************************************************************

********************************************************************************

********************************************************************************




Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #423323 is a reply to message #423316] Wed, 23 September 2009 06:42 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
Hope this will help

http://forums.oracle.com/forums/thread.jspa?threadID=469047
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #423335 is a reply to message #423316] Wed, 23 September 2009 08:07 Go to previous messageGo to next message
antenc
Messages: 13
Registered: September 2009
Location: UK
Junior Member
Thanks for your replies.

If the lob is too big to be converted, which i suspected. Is there a workaround:

SELECT
UNIQUE UPPER(C.CRIME_URN) CRIME_NUMBER,
C.CRIME_TEXT CRIME_TEXT
FROM
CRIME_INTERIM C

gives:

ORA-00932: inconsistent datatypes: expected - got CLOB

but using DBMS_LOB.SUBSTR fails due to the field holding more than 4000 bytes.

Any ideas?

Thanks,

Carl
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #423336 is a reply to message #423335] Wed, 23 September 2009 08:12 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot use UNIQUE/DISTINCT with LOB.
Do you think there is CLOB with same value? If so you have a waste of space and should think about changing the model.

Regards
Michel

[Updated on: Wed, 23 September 2009 08:13]

Report message to a moderator

Previous Topic: Determine Explain Plan for a SQL run several hours prior
Next Topic: Strange experince with Oracle 10g !
Goto Forum:
  


Current Time: Fri Dec 09 11:52:50 CST 2016

Total time taken to generate the page: 0.10576 seconds