Exception handling order

Exceptions can occur in the declaration section, the execution section, or the exception handling section. The exception handling process varies depending on where the exception occurs.

Exceptions in the execution section

When an exception occurs in the execution section, the following two scenarios apply:

• Scenario 1: If the current PL block has an exception handler for this exception, the handler is executed, and the block completes successfully. Control is then passed to the outer block.

• Scenario 2: If the current PL block does not have an exception handler for this exception, it means the exception was raised in the execution of the outer block. The process described in Scenario 1 is then repeated.

Here is an 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. Here is an 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

Exceptions in the declaration section

Exceptions that occur in the declaration section can only be captured by the outer block and cannot be captured 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

Handling exceptions

In general application processing, it is recommended to capture and handle exceptions. If exceptions are not properly handled, the program will be terminated when an error occurs, and insufficient diagnostic information will be provided for subsequent error troubleshooting.