Skip to main content

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.







Labels:
Location: Indiana, USA

Comments

Post a Comment

Please leave your relevant comments and questions only.

Popular posts from this blog

Cyclic blocking session removal script written by me for Oracle Database.

Plenty of times we have cyclic database blocking sessions. We have the script which runs in loop and kills them , only to see new blocking sessions have resurfaced. This is a classic scenario that happens many time due to poor application design. A was blocking B , B was blocking C. Unless you have some automated blocking session clearing script. You would run a script at point in time killing session of A. Giving the lock ownership to B.   B could realize it late that he has the lock and not commit his work. So DBA again goes in and sees block kills B's session. In meantime Frustrated A logs in and start his activity again queuing him up in wait for lock retrieval . This goes on and you end up running the script 5-10 times to kill these sessions to finally clear the blocks. Note this is not a deadlock which is normally apprehended and identified by oracle. In such case we had written below plsql to clear the sessions. This basically goes in and check for locks ever
Post a Comment
Read more

Oracle Connect By Clause to Perform Hierarchical Queries.

 If we want to perform hierarchical queries we need to use connect by clause in our sql query. Hierarchical query is any query wherin you want to display parent child relationship with the case that a parent may have multiple children and those have their own creating a tree like structure. One classic scenario highlighted on almost all sites is employee table wherein employee's manager is stored in the employee row the manager id is again one of the employee in the employee table. But we are not going to take it as you can reference it almost on any site that hosts oracle based content. We are going to do a query based on DBA_DEPEDENCIES or ALL_DEPENDANCIES if you do not have access to former. In DBA_DEPENDANCY table we have hierarchy of REFERENCED_NAME(parent) and the referring objects in column NAME(child ) that have dependency on the parent. with this in mind we would need to set our connect by clause as prior NAME= REFERENCED_NAME This confirms that REF
Post a Comment
Read more

Modify Windows file Change / modified date.

There is no straightforward command in windows to change the file modify date (Commonly referred to as timestamp) . I have spent significant time searching UNIX touch -t like command equivalent in windows but could not find a command that could achieve this feat. However there is a a way around through  a VBS . This VBScript would let you modify the file timestamp (Modified date ) to the one you give in the parameter. In below example it would be 29-Oct-2015.   Sub ChangeModifiedDate(strFolder, strFile, dteNew) Dim oShell Dim objFolder Set oShell = CreateObject("Shell.Application") Set oFolder = oShell.NameSpace(strFolder) oFolder.Items.Item(strFile).ModifyDate = dteNew End Sub changemodifieddate "D:\myfolder\fmx","new_empl.fmx","29-10-2015" So here are the things to change. Change the last line 1 st argument is the windows folder. 2nd Argument is the file name 3rd Argument is the Date modified desired. One needs
Post a Comment
Read more