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

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 ...