O'Reilly logo

Oracle and PL/SQL Recipes: A Problem-Solution Approach by Matt Arena, Josh Juneau

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required