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

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

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.

This kind of errors occurs when we are trying to convert character into number or trying to assign the value to the parameter larger than the length of parameter.

Yesterday user reported that 'Customized - Sale Invoice Report'  is error out. So I asked user to send me Log file of the report. Then I myself run the report on Test instance and the report was not willing to run :). You may ask why I run the report when user provided me the Log file which is enough to believe that user is right. Well, I run report myself for three reasons:

First reason is, I believe we cannot resolve any problem quickly and efficiently until we get into it. 

Secondly, sometime if we change the report definition in application like parameters, value sets etc. and then try to run the report by copying previously run report it will surely error out because definition of the report is changed and we are copying old report with old definition which is different from the current definition. In my case where I works, whenever user report the error first of all I used to ask user to run report by submitting new request instead of copying old report and if the error still exist then I take up. By my this (I must say) proactive step more than half of the times error vanished.

Lastly, By running report myself I can check the validity of the parameters and I can check the Log file and other helping materials too.

So, run the report and open the 'log file' by clicking the 'View Log...' button.

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

In the log file I noted down the 'short name' of the report and then checked the parameters and the error message. 

"REP-1401: 'cf_proforma_inv_noformula': Fatal PL/SQL error occurred"
"ORA-06502: PL/SQL: numeric or value error"

In Oracle Report Builder 6i error occur like:

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

from the error message I came to know problem is with 'cf_proforma_inv_no' formula column.

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

So I pick the RDF from the server and opened it in the report builder 6i. I find out the formula column and opened its property palette.

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

In property palette I just increased the width of the formula column to 350 from 200. Because I know this error can only occurs when we are trying to convert character into number or trying to assign the larger value to the small length of parameter. So in this case my formula column was character type so the only reason left was its length.

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

 I then double click the button to open formula column to check the length of parameter if assign in code too.

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

I also increased the length of parameters  lv_no1, lv_no2 from 200 to 350.

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

This is enough to avoid this error. But I suggest you to use 'Exception' in your functions so that it never error out. Because if report is showing null is less worse than report error out. So put the exception in your formula columns as illustrated below to avoid this.

All the best. :)

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions