Oracle in a Nutshell

Exception Propagation

Exceptions raised in a PL/SQL block propagate to an outer block if they are unhandled or reraised in the exception section.

When an exception occurs, PL/SQL looks for an exception handler that checks for the exception (or is the WHEN OTHERS clause documented later in this section) in the current block. If a match is not found, then PL/SQL propagates the exception to the enclosing block or calling program. This propagation continues until the exception is handled or propagated out of the outermost block, back to the calling program. In this case, the exception is “unhandled” and it stops the calling program.

When an exception is raised in a PL/SQL block, it does not roll back your current transaction, even if the block itself issued an INSERT, UPDATE, or DELETE. You must issue your own ROLLBACK statement if you want to clean up your transaction as a result of the exception.

If your exception goes unhandled (propagates out of the outermost block), however, most host environments will then force an automatic, unqualified rollback of any outstanding changes in your session.

Once an exception is handled, it will not propagate upward. If you want to trap an exception, display a meaningful error message, and have the exception propagate upward as an error, you must reraise the exception. The RAISE statement, described earlier, can reraise the current exception or raise a new exception.