User-defined exceptions v16
Any number of errors (referred to in PL/SQL as exceptions) can occur while a program executes. When an exception is thrown, normal execution of the program stops, and control of the program transfers to the error-handling portion of the program. An exception can be a predefined error that's generated by the server, or it can be a logical error that raises a user-defined exception.
The server never raises user-defined exceptions. They are raised explicitly by a
RAISE statement. A user-defined exception is raised when a developer-defined logical rule is broken. A common example of a logical rule being broken occurs when a check is presented against an account with insufficient funds. An attempt to cash a check against an account with insufficient funds causes a user-defined exception.
You can define exceptions in functions, procedures, packages, or anonymous blocks. While you can't declare the same exception twice in the same block, you can declare the same exception in two different blocks.
Before implementing a user-defined exception, you must declare the exception in the declaration section of a function, procedure, package, or anonymous block. You can then raise the exception using the
exception_name is the name of the exception.
Unhandled exceptions propagate back through the call stack. If the exception remains unhandled, the exception is eventually reported to the client application.
User-defined exceptions declared in a block are considered to be local to that block and global to any blocks nested in the block. To reference an exception that resides in an outer block, you must assign a label to the outer block. Then, preface the name of the exception with
Outer blocks can't reference exceptions declared in nested blocks.
The scope of a declaration is limited to the block in which it's declared unless it's created in a package and, when referenced, qualified by the package name. For example, to raise an exception named
out_of_stock that resides in a package named
inventory_control, a program must raise an error named
This example declares a user-defined exception in a package. The user-defined exception doesn't require a package qualifier when raised in
check_balance, since it resides in the same package as the exception:
Here, the procedure
purchase calls the
check_balance procedure. If
p_amount is greater than
check_balance raises an exception.
purchase catches the
purchase must refer to the exception with a package-qualified name (
purchase isn't defined in the
ar.check_balance raises an exception, execution jumps to the exception handler defined in
The exception handler raises the customer’s credit limit and ends. When the exception handler ends, execution resumes with the statement that follows