PolarDB:Error handling

Predefined exceptions

Predefined exceptions are named conditions built into the database. PolarDB supports all native PostgreSQL predefined exceptions and the most common Oracle predefined exceptions, so you can use either style in a WHEN clause.

-- Implicit: division by zero triggers an exception automatically
DECLARE
    result INT;
BEGIN
    result := 1 / 0;
END;

-- Explicit: PostgreSQL style
BEGIN
    RAISE division_by_zero;
END;

-- Explicit: Oracle style
BEGIN
    RAISE zero_divide;
END;

-- Catch a specific exception: PostgreSQL style
DECLARE
    result INT;
BEGIN
    result := 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        DBMS_OUTPUT.PUT_LINE('division by zero');
END;

-- Catch a specific exception: Oracle style
DECLARE
    result INT;
BEGIN
    result := 1 / 0;
EXCEPTION
    WHEN zero_divide THEN
        DBMS_OUTPUT.PUT_LINE('zero divide');
END;

-- Catch all exceptions
DECLARE
    result INT;
BEGIN
    result := 1 / 0;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('division by zero');
END;

Non-predefined exceptions

Oracle defines many error codes that have no built-in name. To handle these by name, declare an exception variable and associate it with an Oracle error code using PRAGMA EXCEPTION_INIT.

Follow these three steps:

1. In the DECLARE section, declare an exception variable:

   my_exception EXCEPTION;

2. Bind the variable to a specific SQLCODE using PRAGMA EXCEPTION_INIT:

   Important

   The error code must be 100 (for the no_data_found exception) or a negative integer in the range -1000000 to -1. Only the range -65535 to -1 maps to valid Oracle SQLCODEs.

   PRAGMA EXCEPTION_INIT(my_exception, -1476);

3. In the EXCEPTION section, catch the exception by name or with WHEN OTHERS:

   WHEN my_exception THEN
       ...

Example

DECLARE
    result INT;
    my_exception EXCEPTION;
    PRAGMA EXCEPTION_INIT(my_exception, -66666);
BEGIN
    result := 1 / 0;
EXCEPTION
    WHEN my_exception THEN
        DBMS_OUTPUT.PUT_LINE('zero divide');
END;

User-defined exceptions

Oracle reserves the error code range -20999 to -20000 for user-defined exceptions. Use RAISE_APPLICATION_ERROR to raise a custom error with a specific code and message from within this range.

Example

DECLARE
    salary NUMBER := 4000;
BEGIN
    IF salary < 5000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary is below the allowed minimum.');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Custom Error: ' || SQLERRM);
END;

Error codes and messages

Inside an EXCEPTION section, three built-in variables provide information about the error:

Example

DECLARE
    result INT;
BEGIN
    result := 1 / 0;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLSTATE: ' || SQLSTATE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;

The output is:

SQLCODE: -1476
SQLSTATE: 22012
SQLERRM: division by zero