ORA-06502: PL/SQL: numeric or value error string
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.
More Information by Anantha:
This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.
Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.
For example:
declare
test varchar2(1);
begin
test := 'I am here';
end;
The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4
Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.
Now consider the following example:
declare
test number(2);
begin
test := 100;
end;
The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
So now you know more about this frequently occuring error.
Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;
The above declaration methodology is a very efficient one in handling ORA-06502 error.
2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;
3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.
