9-9. Tracing an Exception to Its Origin

Problem

Your application continues to raise an exception that is being caught with the OTHERS handler. You've used SQLCODE and DBMS_UTILITY.FORMAT_ERROR_STACK to help you find the cause of the exception but are still unable to do so.

Solution

Use the stack trace for the exception to trace the error back to its origination. In particular, use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_CALL_TRACE to help you find the cause of the exception. The following solution demonstrates the use of FORMAT_ERROR_BACKTRACE:

CREATE OR REPLACE PROCEDURE obtain_emp_detail(emp_info IN VARCHAR2) IS   emp_qry             VARCHAR2(500);   emp_first           employees.first_name%TYPE;   emp_last            employees.last_name%TYPE; ...

Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.