- 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:
1 2 3 4 5 6 7 8 9 10 11 | 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 largeORA-06512: at line 4 |
1 2 3 4 5 6 7 8 9 10 11 | 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 errorORA-06512: at line 4 |
1 2 3 4 5 6 7 8 9 10 11 | 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 smallORA-06512: at line 4 |
1 2 3 4 5 6 7 8 9 10 11 12 | 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 errorORA-06512: at line 5 |