24.2 Example of an Error Handling Function

The following is an example of an error handling function.

create or replace function apex_error_handling_example (
    p_error in apex_error.t_error )
    return apex_error.t_error_result
IS
    l_result          apex_error.t_error_result;
    l_reference_id    number;
    l_constraint_name varchar2(255);
BEGIN
    l_result := apex_error.init_error_result (
                    p_error => p_error );

    -- If it's an internal error raised by APEX, like an invalid statement or
    -- code which can't be executed, the error text might contain security
    -- sensitive information. To avoid this security problem we can rewrite the
    -- error to a generic error message and log the original error message for
    -- further investigation by the help desk.
    
    IF p_error.is_internal_error THEN

        -- mask all errors that are not common runtime errors (Access Denied
        -- errors raised by application / page authorization and all errors
        -- regarding session and session state)

        IF not p_error.is_common_runtime_error THEN

            -- log error for example with an autonomous transaction and return
            -- l_reference_id as reference#
            -- l_reference_id := log_error (
            --                       p_error => p_error );

            -- Change the message to the generic error message which doesn't
            -- expose any sensitive information.

            l_result.message    := 'An unexpected internal application
                                error has occurred. '||
                                'Please get in contact with XXX and provide '||
                                'reference#
                                '||to_char(l_reference_id, '999G999G999G990')||
                                ' for further investigation.';
            l_result.additional_info := null;
        END IF;
    ELSE

        -- Note: If you want to have friendlier ORA error messages, you can
        -- also define a text message with the name pattern
        --
        --      APEX.ERROR.ORA-number
        -- 
        -- There is no need to implement custom code for that.

        -- If it's a constraint violation like
        --
        --   -) ORA-00001: unique constraint violated
        --   -) ORA-02091: transaction rolled back (-> can hide a deferred
        --      constraint)
        --   -) ORA-02290: check constraint violated
        --   -) ORA-02291: integrity constraint violated - parent key not
        --      found
        --   -) ORA-02292: integrity constraint violated - child record found
        --
        -- We try to get a friendly error message from our constraint lookup
        -- configuration. If we don't find the constraint in our lookup table,
        -- we fallback to the original ORA error message.

        IF p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) THEN
            l_constraint_name := apex_error.extract_constraint_name (
                                     p_error => p_error );

            BEGIN
                select message
                  into l_result.message
                  from constraint_lookup
                 where constraint_name = l_constraint_name;
            EXCEPTION when no_data_found THEN null;

            -- Not every constraint has to be in our lookup table.

            END;
        END IF;

        -- If an ORA error has been raised, for example a 
        -- raise_application_error(-20xxx, '...') in a table trigger or in a 
        -- PL/SQL package called by a process and we haven't found the error 
        -- in our lookup table, then we just want to see the actual error text
        -- and not the full error stack with all the ORA error numbers.

        IF p_error.ora_sqlcode is not null and l_result.message = 
        p_error.message THEN
            l_result.message := apex_error.get_first_ora_error_text (
                                    p_error => p_error );
        END IF;

        -- If no associated page item/tabular form column has been set, we can
        -- use apex_error.auto_set_associated_item to automatically guess the 
        -- affected error field by examine the ORA error for constraint names 
        -- or column names.
        
        IF l_result.page_item_name is null and l_result.column_alias is null
        THEN
            apex_error.auto_set_associated_item (
                p_error        => p_error,
                p_error_result => l_result );
        END IF;
    END IF;

    RETURN l_result;
END apex_error_handling_example;