Use PRAGMA EXCEPTION_INIT to associate an error code with an exception - PolarDB

PRAGMA EXCEPTION_INIT binds a user-defined exception name to a specific error code. Once bound, exception handlers can catch that specific error by name rather than by raw error code.

Declare the exception before using PRAGMA EXCEPTION_INIT. The pragma can appear in any block, sub-block, or package.

Syntax

PRAGMA EXCEPTION_INIT(exception_name,
                      {exception_number | exception_code})

Parameters

Parameter	Description
`exception_name`	The name of the associated exception.
`exception_number`	A user-defined numeric error code to associate with the exception. If the value is not a mapped error code, the server returns a warning.
`exception_code`	The name of a predefined exception. For the full list, see PostgreSQL error codes.

How it works

Using PRAGMA EXCEPTION_INIT requires two steps in the declarative section:

Declare the exception name.
```
exception_name EXCEPTION;
```

Bind it to an error code or predefined exception.

PRAGMA EXCEPTION_INIT(exception_name, error_code_or_exception_name);

After binding, raise the exception with RAISE and catch it by name in an EXCEPTION block.

Bind a user-defined error code

Use a negative integer error code to create a named exception for application-specific error conditions you want to catch in outer PL/SQL layers or client programs.

The following example declares the overdrawn exception in the ar package and binds it to error code -20100. The check_balance procedure raises overdrawn when the requested amount exceeds the available balance.

CREATE OR REPLACE PACKAGE ar AS
  overdrawn EXCEPTION;
  PRAGMA EXCEPTION_INIT (overdrawn, -20100);
  PROCEDURE check_balance(p_balance NUMBER, p_amount NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY ar AS
   PROCEDURE check_balance(p_balance NUMBER, p_amount  NUMBER)
   IS
   BEGIN
       IF (p_amount > p_balance) THEN
         RAISE overdrawn;
       END IF;
    END;
    END;

The purchase procedure calls check_balance. If check_balance raises overdrawn, execution transfers to the exception handler in purchase.

CREATE PROCEDURE purchase(customerID int, amount NUMERIC)
AS
  BEGIN
     ar.check_ balance(getcustomerbalance(customerid), amount);
       record_purchase(customerid, amount);
  EXCEPTION
     WHEN ar.overdrawn THEN
      DBMS_OUTPUT.PUT_LINE ('This account is overdrawn.') ;
      DBMS_OUTPUT.PUT_LINE ('SQLCode :'||SQLCODE||' '||SQLERRM );
END;

The exception handler prints the error message and SQLCODE information:

This account is overdrawn.
SQLCODE: -20100 User-Defined Exception

Create an alias for a predefined exception

Use PRAGMA EXCEPTION_INIT with a predefined exception name to create a more descriptive alias, making exception handlers easier to read.

The following example binds unknown_customer to the predefined no_data_found exception. If no matching customer exists, the handler logs the error and re-raises the original exception.

CREATE OR REPLACE PACKAGE ar AS
  unknown_customer EXCEPTION;
  PRAGMA EXCEPTION_INIT (unknown_customer, no_data_found);
  PROCEDURE check_balance(p_customer_id NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY ar AS
   PROCEDURE check_balance(p_customer_id NUMBER)
   IS
   DECLARE
     v_balance NUMBER;
   BEGIN
     SELECT balance INTO v_balance FROM customer
       WHERE cust_id = p_customer_id;
   EXCEPTION WHEN unknown_customer THEN
     DBMS_OUTPUT.PUT_LINE('invalid customer id');
     RAISE;
   END;
END;

PolarDB:PRAGMA EXCEPTION_INIT

Syntax

How it works

Bind a user-defined error code

Create an alias for a predefined exception

Related topics