How to DEBUG ORA-06502 Numeric or value error.

If you have 3000 lines of code and a custom exception handler like when others. The ORA 06502 can be  notoriously elusive to find.

This error is caused by .

• Trying to assign a higher length/precision value to variable whose size / precision is smaller than the value being assigned.
• Or trying to assign a non numeric value to a numeric variable.

Ex .

This is easy to see in 5 line code but if you have 3000+ lines of code and you did not handle the exception correctly. (plenty of times we write a generic when others handler which does not give us the line number but only a petty error message which says Numeric or valuer error.) 

This would be the case wherein you would need to debug or trace where the error is occurring.

Here's how you can debug such failure in this case.

1. If you are on your development or test enviornment , strip off all your excption handler in your package , procedure , function whatever it is that's throwing this error. When you run the code next time the line number of code throwing this error will be revealed.

2. If you are on production environment , if possible create a plsql block of the procedure / function throwing this error , again strip off all exception handlers. when you run this plsql block line number of code throwing this error will be reveled. 

3. If option 2 is not feasible due to huge package or package delarations with types or due to some other DML constraints on your production instance. Then do this.

• Find out all the lines of code where assignment is happening (search for := )
• Find out all lines with select into variable statements.
• Isolate numeric value assignments from above lines (gotten in step 1 & 2) 
• Check with help of queries and surrounding code which statement could go wrong.
• At this juncture you may have isolated the 2-3 lines which could be the culprit.
• Compare and root out the statements by running against valid data not throwing this error.

So this was how you could possibly check for the ora 06502. Its of course manual effort and there is no magic wand. 

One notable option is to use dbms_debug through sqldeveloper or toad.  But I never found it useful. If I can run the package in debug mode I might as well change it and remove exception handlers instead.