3.5.8 User-defined Exceptions

Table of Contents Previous Next


3 Stored Procedure Language : 3.5 Control Structures : 3.5.8 User-defined Exceptions

Any number of errors (referred to in PL/SQL as exceptions) can occur during program execution. 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 may be a pre-defined error that is generated by the server, or may be a logical error that raises a user-defined exception.
User-defined exceptions are never raised by the server; 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 will provoke a user-defined exception.
exception_name EXCEPTION;
RAISE exception_name;
exception_name is the name of the exception.
block_name.exception_name
The scope of a declaration is limited to the block in which it is declared unless it is 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:
The following procedure (purchase) calls the check_balance procedure. If p_amount is greater than p_balance, check_balance raises an exception; purchase catches the ar.overdrawn exception. purchase must refer to the exception with a package-qualified name (ar.overdrawn) because purchase is not defined within the ar package.
CReATE PROCEDURE purchase(customerID int, amount NUMERIC)
ar.check_ balance(getcustomerbalance(customerid), amount);
record_purchase(customerid, amount);
WHEN ar.overdrawn THEN
raise_credit_limit(customerid, amount*1.5);
When ar.check_balance raises an exception, execution jumps to the exception handler defined in purchase:
WHEN ar.overdrawn THEN
raise_credit_limit(customerid, amount*1.5);

3 Stored Procedure Language : 3.5 Control Structures : 3.5.8 User-defined Exceptions

Table of Contents Previous Next