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

The ORA-06502: PL/SQL: numeric or value error occurs when the not null variable is assigned with a null value. The numeric not null variable should assign with a number. If the variable is assigned with a variable that contains a null value, 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.



Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *