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


Related Posts:
- ORA-06502: invalid LOB locator specified
- ORA-01460 unimplemented or unreasonable conversion requested
- ORA-01723: zero-length columns are not allowed
- ORA-01489: result of string concatenation is too long
- ORA-01722: invalid number
- ORA-00911: invalid character
- ORA-00904: invalid identifier

3 comments:

  1. 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;




    ReplyDelete
    Replies
    1. change
      Reply := SUBSTR(Reply, LENGTH((individual + ' ')||'ab'), LENGTH(reply));
      to
      Reply := SUBSTR(Reply, LENGTH((individual || ' ')||'ab'), LENGTH(reply));

      Delete
    2. thanks so much :) ..it was really a silly mistake..

      Delete