Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0
icon4.gif  ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240187] Thu, 24 May 2007 06:01 Go to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Hi .

I am getting this error for my PLSQL
"ORA-06502:PL/SQL:numeric or value error: character string buffer too small" in ORACLE 10.2.0 installed on Linux.

My plsql procedure has this segment which fails with the above error.The same plsql procedure works fine in Oracle 9i.

DECLARE
l_bid_direction VARCHAR2(1) ;
BEGIN
SELECT MAX(bidparam.bid_direction)
INTO l_bid_direction
FROM auction_bid_parameters bidparam
JOIN auction_bid_component bidcomp
ON bidcomp.bid_parameter_id = bidparam.bid_parameter_id
AND bidcomp.item_id between 1 and 100 ;
END;

But when I run the select query seperately it works fine.
The same plsql procedure works fine in Oracle 9i too.

I am getting this issue when I migrated my DB from Oracle 9i to Oracle 10.2.0
Is there any change in Oracle 10.2.0

Please let me know

Thanks in advance
Binu
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240193 is a reply to message #240187] Thu, 24 May 2007 06:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
change l_bid_direction to a varchar2(2000) to see what gets fetched into it..
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240194 is a reply to message #240193] Thu, 24 May 2007 06:12 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Hi Frank

Thanks for your prompt reply.

I have tried the following

1) increased the _bid_direction VARCHAR2(1) size to even 100,200
and 2000 ,it didnt work :-(

2) The same plsql used to work fine in Oracle 9 i with no issues.

3) Even in Oracle 10g , the select query returns correct value.

4) In DB all the values of bidparam.bid_direction are 'D' . That is only one character.


Any thoughts please

Binu
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240195 is a reply to message #240187] Thu, 24 May 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change the type of your variable l_bid_direction accordingly to the type of what you're searching for.

The correct declaration is:
l_bid_direction auction_bid_parameters.bid_direction%type;

Regards
Michel
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240197 is a reply to message #240195] Thu, 24 May 2007 06:16 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Frank,

I had tried this too earlier, but no luck it throws the same error 06502 ...

Thanks
Binu
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240198 is a reply to message #240194] Thu, 24 May 2007 06:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
binuraj wrote on Thu, 24 May 2007 13:12
Hi Frank

Thanks for your prompt reply.

I have tried the following

1) increased the _bid_direction VARCHAR2(1) size to even 100,200
and 2000 ,it didnt work Sad


Define 'it didnt work'. Still the same error?
Please show a copy/paste from sqlplus
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240199 is a reply to message #240198] Thu, 24 May 2007 06:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's the datatype of:
bidcomp.bid_parameter_id
bidparam.bid_parameter_id
bidcomp.item_id
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240201 is a reply to message #240199] Thu, 24 May 2007 06:23 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Frank,

The datatype of:
bidcomp.bid_parameter_id is NUMBER(20,0)Nullable
bidparam.bid_parameter_id is NUMBER(20,0)Not Nullable
bidcomp.item_id is NUMBER(20,0)Not Nullable
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240204 is a reply to message #240198] Thu, 24 May 2007 06:26 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Yes the same ERROR

DECLARE
l_bid_direction VARCHAR2(1);
BEGIN
SELECT MAX(bidparam.bid_direction)
INTO l_bid_direction
FROM auction_bid_parameters bidparam
JOIN auction_bid_component bidcomp
ON bidcomp.bid_parameter_id = bidparam.bid_parameter_id
AND bidcomp.item_id between 1 and 10 ;
END;
Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240206 is a reply to message #240204] Thu, 24 May 2007 06:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Any triggers on the tables?
Auditing?
Invalid indexes?
These are both tables? Not views with functions in them?

[Updated on: Thu, 24 May 2007 06:28]

Report message to a moderator

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240211 is a reply to message #240206] Thu, 24 May 2007 06:33 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
yes it had indexes no triggers or auditing.
But will it matter?
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240235 is a reply to message #240206] Thu, 24 May 2007 07:45 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Frank,

it started working after I changed l_bid_direction VARCHAR2(4000) ; ..

DECLARE
l_bid_direction VARCHAR2(4000) ;
BEGIN
SELECT MAX(bidparam.bid_direction)
INTO l_bid_direction
FROM auction_bid_parameters bidparam
JOIN auction_bid_component bidcomp
ON bidcomp.bid_parameter_id = bidparam.bid_parameter_id
AND bidcomp.item_id between 1 and 10;
END;


But I am stil concerned why I should change the size to 4000 when bidparam.bid_direction is single character element.


Any idea?

[Updated on: Thu, 24 May 2007 07:46]

Report message to a moderator

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240241 is a reply to message #240235] Thu, 24 May 2007 07:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What datatype is the database-column?
What does the variable contain? What value is fetched?

[Updated on: Thu, 24 May 2007 07:51]

Report message to a moderator

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240244 is a reply to message #240241] Thu, 24 May 2007 07:53 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
"BID_DIRECTION" CHAR(1 BYTE) NOT NULL

it is of type char

and DB contains 'D' for all entries

[Updated on: Thu, 24 May 2007 07:53]

Report message to a moderator

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240245 is a reply to message #240244] Thu, 24 May 2007 07:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, I'm out of ideas..
Thought it might have something to do with CHAR-columns, but whatever NLS setting you use, a CHAR(1 BYTE) should in my world always fit in a varchar2(2000)

[Edit: 1 sorry should do Wink]

[Updated on: Thu, 24 May 2007 07:58]

Report message to a moderator

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240246 is a reply to message #240245] Thu, 24 May 2007 07:58 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Thanks a lot Frank
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240255 is a reply to message #240204] Thu, 24 May 2007 08:26 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Here's some things to try.

1. Change your code to this (traditional join):
SELECT MAX(bidparam.bid_direction)
INTO l_bid_direction
FROM auction_bid_parameters bidparam, auction_bid_component bidcomp
where bidcomp.bid_parameter_id = bidparam.bid_parameter_id
AND bidcomp.item_id between 1 and 10 ;


2. Do a DESCribe of both of those tables. You don't have a column the same name as the variable, do you?

3. I know it sounds silly, but change the name of your variable.

Try these things independently, not all at once.
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240548 is a reply to message #240255] Fri, 25 May 2007 03:31 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Thanx Joy.

I tried the ways u told,but same error

and THE ERROR COMES IF i DO a simple select and not a join.
ORA 06502 comes even for this simple qeary accessed through procedure.

DECLARE
l_bid_direction VARCHAR2(1) ;
BEGIN
SELECT MAX(bidparam.bid_direction) INTO l_bid_direction
FROM auction_bid_parameters bidparam;
END;


Again , the select query if I execute separately it works fine.

Any thoughs?

Binu
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240563 is a reply to message #240548] Fri, 25 May 2007 03:55 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Hi ,

I tried trim and it works fine...
the new plsql looks like this

DECLARE
l_bid_direction VARCHAR2(1) ;
BEGIN
SELECT MAX(trim(bidparam.bid_direction)) INTO
l_bid_direction FROM auction_bid_parameters bidparam;
END;


But still dont know y I need to do this as the query

"SELECT MAX(LENGTHB(bidparam.bid_direction)) FROM auction_bid_parameters bidparam" always returns 1. So in DB the values are right.

Probably the entries for bid_direction when it is read from DB is coming with spaces which causes 06502

as of now, I am going with this solution.

any hints still invited....

Binu
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240568 is a reply to message #240563] Fri, 25 May 2007 04:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What if you declare l_bid_direction as varchar2(1 byte)?
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240570 is a reply to message #240568] Fri, 25 May 2007 04:04 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
Frank
changing to varchar2(1 byte)? gives the same ORA 06502 error
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240578 is a reply to message #240570] Fri, 25 May 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to declare it with %TYPE as I first posted?

Regards
Michel
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240584 is a reply to message #240568] Fri, 25 May 2007 04:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it's a multi-byte character set, wouldn't you need to declare the field as VARCHAR2(1 CHAR)?
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #240585 is a reply to message #240584] Fri, 25 May 2007 04:41 Go to previous messageGo to next message
binuraj
Messages: 14
Registered: May 2007
Junior Member
it is not meant to be multi-byte. All DB values are single character . The value is 'D'.
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #266808 is a reply to message #240187] Tue, 11 September 2007 12:39 Go to previous messageGo to next message
Marcelo_laus
Messages: 2
Registered: September 2007
Location: Brasil
Junior Member

Hi binuraj,

You it obtained to decide this problem.
I am with a similar problem and I am not obtaining to also decide and wanted to know if you have some reply.


Marcelo Lauschner
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #266838 is a reply to message #266808] Tue, 11 September 2007 14:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try this:

SELECT MAX(bidparam.bid_direction), dump(MAX(bidparam.bid_direction), 1016)
FROM auction_bid_parameters bidparam;


the dumped output should show a single byte and the characterset will show what encoding the value is stored in (will be the same as your database character set.

You can't accurately rely on the 'D' displayed in your output. Client fonts and client NLS settings can effect the display.

[Updated on: Tue, 11 September 2007 14:27]

Report message to a moderator

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #266844 is a reply to message #240187] Tue, 11 September 2007 14:54 Go to previous messageGo to next message
Marcelo_laus
Messages: 2
Registered: September 2007
Location: Brasil
Junior Member

which parameters nls can intervene with the result output?

Marcelo Lauschner
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #266863 is a reply to message #266844] Tue, 11 September 2007 18:41 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
when the client NLS characterset doesn't match (is not equivalent to?) the characterset used to store the data(database characterset for char, varchar2, clob etc; NCHAR characterset for nchar, nvarchar2, nclob etc), then characterset conversion takes place between the database and the client.

If your pl/sql read the data across a DB link, characterset conversion could be a factor and it could potentially cause a single byte to be converted into a multi-byte character which would blow out the 1 byte variable.

By the time you actually display the data, you will have gone through some client and so characterset conversion could be a factor.
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #266864 is a reply to message #266863] Tue, 11 September 2007 19:10 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
NLS parameters could be at the database or session level - query the right view to get the right values:
select * from v$nls_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     AL32UTF8

Corresponding NLS_LANG is determined as follows:
NLS_LANG=<NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>
i.e.
NLS_LANG=AMERICAN_AMERICA.AL32UTF8


This doesn't imply that the client NLS_LANG must be set to match exaclty. It tells Oracle what characterset your client sends/receives data in and what format your decimal point, month/day abbreviations are in etc.

This shows a DOS SQLplus on Windows in the US displaying Euro sign:

--==========================================================================
Euro is CHR(14844588) (U+20AC)
--==========================================================================
1.) Set NLS_LANG in registry for current Oracle Home to something having a Euro
 e.g. AMERICAN_AMERICA.WE8MSWIN1252

-- trick to show contents of Oracle environment variables
SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"

--==========================================================================
-- Default DOS codepage 437 (Euro sign display test fails)
--==========================================================================
C:\>chcp
Active code page: 437

C:\>sqlplus scott/tiger@utf8dev

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 7 14:27:37 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

SQL> select CHR(14844588), dump(CHR(14844588)) from dual;

CHR DUMP(CHR(14844588))
--- ------------------------
   Typ=1 Len=3: 226,130,172   <<=== Incorrect display (default codepage 437)


--==========================================================================
-- Windows codepage 1252 (Euro sign display test works)
--==========================================================================
2.) C:\>chcp 1252
Active code page: 1252

3.) Set font in DOS window to Lucida Console (it contains Euro)

4.) C:\>sqlplus scott/tiger@utf8dev

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 7 14:28:21 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

2.) SQL> select CHR(14844588), dump(CHR(14844588)) from dual;

CHR DUMP(CHR(14844588))
--- ------------------------
   Typ=1 Len=3: 226,130,172   <<=== Correct display (codepage 1252)




Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #267906 is a reply to message #240585] Sun, 16 September 2007 06:13 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Binuraj,
What is the datatype of the column bidparam.bid_direction?
Is it CHAR (or) VARCHAR2?
What is the 4 digits db version?
I remember I have seen some bugs in 10g, with CHAR datatype, when we use min / max.

Have a try on metalink for them.

By
Vamsi
Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #267909 is a reply to message #267906] Sun, 16 September 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Binuraj is no more there.
He did not access the forum since 25 May.
Like many here when they got their answer they no more talk not even to say they got the answer or thank those who helped him.
It is useless to ask him anything.

Regards
Michel

Re: ORA-06502:PL/SQL:numeric or value error: character string buffer too small in Oracle 10.2.0 [message #267965 is a reply to message #267909] Mon, 17 September 2007 00:07 Go to previous message
binuraj
Messages: 14
Registered: May 2007
Junior Member
HI Michel AND OTHERS

First of all thanks for your replies.

I got rid of the problem by trimming,
trim(bidparam.bid_direction).

This I had mentioned in my last thread as you mentioned.

After that I didnt get any mails from the forum except the last two mails(May be it is going to my spam folder or some other issue). Unless other wise I get mails on this thread there is no way that I can respond.

anyway thanks Michel for your helpful replies

Binu
Previous Topic: Insert 1 million records using SQLPLUS
Next Topic: Help in writing function.. regarding parameters of function
Goto Forum:
  


Current Time: Wed Dec 07 08:35:07 CST 2016

Total time taken to generate the page: 0.10170 seconds