Creating Custom APEX Error Handling Functions

Why Custom Error Handling Matters

By default, when a database error occurs during an APEX page process, the user sees the raw Oracle error message: something like “ORA-00001: unique constraint (HR.UK_EMPLOYEES_EMAIL) violated.” This is meaningless to most users and exposes internal schema details. A custom error handling function intercepts these errors and translates them into user-friendly messages.

Creating the Error Function

Create a PL/SQL function that accepts an APEX_ERROR.T_ERROR record and returns an APEX_ERROR.T_ERROR_RESULT record:

CREATE OR REPLACE FUNCTION custom_error_handler(
    p_error IN APEX_ERROR.T_ERROR
) RETURN APEX_ERROR.T_ERROR_RESULT
IS
    l_result APEX_ERROR.T_ERROR_RESULT;
BEGIN
    l_result := APEX_ERROR.INIT_ERROR_RESULT(p_error => p_error);

    -- Translate constraint violations to friendly messages
    IF p_error.ora_sqlcode = -1 THEN
        -- Unique constraint violation
        IF p_error.message LIKE '%UK_EMPLOYEES_EMAIL%' THEN
            l_result.message := 'This email address is already in use by another employee.';
            l_result.additional_info := NULL;
            l_result.display_location := APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF;
            l_result.page_item_name := 'P10_EMAIL';
        ELSIF p_error.message LIKE '%UK_ORDERS_PO_NUM%' THEN
            l_result.message := 'This purchase order number already exists.';
            l_result.display_location := APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF;
            l_result.page_item_name := 'P20_PO_NUMBER';
        END IF;

    ELSIF p_error.ora_sqlcode = -2291 THEN
        -- Foreign key violation
        l_result.message := 'The selected reference value is no longer valid. '
                         || 'Please refresh the page and try again.';
        l_result.additional_info := NULL;

    ELSIF p_error.ora_sqlcode = -2292 THEN
        -- Child records exist
        l_result.message := 'This record cannot be deleted because it has '
                         || 'related records that depend on it.';
        l_result.additional_info := NULL;

    ELSIF p_error.ora_sqlcode BETWEEN -20999 AND -20000 THEN
        -- Custom application errors (RAISE_APPLICATION_ERROR)
        -- Strip the ORA-2xxxx prefix and show just the message
        l_result.message := REGEXP_REPLACE(
            p_error.message, '^ORA-2\d{4}: ', '');
        l_result.additional_info := NULL;
    END IF;

    -- Log all errors for debugging
    log_apex_error(
        p_error_code    => p_error.ora_sqlcode,
        p_error_message => p_error.message,
        p_page_id       => V('APP_PAGE_ID'),
        p_app_user      => V('APP_USER')
    );

    RETURN l_result;
END custom_error_handler;
/

Registering the Error Function

In your application’s Shared Components, go to Application Definition and then Error Handling. Set the “Error Handling Function” to your function name: custom_error_handler. From that point on, every unhandled database error in your application passes through this function before being displayed to the user.

Handling Different Error Sources

The APEX_ERROR.T_ERROR record provides rich context about the error. The is_internal_error flag distinguishes between APEX framework errors and application errors. The component fields tell you which page process, validation, or computation triggered the error. Use this context to provide the most specific error message possible.

Inline Error Display

Setting l_result.display_location to APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF and specifying a page_item_name makes the error appear next to the relevant field, just like a validation error. This gives users immediate visual feedback about which field caused the problem, which is a much better experience than a generic error notification at the top of the page.