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

Today one user write to me and asking the solution about the below DEVELOPER 6i report error. He is using formula column to calculate monthly net balance on report 6i.

Cause: An arithmetic, numeric, string conversion or constraint error occurred when 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.

This is very common error occurs when you are trying to assign a value to a numeric variable, but the value is the larger than the variable it can handle or you have defined the numeric value and trying to pass the non-numeric value.

From the error it is clear the problem is with cf_calc formula column. When i checked his PL/SQL section of cf_calc then found he has defined column number (6, 2) where as the column width of formula column is number 10 the resultant value is not fit in the defined column length. You have to increase the length of  xdbsum, xcrsum, temp to number (12, 2).

xdbsum  number(6, 2) := 0 ;

xcrsum  number(6, 2) := 0 ;

TEMP    number(6, 2) := 0;

Checklist:

·        Check the size and type of value whether it is having any variable small length and you are trying to return some big value. For example you have a field of length 10 and trying to return the value is more than 10.

·        Check the variable if you assign non-numeric value to a numeric variable or filed in the report with integer value.