How to resolve ORA-06502 Error Message in Oracle database

Learn the reason and how to unravel the ORA-06502 error message in Oracle.

Description

When you stumble upon an ORA-06502 error, the following error message will appear:

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

Cause

You tried to execute a assertion that resulted in an arithmetic, numeric, string, conversion, or constraint error.

The common reasons for this error are:

You tried to assign a fee to a numeric variable, however the fee is larger than the variable can handle. You tried to assign a non-numeric value to a numeric variable and induced a conversion error.

Resolution

Let’s seem at three options on how to unravel the ORA-06502 error:

Option #1 – Value too large

In our first option, this error happens when you try to assign a cost to a numeric variable, however the value is larger than the variable can handle.

For example, if you created a procedure known as TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    v_number number(2);
  4  BEGIN
  5    v_number := 100;
  6  END;
  7  /

Procedure created.

This manner was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:

SQL> execute TestProc();
BEGIN TestProc(); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "EXAMPLE.TESTPROC", line 5
ORA-06512: at line 1

The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) shows that the error befell at line 5 of the PLSQL code.

In this example, you have tried to assign a 3 digit quantity to a variable known as v_number that can solely take care of two digits. You ought to correct this error with the aid of redefining the v_number variable as number(3).

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    v_number number(3);
  4  BEGIN
  5    v_number := 100;
  6  END;
  7  /

Procedure created.

And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.

SQL> execute TestProc();

PL/SQL procedure successfully completed.

Option #2 – Conversion error

In our 2nd option, this error takes place if you are trying to assign a non-numeric price to a numeric variable.

For example, if you created a method referred to as TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    v_number number(2);
  4  BEGIN
  5    v_number := 'a';
  6  END;
  7  /

Procedure created.

This technique was efficiently created. But when we attempt to execute this procedure, we will get an ORA-06502 error as follows:

SQL> execute TestProc();
BEGIN TestProc(); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "EXAMPLE.TESTPROC", line 5
ORA-06512: at line 1

In this example, the cost of ‘a’ does now not good convert to a numeric value. You can right this error by means of assigning the variable referred to as v_number a applicable numeric value.

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    v_number number(2);
  4  BEGIN
  5    v_number := ASCII('a');
  6  END;
  7  /

Procedure created.

And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.

SQL> execute TestProc();

PL/SQL procedure successfully completed.

Option #3 – Assigning NULL to a NOT NULL constrained variable

In our third option, this error happens if you are attempting to assign a NULL cost to a NOT NULL limited variable.

For example, if you created a procedure referred to as TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS   
  3    v_non_nullable_variable VARCHAR2(30) NOT NULL := '5';
  4    v_null_variable         VARCHAR2(30)          := NULL;
  5  BEGIN
  6    v_non_nullable_variable := v_null_variable;
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      dbms_output.put_line(SQLERRM);
  10 END;
  11 /

Procedure created.

This method was efficaciously created. But when we attempt to execute this procedure, we will get an ORA-06502 error as follows:

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

In this example, you can not assign a NULL price to the variable called v_non_nullable_variable. You can right this error doing away with NOT NULL from the variable assertion of the v_non_nullable_variable as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS   
  3    v_non_nullable_variable VARCHAR2(30) := '5';
  4    v_null_variable         VARCHAR2(30) := NULL;
  5  BEGIN
  6    v_non_nullable_variable := v_null_variable;
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      dbms_output.put_line(SQLERRM);
  10 END;
  11 /

Procedure created.