3.5.9 PRAGMA EXCEPTION_INIT

Table of Contents Previous Next


3 Stored Procedure Language : 3.5 Control Structures : 3.5.9 PRAGMA EXCEPTION_INIT

PRAGMA EXCEPTION_INIT associates a user-defined error code with an exception. A PRAGMA EXCEPTION_INIT declaration may be included in any block, sub-block or package. You can only assign an error code to an exception (using PRAGMA EXCEPTION_INIT) after declaring the exception. The format of a PRAGMA EXCEPTION_INIT declaration is:
PRAGMA EXCEPTION_INIT(exception_name,
{exception_number | exception_code})
exception_name is the name of the associated exception.
exception_number is a user-defined error code associated with the pragma. If you specify an unmapped exception_number, the server will return a warning.
exception_code is the name of a pre-defined exception. For a complete list of valid exceptions, see the Postgres core documentation available at:
The previous section (User-defined Exceptions) included an example that demonstrates declaring a user-defined exception in a package. The following example uses the same basic structure, but adds a PRAGMA EXCEPTION_INIT declaration:
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.
ar.check_ balance(getcustomerbalance(customerid), amount);
DBMS_OUTPUT.PUT_LINE ('This account is overdrawn.');
When ar.check_balance raises an exception, execution jumps to the exception handler defined in purchase.
The following example demonstrates using a pre-defined exception. The code creates a more meaningful name for the no_data_found exception; if the given customer does not exist, the code catches the exception, calls DBMS_OUTPUT.PUT_LINE to report the error, and then re-raises the original exception:

3 Stored Procedure Language : 3.5 Control Structures : 3.5.9 PRAGMA EXCEPTION_INIT

Table of Contents Previous Next