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

9-2. Catching Unknown Exceptions

Problem

Some exceptions are being raised when executing one of your procedures and you want to ensure that all unforeseen exceptions are handled using an exception handler.

Solution

Use an exception handler, and specify OTHERS for the exception name to catch all the exceptions that have not been caught by previous handlers. In the following example, the same code from Recipe 9-1 has been modified to add an OTHERS exception handler:

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;   email                     employees.email%TYPE;   valid_id_count            NUMBER ...