05 December 2011

Possible Causes of “ORA-06502: PL/SQL: numeric or value error”


Sometimes people ask me that what the reason of ORA-06502 is. Mostly, this is because of carelessness of developersJ. Official description of ORA-06502 is

[oracle@dhcppc5 ~]$ oerr ora 6502
06502, 00000, "PL/SQL: numeric or value error%s"
// *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.
[oracle@dhcppc5 ~]$


This exception also exists in the STANDART package of Oracle database:
  VALUE_ERROR exception;
    pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');


I show possible reasons of this error below:
DECLARE
  i  NUMBER;
  i2 NUMBER NOT NULL := 0;-- !is not a best practice...
  i3 NUMBER(1);
  i4 POSITIVE;
  s  VARCHAR2(3) DEFAULT 'aaa';-- !is not a best practice...
  s2 VARCHAR2(1);
BEGIN
  -- assign to a number data type variable a non-number value
  i := s; -- raises ORA-06502: PL/SQL: numeric or value error: character to number conversion error

  -- assign more values (in terms of length) to a variable with less size
  s2 := s; -- raises ORA-06502: PL/SQL: numeric or value error: character string buffer too small

  -- assign a not-null-constrained value to null value
  i2 := i; -- raises ORA-06502: PL/SQL: numeric or value error

  -- assign bigger numeric value to a smaller size numeric variable
  i3 := 10; -- raises  ORA-06502: PL/SQL: numeric or value error: number precision too large

  -- assign negative value to positive data type
  i4 := -1; -- raises  ORA-06502: PL/SQL: numeric or value error


END;

No comments: