tech on the net

Oracle/PLSQL: ORA-06502

Learn the cause and how to resolve the ORA-06502 error message in Oracle.

Description

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

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

Cause

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

The common reasons for this error are:

  1. You tried to assign a value to a numeric variable, but the value is larger than the variable can handle.
  2. You tried to assign a non-numeric value to a numeric variable and caused a conversion error.

Resolution

Let's look at two options on how to resolve the ORA-06502 error:

Option #1 - Value too large

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

For example, if you created a procedure called 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 procedure 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) indicates that the error occurred at line 5 of the PLSQL code.

In this example, you've tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by 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 second option, this error occurs if you are trying to assign a non-numeric value to a numeric variable.

For example, if you created a procedure called 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 procedure 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: character to number conversion error
ORA-06512: at "EXAMPLE.TESTPROC", line 5
ORA-06512: at line 1

In this example, the value of 'a' does not properly convert to a numeric value. You can correct this error by assigning the variable called v_number a proper 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.