Fixing ORA-20001 and RAISE_APPLICATION_ERROR Messages Not Showing in APEX

The Problem

You call RAISE_APPLICATION_ERROR(-20001, 'Order total exceeds limit') in a PL/SQL process but the user sees a raw Oracle error like “ORA-20001: Order total exceeds limit” with a stack trace instead of a clean message. Or worse, the message does not appear at all.

Why It Happens

APEX wraps PL/SQL processes in its own exception handling. When RAISE_APPLICATION_ERROR fires, APEX catches it and displays the full Oracle error text including the ORA code prefix. If the error occurs inside a deeply nested procedure call, the message may include the full call stack which is confusing for users.

Solution 1: Custom Error Handling Function

Create an error handling function in Shared Components that strips the ORA prefix from custom error codes (-20000 to -20999):

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 BETWEEN -20999 AND -20000 THEN
        l_result.message := REGEXP_REPLACE(p_error.message, '^ORA-2\d{4}: ', '');
        l_result.additional_info := NULL;
    END IF;
    RETURN l_result;
END;

Register this function in Application Definition, then Error Handling. Every custom error now shows a clean message.

Solution 2: Use APEX_ERROR.ADD_ERROR Instead

BEGIN
    IF :P10_TOTAL > 50000 THEN
        APEX_ERROR.ADD_ERROR(
            p_message          => 'Order total exceeds the $50,000 limit.',
            p_display_location => APEX_ERROR.C_INLINE_WITH_FIELD_AND_NOTIF,
            p_page_item_name   => 'P10_TOTAL'
        );
    END IF;
END;

This displays the error inline next to the field and in the notification area without any ORA prefix. It also does not abort processing, so multiple errors can be collected before returning to the user.