ORA 06502 Numeric or Value Error

By: Dean Richards


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

Description: Many data type and definition problems generate this message, with multiple instances shown. Let’s look at two different examples.

Example 1: A constraint violation when setting a “Not Null” variable to null

declare
w_string varchar2(3) not null :='NOT NULL FIELD';
begin
w_string := '';
end;
/
ÖÖÖÖ
declare
*
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error:
character string buffer too small
ORA-06512: at line 2

 

Example 2: An error when assigning a value larger than the variable definition

declare
w_number number(3);
begin
w_number := 9999;
end;
/
ÖÖ...
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

Potential reasons for this error

An error occurred relating to arithmetic, numeric, string or conversion operations. For example, an attempt is made to assign an integer larger than 999 to a variable declared NUMBER(3), or a variable declared NOT NULL if an a user tries to assign the value NULL.

How to fix this error

Modify the data, how it is declared or the operations on it to eliminate violations.

Leave a Reply