9-7. Raising Exceptions and Continuing Processing
Problem
The application you are coding requires a series of INSERT, UPDATE, and DELETE statements to be called. You want to add proper exception handling to your code and also ensure that processing continues and all of the statements are executed even if an exception is raised.
Solution
Enclose each statement within its own code block, and provide an exception handler for each of the blocks. When an exception is raised within one of the nested blocks, then control will be passed back to the main code block, and execution will continue. This style of coding is displayed in the following example:
CREATE OR REPLACE PROCEDURE delete_employee (in_emp_id IN NUMBER) AS
BEGIN
-- ENTER INITIAL NESTED CODE BLOCK TO PERFORM DELETE
BEGIN
-- DELETE EMP
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- perform statements
END;
-- ENTER SECOND NESTED CODE BLOCK TO PERFORM LOG ENTRY
BEGIN
-- LOG DELETION OF EMP
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- perform statements
END;
EXCEPTION WHEN OTHERS THEN
-- perform statements
END;
As this code stands, no exception will go on to become an unhanded exception because the outermost code block contains an exception handler using the OTHERS exception name. Every nested code block contains a handler, so every exception that is encountered in this application will be caught.
How It Works
Scope plays an important role when designing your application's exception-handling system. When doing so, you should think of your application and determine whether portions of the code need to be executed regardless of any exception being raised. If this is the case, then you will need to provide proper exception handling and still ensure that the essential code is executed each run.
The scope of an exception pertains to the code block in which the exception is declared. Once an exception has been encountered, program control halts immediately and is passed to the exception handler for the current block. If there is not an exception handler in the current code block or if no handler matches the exception that was raised, then control passes to the calling program or outer control block. Control is immediately passed to the exception handler of that program. If no exception handler exists or matches the exception being raised, then the execution of that block halts, and the exception is raised to the next calling program or outer code block, and so on.
This pattern can be followed any number of times. That is why the technique used in the solution to this recipe works well. There is one main code block that embodies two nested code blocks. Each of the blocks contains essential statements that need to be run. If an exception is raised within the DELETE block, then program control is passed back to its outer code block, and processing continues. In this case, both essential statements will always be executed, even if exceptions are raised.