ORA-06502: PL/SQL: numeric or value error: number precision too large

ORA-06502: PL/SQL: numeric or value error: number precision too large error occurs when the numeric value exceeds the declared numeric data type size. The number of digits in the value exceeds the number of digits in the declared numeric data type. The numeric value exceeds the number precision specified in the numeric datatype. If you insert a large numeric value into a number variable with a smaller precision size, Oracle will throw the error ORA-06502: PL/SQL: numeric or value error: number precision too large.

ORA-06502 error occurs when numeric values are used. The number of digits in the value must be less than or equal to the number of digits in the declared numeric data type. The value is stored in the declared variable in this case. The value cannot be stored by the variable if the number of digits in the value is greater. To store the larger value, the declared variable data type’s size should be increased. Otherwise, the error ORA-06502: PL/SQL: numeric or value error: number precision too large will be thrown.



Exception

The error will be thrown if the number of digits in the value is greater than the declared numeric data type size. The database error will be displayed below the stack trace. The error ORA-06502: PL/SQL: numeric or value error: number precision too large will be displayed with the line number.

Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:


Cause

The number of digits in the value exceeds the declared numeric data type’s number of digits. A value greater than the size of the declared data type cannot be stored in the declared variable. The error ORA-06502: PL/SQL: numeric or value error: number precision too large will be thrown if you try to assign a larger value. The value’s number of digits should be less than or equal to the declared number of digits.



Problem

The value could not be assigned to the variable if it had a number of digits greater than the declared numeric data type’s number of digits. The error ORA-06502: PL/SQL: Numeric or Value Error: Number Precision Too Large will be thrown by the oracle. In the example below, the number of digits in the value is greater than the declared numeric data type’s number of digits.

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

Output

Error starting at line : 1 in command -
declare
    empid numeric(4);
begin
    empid := 10001;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
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 value assigned to the variable cannot be stored in the variable if it is larger than the size of the data type declared. The declared datatype size should be increased to accommodate the large value. If the value exceeds the declared data type size’s maximum size, you should consider changing the data type that holds the larger value.

The declared numeric data type size in the example below is 4. It can hold values ranging from 0 to 9999. The assigned value exceeds the permitted value range. To accommodate the given value in the variable, the declared numeric data type size is increased to 5. The error ORA-06502: PL/SQL: numeric or value error: number precision too large is resolved.

declare
    empid numeric(5);
begin
    empid := 10001;
end;

Output

PL/SQL procedure successfully completed.


Solution 2

Analyze the value if the number of digits in the value is greater. The value should fall within the declared data type’s size range. If the database value is wrongly assigned to the declared variable. Analyze the variables and the value’s origin. The value will be assigned to the variable if you correct a value with fewer digits.

In the example below, the number of digits in the value is 5 which is greater than the declared data type size 4. The value (201) that has been assigned to it has been changed. The size of the value is less than 4 digits. The error ORA-06502: PL/SQL: numeric or value error: number precision too large is resolved.

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

Output

PL/SQL procedure successfully completed.


Solution 3

The value cannot be assigned if the declared data type precision value is incorrectly declared. Because the size of the datatype declared differs from the value, Oracle will throw an error. In this case, the variable’s declared data type must be changed to match the value.

declare
    empid numeric(4,2);
begin
    empid := 1001;
end;

Error

Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Solution

declare
    empid numeric(6,2);
begin
    empid := 1001;
end;

Output

PL/SQL procedure successfully completed.


Solution 4

Handling the exception is another option for dealing with this error. If an error occurs, it is handled using exception handling and an alternative action is taken using PL/SQL code.

declare
    empid numeric(4);
begin
    empid := 10001;
exception
    WHEN OTHERS THEN
        empid :=0;
end;

Output

PL/SQL procedure successfully completed.



Leave a Reply