APEX 4.2 error handling function example

Few weeks ago I experimented with APEX 4.2 error handling.
APEX documentation provides nice Example of an Error Handling Function witch I used for my application.

Here I explain what changes I made to documentation example function.

First I started to think part where constraint violations are handled:

        -- 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 configuration.
        -- 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
                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;

Documentation example shows that you should create custom lookup table to fetch error messages for different constraints.
Isn't there anything build in for this in APEX?

Same time when I was thinking this, new business requirement arrived. I needed to translate this application to other languages.
Now it was obvious that also error messages need to be translated.

I have never translated any application to other languages, so I started to check APEX_LANG package documentation.

I found that MESSAGE Function could be useful e.g. translating error messages.
Using that function I could return different language error messages .

Noticed that APEX_LANG.MESSAGE function will return same value witch is passed in parameter p_name if it could not find that string from APEX text messages. That is useful if I do not like create message for all constraints.

So, I changed example function code like this:

        -- 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 APEX text messages.
        -- If the constraint name not match text message name, 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 );

          -- fetch error message from APEX Text Messages
          l_err_msg := APEX_LANG.MESSAGE(l_constraint_name);

          -- not every constraint has to be in Text Messages
          IF NOT l_err_msg = l_constraint_name THEN
            l_result.message := l_err_msg;
          END IF;

        END IF;

Then I added text messages to my application.
In this example text message name is same as constraint name where I like show friendly error message.

In this point my application wasn't yet translated and still nice error message was displayed to end users if there was constraint violation error.
Even you will not translate application to other languages, you can use APEX text messages to store error messages instead of creating custom lookup table.

Next I started to check example function part where APEX internal errors were handled.

    -- If it's an internal error raised by APEX, like an invalid statement or
    -- code which cannot be executed, the error text might contain security sensitive
    -- information. To avoid this security problem 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
        -- Access Denied errors raised by application or page authorization should
        -- still show up with the original error message
        if  p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED'              
        and p_error.apex_error_code not like 'APEX.SESSION_STATE.%' 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 is not exposed
            -- 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

First I noticed that example function will convert duplicate page submissions error to generic message.
I did not want that because I think original error message provide sufficient information to end user about error.

Secondly, generic error message could be stored to APEX text messages as you can pass values to messages using APEX_LANG.MESSAGE function parameters p0 - p9. Then it is easy to maintain trough APEX builder and translate if needed.

Third, I noticed that documentation example shows that you should create custom function to log real error message and return reference number.

Again I was wondering isn't there anything build in to store real error messages and provide reference number to user?

For reference number I thought that APEX session id would be enough, even there might be several different errors for one user in same session.

But where to store real error message?

For that I looked APEX_DEBUG package documentation if there is some useful function or procedure.
There I found ERROR Procedure that seems to be useful.

So, I added another text message for generic error message

And modified example function code like this:

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

      -- Session state errors should still show up with the original error message
      IF NOT p_error.apex_error_code LIKE 'APEX.SESSION_STATE.%'
      -- Access Denied errors raised by application or page authorization should
      -- still show up with the original error message
      AND NOT p_error.apex_error_code = 'APEX.AUTHORIZATION.ACCESS_DENIED'
      -- Duplicate page submissions errors should still show up with the original error message
      AND NOT p_error.apex_error_code = 'APEX.PAGE.DUPLICATE_SUBMIT'
      THEN
        -- log error to application debug information
        APEX_DEBUG.ERROR(
          'Error handler: %s %s %s',
           p_error.apex_error_code,
           l_result.message,
           l_result.additional_info
        );
        -- Change the message to the generic error message which is not expose
        -- any sensitive information.
        l_result.message := APEX_LANG.MESSAGE('MY_GENERIC_ERROR', v('SESSION'));
        l_result.additional_info := NULL;

      END IF;
    ELSE

After that internal APEX error messages looked like this

I can now find entry from application debug info by searching reference number from session column

And see what is actual error when clicking identifier number.