9-1. Trapping an Exception
Problem
A procedure in your application has the potential to cause an exception to be raised. Rather than let the program exit and return control to the host machine, you want to perform some cleanup to ensure data integrity, as well as display an informative error message.
Solution
Write an exception handler for your procedure so that the exception can be caught and you can perform tasks that need to be completed and provide a more descriptive message. The following procedure is used to obtain employee information based upon a primary key value or an e-mail address. Beginning with the EXCEPTION keyword in the following example, an exception-handling block has been added to the end of the procedure in order to handle any exceptions that may occur when no matching record is found.
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 INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('YOU MUST ENTER AN EMAIL ADDRESS INCLUDING ' ||
'THE @ OR A POSITIVE INTEGER VALUE FOR THE ' ||
'EMPLOYEE ID.'),
END;
Here are the results of calling the procedure with various arguments:
SQL> EXEC OBTAIN_EMP_DETAIL(000);
THE INFORMATION YOU HAVE USED DOES NOT MATCH ANY EMPLOYEE RECORD
PL/SQL procedure successfully completed.
SQL> EXEC OBTAIN_EMP_DETAIL('TEST'),
YOU MUST ENTER AN EMAIL ADDRESS INCLUDING THE @ OR A POSITIVE INTEGER VALUE FOR
THE EMPLOYEE ID.
PL/SQL procedure successfully completed.
SQL> EXEC OBTAIN_EMP_DETAIL(200);
Jennifer Whalen - JWHALEN
PL/SQL procedure successfully completed.
This procedure is essentially the same as the one demonstrated in Recipe 8-1. The difference is that when an exception is raised, the control will go into the exception block. At that time, the code you place within the exception block will determine the next step to take as opposed to simply raising an Oracle error and returning control to the calling procedure, calling function, or host environment.
How It Works
To perform remedial actions when an exception is raised, you should always make sure to code an exception handler if there is any possibility that an exception may be thrown. The sole purpose of an exception handler is to catch exceptions when they are raised and handle the outcome in a controlled fashion. There are two different types of exceptions that can be raised by a PL/SQL application: internally defined and user defined. Oracle Database has a defined set of internal exceptions that can be thrown by a PL/SQL application. Those exceptions are known as internally defined. It is also possible to define your own exceptions, which are known as user defined.
An exception-handling block is structured like a CASE statement in that a series of exceptions is listed followed by a separate set of statements to be executed for each outcome. The standard format for an exception-handling block is as follows:
EXCEPTION
WHEN name_of_exception THEN
-- One or more statements
Exception blocks begin with the EXCEPTION keyword, followed by a series of WHEN..THEN clauses that describe different possible exceptions along with the set of statements that should be executed if the exception is caught. The exception name can be one of the Oracle internally defined exceptions, or it can be the name of an exception that has been declared within your code. To learn more about declaring exceptions, please see Recipe 9-3 in this chapter. In the solution to this recipe, the internally defined NO_DATA_FOUND exception is raised if an unknown e-mail address is entered into the procedure because there will be no rows returned from the query. When the exception block encounters the WHEN clause that corresponds with NO_DATA_FOUND, the statements immediately following the THEN keyword are executed. In this case, an error message is printed using the DBMS_OUTPUT package. However, in a real-world application, this is where you will place any cleanup or error handling that should be done to help maintain the integrity of the data accessed by your application.
An exception block can contain any number of WHEN..THEN clauses, and therefore, any number of exceptions can each contain their own set of handler statements. Even if a simple message was to be displayed, as is the case with the solution to this recipe, a different and more descriptive error message can be coded for each different exception that may possibly be raised. This situation is reflected in the second exception handler contained within the solution because it returns a different error message than the first.
As mentioned previously, Oracle contains a number of internally defined exceptions. Table 9-1 provides a list of the internally defined exceptions, along with a description of their usage.
Table 9-1. Oracle Internal Exceptions
An exception handler's scope corresponds to its enclosing code block. They have the same scope as a variable would have within a code block. If your code contains a nested code block, an exception handler that is contained within the nested code block can only handle exceptions raised within that code block. The outer code block can contain an exception handler that will handle exceptions for both the outer code block and the nested code block. If an exception is raised within the nested code block and there is no corresponding handler for an exception that has been raised within the nested code block, then the exception is propagated to the outer code block to look for a corresponding handler there. If no handler is found, then runtime will be passed to the procedure or function that called it or the host system, which is what you do not want to have occur. The following code demonstrates an example of using an exception handler within a nested code block:
DECLARE
CURSOR emp_cur IS
SELECT *
FROM EMPLOYEES;
emp_rec emp_cur%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' ||
emp_rec.last_name);
DECLARE
emp_dept departments.department_name%TYPE;
BEGIN
SELECT department_name
INTO emp_dept
FROM departments
WHERE department_id = emp_rec.department_id;
DBMS_OUTPUT.PUT_LINE('Department: ' || emp_dept);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION IN INNER BLOCK'),
END;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION IN OUTER BLOCK'),
END;
Multiple exceptions can be listed within the same exception handler if you want to execute the same set of statements when either of them is raised. You can do this within the WHEN clause by including two or more exception names and placing the OR keyword between them. Using this technique, if either of the exceptions that are contained within the clause is raised, then the set of statements that follows will be executed. Let's take a look at an exception handler that contains two exceptions within the same handler:
EXCEPTION
WHEN NO_DATA_FOUND OR INVALID_EMAIL_ADDRESS THEN
-- statements to execute
WHEN OTHERS THEN
-- statements to execute
END;
Note You cannot place the AND keyword in between exceptions because no two exceptions can be raised at the same time.
It is easy to include basic exception handling in your application. Code an exception-handling block at the end of each code block that may raise an exception. It is pertinent that you test your application under various conditions to try to predict which possible exceptions may be raised; each of those possibilities should be accounted for within the exception-handling block of your code.