- 11 Nov, 2021
- 0 Comments
- 1 Min Read
ORA-06502: PL/SQL: numeric or value errorstring
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