Exception handling processes

An exception may occur in the declarative part, the executable part, or the exception-handling part. Exceptions that occur in different parts are handled by using different processes.

Handle exceptions raised in the executable part

For exceptions raised in the executable part, the handling method varies in the following two cases:

• Case 1: If a handler is set for the current PL block, execute the handler, execute the block after the exception is cleared, and then transfer control to the outer block.

• Case 2: If the current PL block does not capture the exception in the exception-handling part, the exception is raised during the execution of the enclosing block. Then, repeat the execution steps in Case 1.

Example:

obclient> BEGIN
         DECLARE
             v_sal   employees.salary%TYPE;
         BEGIN
             SELECT salary INTO v_sal FROM employees
             WHERE empno=100;
         EXCEPTION
             WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLCODE||'-INNER');
         END;
     EXCEPTION
         WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'-OUTER');
     END;
     /
Query OK, 0 rows affected

-4026-INNER

After the exception handler of the inner block is deleted, the exception is captured by the outer block. Example:

obclient> BEGIN
         DECLARE
             v_sal   employees.salary%TYPE;
         BEGIN
             SELECT salary INTO v_sal FROM employees
             WHERE empno=100;
         END;
     EXCEPTION
         WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'-OUTER');
     END;
     /
Query OK, 0 rows affected

-4026-OUTER

Handle exceptions raised in the declarative part

An exception raised in the declarative part can be captured only by the outer block, but not by the exception handler of the current block.

obclient> BEGIN
         DECLARE
             abc number(3):='a';
         BEGIN
             null;
         EXCEPTION
             WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLCODE||'-INNER');
         END;
     EXCEPTION
         WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'-OUTER');
     END;
     /
Query OK, 0 rows affected

-5114-OUTER

Handle exceptions

We recommend that programmers use codes to handle exceptions in general applications. If exceptions are not properly handled, the program stops when an error occurs and cannot provide sufficient diagnostic information for subsequent troubleshooting.