Oracle exception to find where procedure is crashing

I am trying to debug my procedure and I am getting the error

OTHERS Error Code -6502 Err Msg ORA-06502: PL/SQL: numeric or value error: character to number conversion error POL NBR = WC291645277 EFFDT = 01-MAR-14

I have so much going on that unless I step through the code I cant find exactly where its bombing. Is there anything I can do other then put a SYS.DBMS_OUTPUT.PUT_LINE everywhere to find out exactly what is going on? I understand that I have to be aware of what is being matched to each other when Im setting variables and everything but I was wondering if there is something like a call stack type of thing where I can find the line of code that is throwing the error? I know the basics and have been trying everything I can but I was curious if there is something I may be overlooking, like if I can somehow put the variable or field that is bombing in the Exception? In other words, if a line of code bombs and ends up in my WHEN OTHERS Exception, can I somehow code it to identify where the code is bombing? Im sure its somewhat of a longshot but I was curious if someone knows something I don't. I also don't have privs to debug in PROD and that's where its bombing. If can do it without the debugging privs and can get it into my Exception Id like to do that.

THANKS
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
That looks like a customized error message.

Yes, there is a stack trace available but you need to code for it.  

Check out dbms_utility.format_error_stack and/or dbms_utility.format_error_backtrace

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73210


You have the proper POLNBR and Date.  Shouldn't be that difficult to track it down manually.  Look in the code where you convert a string to a number (either implicitly or explicitly).  Then query the data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
Its not difficult its just tedious. There are many things to check and I thought there may be an easier way to do it that's all.. Ill try out that function. It may be what I am looking for. Thanks

I forgot to add my Exception where the message is actually the ERR_MESSAGE with the POLNBR and Date concatenated in the OTHERS portion of the Exception.

     EXCEPTION
              WHEN NO_DATA_FOUND THEN
                Insert Into PCOV_POLICY_MESSAGE_TAB(ERR_ID, ERR_MESSAGE, ERR_TIMESTAMP) Values  (i,'NO DATA FOUND ' || l_COV_polNbr || ' EFFDT = ' || l_COV_covEffDate ,SYSTIMESTAMP);
                COMMIT;
                i := l_POLICY_TAB.next(i);
                Continue;
              WHEN OTHERS THEN
                  err_code := SQLCODE;
                  err_msg := SUBSTR(SQLERRM, 1, 2000);
               
                Insert Into PCOV_POLICY_MESSAGE_TAB(ERR_ID, ERR_MESSAGE, ERR_TIMESTAMP) Values  (i,'OTHERS Error Code ' || err_code  || ' Err Msg ' || err_msg  || ' POL NBR = ' || l_COV_polNbr || ' EFFDT = ' || l_COV_covEffDate ,SYSTIMESTAMP);
                COMMIT;
                i := l_POLICY_TAB.next(i);
                Continue;
              END;
jknj72Author Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.