SOLVED
ORA-06502: PL/SQL: numeric or value errorstring
Asked by OracleDba••10 views•oracle
#oracle#error
Solutions(1)
Accepted Solution
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111
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 5OracleDba•