ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06502: PL/SQL: numeric or value error: character to number conversion error occurs when a character value is assigned to a numeric variable in the oracle PL/SQL code. When a non-numeric value is assigned to a numeric datatype variable, the character cannot be converted to a number. A numeric data type variable cannot be assigned a character value. If you try to assign character to number, the conversion error ORA-06502: PL/SQL: numeric or value error: character to number conversion error will be thrown.

The number conversion error happens when the character value is assigned to a number variable. It is not possible to convert the non-numeric character value to a numeric number. The data type of the variable should be changed to character or a numeric value should be assigned. The assigned value and the declared datatype must be same to store the value. Otherwise, the character to number conversion error ORA-06502: PL/SQL: numeric or value error: character to number conversion error would be shown.



Exception

If you run the above code in Oracle, you will get the stack trace error shown below. The numeric data type is attempted to be assigned to the character value.

declare
    empid numeric(4);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"


Problem

A character employee id is assigned to variable empid in the example below. The empid’s data type is a number with a size of four. The oracle error is thrown if the character value is assigned to the number data type variable.

declare
    empid numeric(4);
begin
    empid := 'A101';
end;

Output

declare
    empid numeric(4);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
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 data type of the variable should be changed to character data type. The character data type will store both the alphabet and the value of a number. The character data type is shown in the example below.

declare
    empid varchar(4);
begin
    empid := 'A101';
end;

Output

PL/SQL procedure successfully completed.


Solution 2

A number should be used as the value. Make sure the origin of the value. This variable receives the value incorrectly. If the code bug exists and is fixed, the value assigned to the variable would be a number value. The numeric value will be stored in the number data type variable.

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

Output

PL/SQL procedure successfully completed.


Solution 3

Handling the exception from PL/SQL code is another method for dealing with this error. If an error occurs, handle it and take a different action for the value.

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

Output

PL/SQL procedure successfully completed.



Leave a Reply