Oracle and PL/SQL Recipes: A Problem-Solution Approach

9-4. Determining Which Error Occurred Inside the OTHERS Handler

Problem

Your code is continually failing via an exception, and the OTHERS handler is being invoked. You need to determine the exact cause of the exception so that it can be repaired.

Solution

Code the OTHERS exception handler as indicated by Recipe 9-2, and use the SQLCODE and DBMS_UTILITY.FORMAT_ERROR_STACK functions to return the Oracle error code and message text for the exception that has been raised. The following example demonstrates the usage of these functions, along with the procedure that was used in Recipe 9-3, for obtaining the error code and message when the OTHERS handler is invoked.

CREATE OR replace PROCEDURE salary_increase(emp_id       IN NUMBER,                                             pct_increase ...