ORA-06502

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

What causes this error?

An ORA-06502 error occurs when an arithmetic, numeric, string, conversion, or constraint error occurred in a PL/SQL block. This error mainly results from programmer error or invalid data input.

An example:

SQL> DECLARE
  2    i NUMBER;
  3  BEGIN
  4    i := ' ';
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

Other examples:

• An attempt is made to assign the value NULL to a variable declared NOT NULL
• An attempt is made to assign an integer larger than 999 to a variable declared NUMBER(3)
• An attempt is made to assign more than 5 characters to a VARCHAR2(5) variable

How to fix it

Change the data, how it is manipulated, or how it is declared so that values do not violate the declared data type definitions.

You can also capture these errors with the predefined VALUE_ERROR exception. Example:

DECLARE
  i NUMBER;
BEGIN
  i := ' ';
EXCEPTION
  when VALUE_ERROR then
    dbms_output.put_line('VALUE_ERROR exception raised');
END;
/