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

ORA-06502: PL/SQL: numeric or value error error occurs when the not null variable is assigned a null value or when the assigned numeric value is greater than the allowed numeric datatype value or when a non-numeric value is assigned to a numeric variable. The numeric not null variable should be assigned a value that is within the maximum specified limit. If a non-numeric character, such as a number, should not be assigned to a numeric datatype. Otherwise, the error ORA-06502: PL/SQL: numeric or value error will be thrown

If a numeric variable is declared as not null and a null value is assigned to it, the null value cannot be assigned to a not null variable. The error message ORA-06502: PL/SQL: numeric or value error will be shown. In another case, if the value of a numeric variable is greater than the maximum specified numeric value, the value cannot be stored in the variable. The error message ORA-06502: PL/SQL: numeric or value error will be shown. If a non-numeric variable is assigned a numeric datatype, the non-numeric variable can not be assigned. The error ORA-06502: PL/SQL: numeric or value error will be thrown.



Problem

In the example below, there are two numeric variables empid and managerid. The empid is declared as the numeric not null variable. It is assigned with 1 as default. If the empid is assigned with managerid that contains null, the empid can not store a null value. The error ORA-06502: PL/SQL: numeric or value error will be thrown.

declare
    empid numeric(4) not null := 1;
    managerid numeric(4);
begin
    empid := managerid;
end;


Oracle Error

The below oracle error is thrown if a numeric not null variable is assigned with a null value. The following error will be shown when you run the PL/SQL code in oracle.

declare
    empid numeric(4) not null := 1;
    managerid numeric(4);
begin
    empid := managerid;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
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.



Solution 1

The variable assigned with a null value should be changed to assign with a value. If a value is assigned with a not null numeric variable, the error will be resolved.

declare
    empid numeric(4) not null := 1;
    managerid numeric(4);
begin
    managerid :=2;
    empid := managerid;
end;

Output

PL/SQL procedure successfully completed.


Solution 2

The null value can not be assigned to a not null numeric variable. If the null value is allowed to store in the variable, the variable should be declared as numeric null data type. This will resolved the null value assignment in the variable.

declare
    empid numeric(4);
    managerid numeric(4);
begin
    empid := managerid;
end;

Output

PL/SQL procedure successfully completed.


Solution 3

If the value could not be predicted and very unlikely the null value is inserted, then you can not change the data type or validate the value. The easiest approach is to handle the error.

declare
    empid numeric(4) not null := 1;
    managerid numeric(4);
begin
    empid := managerid;
exception
    WHEN OTHERS THEN
        empid :=0;
end;

Output

PL/SQL procedure successfully completed.



Leave a Reply