ORA-06502: PL/SQL: numeric or value errorstring

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.

Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e4100.htm#sthref1889

ORA-06502 exception occurs when arithmetic, numeric, string, conversion, or constraint error occurred. In my views, ORA-06502 normally occurs because of programming bugs and programmer ignorance.

ORA-06502 exception raises by the Oracle Database when:
- We try to assign a larger value a variable can hold
- We try to assign a string to a number type variable
- We try to assign NULL to variable declared as NOT NULL

Examples:

SQL> declare
  2     n number(2);
  3  begin
  4     n := 123;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

SQL> declare
  2     n number(2);
  3  begin
  4     n := 'test';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

SQL> declare
  2     str varchar2(3);
  3  begin
  4     str := 'test';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL> declare
  2     n1 number not null := 1;
  3     n2 number;
  4  begin
  5     n1 := n2;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5

3 comments:

AnonymousAugust 21, 2013 at 12:16 AM
hi..please resolve my query..

i AM GETTING BELOW ERROR IN MY PROGRAM

wheels

declare
*

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.ROSTER_EMPLOYEE", line 12
ORA-06512: at line 4

MY PROGRAM CODE IS:

set serveroutput on
CREATE OR REPLACE PROCEDURE Roster_Employee(Reply in out VARCHAR2) is

individual varchar2(1000);

keyword1 varchar2(200);

empno varchar2(200);

day varchar2(200);

shifttime varchar2(200);

bldno varchar2(200);

Counter NUMBER(2) :=0;

Received_Time varchar2(200);

BEGIN

if(Reply like 'wheels%') then
WHILE (LENGTH(Reply) > 0) loop
IF (instr(Reply,' ', 1, 1) > 0) then

individual := SUBSTR(Reply, 0, instr(Reply,' ', 1, 1));

if(Counter=0) then

keyword1:=individual;
dbms_output.put_line(keyword1);

end if;

if(Counter=1) then

empno:=individual;
dbms_output.put_line(empno);

end if;

if(Counter=2) then

day:=individual;

dbms_output.put_line(day);

end if;

if(Counter=3) then

shifttime:=individual;

dbms_output.put_line(shifttime);

end if;

if(Counter=4) then

bldno:=individual;
dbms_output.put_line(bldno);

end if;

Reply := SUBSTR(Reply, LENGTH((individual + ' ')||'ab'), LENGTH(reply));

Counter:=Counter+1;

ELSE

BEGIN

individual := Reply;

dbms_output.put_line(individual);

Received_Time:=individual;

dbms_output.put_line(Received_Time);

END;

end if;

END loop;

insert into test values( keyword1, empno,day,shifttime, bldno);

end if;

END;

I AM RUNNING PROCEDURE LIKE BELOW:

declare
val varchar2(1000):='wheels my first roster test received';
begin
roster_employee(val);
end;