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 := 0;
valid_flag BOOLEAN := TRUE;
temp_emp_info VARCHAR2(50);
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ';
IF emp_info LIKE '%@%' THEN
temp_emp_info := substr(emp_info,0,instr(emp_info,'@')-1);
emp_qry := emp_qry || 'WHERE EMAIL = :emp_info';
ELSE
SELECT COUNT(*)
INTO valid_id_count
FROM employees
WHERE employee_id = emp_info;
IF valid_id_count > 0 THEN
temp_emp_info := emp_info;
emp_qry := emp_qry || 'WHERE EMPLOYEE_ID = :id';
ELSE
valid_flag := FALSE;
END IF;
END IF;
IF valid_flag = TRUE THEN
EXECUTE IMMEDIATE emp_qry
INTO emp_first, emp_last, email
USING temp_emp_info;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
ELSE
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AN UNEXPECTED ERROR HAS OCCURRED, PLEASE ' ||
'TRY AGAIN'),
END;
In this example, if an unexpected exception were to be raised, then the program control would transfer to the statements immediately following the WHEN OTHERS THEN clause.
Note In a real-world application, an exception should be manually reraised within the OTHERS handler. To learn more about determining the exception that was raised, please see Recipe 9-4.
How It Works
You can use the OTHERS handler to catch all the exceptions that have not been previously handled by any named exception handler. It is a good idea to include an OTHERS handler with any exception handler so that any unknown exceptions can be handled reasonably by your application. However, OTHERS should be used only to assist developers in finding application bugs rather than as a catchall for any exception. The format for using the OTHERS handler is the same as it is with other named exceptions; the only difference is that it should be the last handler to be coded in the exception handler. The following pseudocode depicts a typical exception handler that includes an OTHERS handler:
EXCEPTION
WHEN named_exception1 THEN
-- perform statements
WHEN named_exception2 THEN
-- perform statements
WHEN OTHERS THEN
-- perform statements
WHEN TO USE THE OTHERS HANDLER
As stated, the OTHERS handler will catch any exception that has not yet been caught by another handler. It is very important to code a separate handler for each type of named exception that may occur. However, if you have one set of statements to run for any type of exception that may occur, then it is reasonable to include only an OTHERS exception handler to catch exceptions that are unexpected. If no named exceptions are handled and an exception handler includes only an OTHERS handler, then the statements within that handler will be executed whenever any exception occurs within an application.