Creating Custom APEX Error Handling Functions

Why Custom Error Handling Matters

By default, database errors in APEX show raw Oracle messages like “ORA-00001: unique constraint (HR.UK_EMPLOYEES_EMAIL) violated.” This is meaningless to users and exposes schema details. A custom error handling function intercepts these and translates them into friendly messages.

Creating the Error Function

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);

    IF p_error.ora_sqlcode = -1 THEN
        IF p_error.message LIKE '%UK_EMPLOYEES_EMAIL%' THEN
            l_result.message := 'This email address is already in use.';
            l_result.display_location := APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF;
            l_result.page_item_name := 'P10_EMAIL';
        END IF;
    ELSIF p_error.ora_sqlcode = -2292 THEN
        l_result.message := 'This record cannot be deleted because '
                         || 'related records depend on it.';
    ELSIF p_error.ora_sqlcode BETWEEN -20999 AND -20000 THEN
        l_result.message := REGEXP_REPLACE(p_error.message, '^ORA-2\d{4}: ', '');
    END IF;

    l_result.additional_info := NULL;
    RETURN l_result;
END custom_error_handler;
/

Registering the Function

In Shared Components go to Application Definition then Error Handling. Set the Error Handling Function to custom_error_handler. Every unhandled error now passes through your function before display.

Inline Error Display

Setting display_location to 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.

Error Logging

Add logging inside the error handler to capture all application errors in a custom table. This gives you a centralized audit trail of every error, which page it occurred on, and which user triggered it, invaluable for production support.