PolarDB:Error handling

Predefined exceptions

You can use predefined exceptions from both PostgreSQL, such as division_by_zero, and Oracle, such as zero_divide. You can raise and catch these exceptions in your SQL blocks.

DECLARE
    result INT;
BEGIN
    result := 1 / 0;
END;

-- PostgreSQL style
BEGIN
    RAISE division_by_zero;
END;

-- Oracle style
BEGIN
    RAISE zero_divide;
END;

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

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

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

Non-predefined exceptions

Oracle syntax includes many error codes that are not named, predefined exceptions. To handle these errors by name, you can declare an exception variable and associate it with an Oracle error code.

The process involves three steps:

1. In the DECLARE section, declare an exception, such as my_exception EXCEPTION;.

2. Use the PRAGMA EXCEPTION_INIT statement to bind the variable to a specific SQLCODE, such as PRAGMA EXCEPTION_INIT(my_exception, -1476);.

   Important

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

3. In the EXCEPTION section, use WHEN my_exception or WHEN OTHERS to catch the exception.

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;

Custom Exception (RAISE_APPLICATION_ERROR)

Oracle reserves the integer range from -20999 to -20000 for predefined exceptions and non-predefined exceptions. You can use the RAISE_APPLICATION_ERROR procedure to raise a custom exception with a specific error code and message from within this range.

Example

DECLARE
    salary NUMBER := 4000;
BEGIN
    -- Validate business rules, trigger an error when conditions are not met
    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 error messages

Within an EXCEPTION block, you can use the following built-in functions to get information about an error:

• SQLSTATE: The native PostgreSQL error code. This is a five-character string where the first two characters represent the error class.

• SQLCODE: The Oracle-compatible integer error code. It falls into one of three categories:

  • 100: Indicates the no_data_found exception, indicating that a query statement returned no results.

  • -65535 to -1: Indicates a standard Oracle error.

  • -99999: Indicates an invalid value or unsupported scenario in PolarDB for PostgreSQL (Compatible with Oracle).

• SQLERRM: The error message that provides a detailed description of the exception.

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;

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