asked on

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jknj72

ASKER

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;

jknj72

ASKER

thanks