Blog

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

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.

ORA-06502 exception occurs when arithmetic, numeric, string, conversion, or constraint error occurred. In my views, ORA-06502 normally occurs because of programming bugs and programmer ignorance.

ORA-06502 exception raises by the Oracle Database when:
- We try to assign a larger value a variable can hold
- We try to assign a string to a number type variable
- We try to assign NULL to variable declared as NOT NULL

Examples:

SQL> declare
  2     n number(2);
  3  begin
  4     n := 123;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
SQL> declare
  2     n number(2);
  3  begin
  4     n := 'test';
  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
SQL> declare
  2     str varchar2(3);
  3  begin
  4     str := 'test';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
SQL> declare
  2     n1 number not null := 1;
  3     n2 number;
  4  begin
  5     n1 := n2;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.