Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314306] Wed, 16 April 2008 05:49 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
while running this code
PROCEDURE GET_MEMO_TYPE IS
CURSOR c1_dup_memo_alerts is
    select * from adm.dup_memo_alerts
    where  nvl(sent_flag,'N') = 'N'
    order by MEMO_TYPE asc;
And the code goes on..

I am getting this error
4:12:14 PM  Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
4:12:14 PM  ORA-06512: at line 23


This is the desc for adm.dup_memo_alerts
*** SCRIPT START :  Session:#(1)   4/16/2008 4:14:29 PM *** 
Processing ...
desc adm.dup_memo_alerts
Describing adm.dup_memo_alerts....
NAME                            Null?     Type
------------------------------- --------- -----
MEMO_ALERT_ID                   NOT NULL  NUMBER(8,0)
MEMO_TYPE                       NOT NULL  NUMBER(1,0)
ACTIVITY                                  VARCHAR2(12)
PROJECT                         NOT NULL  VARCHAR2(16)
VERSION                                   VARCHAR2(2)
RECIPIENT                                 VARCHAR2(2000)
COPY_TO                                   VARCHAR2(2000)
DATE_REQUESTED                            DATE
SENT_FLAG                                 VARCHAR2(1)
DATE_SENT                                 DATE
*** SCRIPT END :  Session:#(1)   4/16/2008 4:14:29 PM *** 



[Edit MC: sensitive information replaced by # at OP's request]

[Updated on: Thu, 08 November 2012 02:12] by Moderator

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314311 is a reply to message #314306] Wed, 16 April 2008 05:52 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Sorry, line 23 points to this line

CURSOR c1_dup_memo_alerts is
    select * from adm.dup_memo_alerts -- LINE 23
    where  nvl(sent_flag,'N') = 'N'
    order by MEMO_TYPE asc;
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314321 is a reply to message #314311] Wed, 16 April 2008 06:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
I don't think that line causes the error.
test case :
Error starting at line 1 in command:
declare
l_name varchar2(2); 
begin
select ename into l_name from emp where empno=7788;
dbms_output.put_line(l_name);
end;
Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"


can you post your code.

regards,
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314324 is a reply to message #314321] Wed, 16 April 2008 06:16 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
code removed at OP's request




[Edit MC: code removed at OP's request]

[Updated on: Thu, 08 November 2012 03:19] by Moderator

Report message to a moderator

icon8.gif  Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314333 is a reply to message #314306] Wed, 16 April 2008 06:42 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
hey guys, can anyone please look into the above issue Sad
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314335 is a reply to message #314306] Wed, 16 April 2008 06:47 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hey guy, can you show a little patience? This isn't a pay service.

I encounter this error most often when I try and assign a string to a plsql variable that is not big enough to hold the string. Check your string assignments.

Quote:

ORA-06502 PL/SQL: numeric or value errorstring

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred.
For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL,
or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated,
or how it is declared so that values do not violate constraints.



[Updated on: Wed, 16 April 2008 06:48]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314337 is a reply to message #314306] Wed, 16 April 2008 06:48 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
ok cool, i shall check my string assignments, thanks Smile
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #314422 is a reply to message #314337] Wed, 16 April 2008 11:47 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
BTW,

You don't need to say...

to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'));


You can just use sysdate. There's no need to convert sysdate from a DATE type to a STRING type and then back to a DATE type.
Previous Topic: Select query within trigger
Next Topic: commit in side stored procedure
Goto Forum:
  


Current Time: Wed Dec 07 06:52:23 CST 2016

Total time taken to generate the page: 0.10471 seconds