20.1.6.3 Examining Error Handling Function Code

Examine an Error Handling Function that logs internal errors and replaces constraint errors with friendlier messages.

The following APEX_ERROR_HANDLING function initializes the error result, then checks to see if the error is an APEX internal error. For uncommon internal errors, it calls the ADD_ERROR_LOG helper procedure to log the error. It then sets user-facing error message by assigning the alternate text to the message field in the l_result return record. It uses a generic message with the name UNEXPECTED_INTERNAL_ERROR.

For other errors, it checks p_error.ora_sqlcode for a database constraint code. If it finds one, it calls EXTRACT_CONSTRAINT_NAME in the APEX_ERROR package. Using that constraint name, say XXX, it calls APEX_LANG.GET_MESSAGE with message name CONSTRAINT_XXX. If that message name exists, it uses the the corresponding translatable text. Otherwise, GET_MESSAGE returns the original message name itself. So if the returned message is different from the message name passed in, it sets that text as the error message to show the user.

If no other path changed the error message yet, it simplifies the error by calling GET_FIRST_ORA_ERROR_TEXT in the APEX_ERROR package. Then, finally, it returns the l_result to the APEX engine.

-- In package messages_app
function apex_error_handling (
    p_error in apex_error.t_error )
    return apex_error.t_error_result
is
    l_result          apex_error.t_error_result;
    l_constraint_name varchar2(255);
    l_error_message   varchar2(255);
begin
    -- Always start by initializing the error result
    l_result := apex_error.init_error_result(p_error);
    -- For uncommon internal errors, log and show a generic message instead
    if p_error.is_internal_error then
        -- only log errors that are not common ones
        if not p_error.is_common_runtime_error then
            add_error_log( p_error );
            -- Change the error message to a generic one
            l_result.message         := apex_lang.get_message('UNEXPECTED_INTERNAL_ERROR');
            l_result.additional_info := null;
        end if;
    else
        -- Always show the error as inline error
        l_result.display_location := apex_error.c_inline_in_notification;

        -- 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
        --
        -- try to get a friendly error message from our constraint lookup table.
        -- If the constraint in our lookup table is not found, 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
                -- Try to find text message CONSTRAINT_XXX for constraint name XXX
                l_error_message := apex_lang.get_message('CONSTRAINT_'||l_constraint_name);
                -- If we found a text message (message will NOT just be the name itself)
                if l_error_message != 'CONSTRAINT_'||l_constraint_name then
                    l_result.message := l_error_message;
                end if;
            exception
                -- not every constraint has to be in our lookup table or          
                when no_data_found then null; 
            end;
        end if;            

        -- If its an error that we didn't find a message for yet, simplify it
        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;
    end if;
    return l_result;
end apex_error_handling;