Exception handling

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 exception is handled in one of the following two ways:

• Case 1: If the current PL block has an exception handler for the exception, the exception handler is executed, and the block is successfully completed. Then, control is passed to the outer block.

• Case 2: If the current PL block does not have an exception handler for the exception, the exception is raised in the execution of the outer block. Then, case 1 is 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 in the declaration section can be captured only by the outer block, not by the current block's exception handler.

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 it will not provide sufficient diagnostic information for subsequent error troubleshooting.