Continuing After an Exception Is Raised

Previous

Next

By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue execution from the spot where an exception occurred, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN-END block with an exception handler inside of a loop, you can continue executing the loop if some loop iterations raise exceptions.

You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own subblock with its own exception handlers. If an error occurs in the subblock, a local handler can catch the exception. When the subblock ends, the enclosing block continues to execute at the point where the subblock ends, as shown in Example: Continuing After an Exception in PL/SQL.

-- create a temporary table for this example
CREATE TABLE employees_temp AS 
  SELECT employee_id, salary, commission_pct FROM employees;

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp VALUES (303, 2500, 0);
  BEGIN -- subblock begins
    SELECT salary / commission_pct INTO sal_calc FROM employees_temp
      WHERE employee_id = 303;
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        sal_calc := 2500;
  END; -- subblock ends
  INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/
-- view the results
SELECT * FROM employees_temp WHERE employee_id = 303 OR employee_id = 304;
-- drop the temporary table
DROP TABLE employees_temp;

In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 2500. Execution of the handler is complete, so the subblock terminates, and execution continues with the INSERT statement.