Enhancing the APEX Error Handling Function with Logger

Many, many moons ago, I created an error handling function for Oracle APEX, just like the original sample provided by Patrick Wolf for the 4.1 error handling feature. You'll probably find a strong correlation between the two events.

OK, now what?

We normally see this if an exception was propagated within a PL/SQL Dynamic Action.
In this case I thought it was time for a little upgrade.

I noticed the sample code, now part of the APEX_ERROR documentation, suggested including a reference ID, perhaps from some logging package.
Conveniently, we use such a package, a popular one among the PL/SQL community called Logger.

I glanced through the package specification, and for a moment I thought it was missing a function that returned the new log ID.

I found a procedure with an OUT parameter instead, so here I log some contextual information about the error.

logger_user.logger.ins_logger_logs(
    p_unit_name => 'error_handler' ,
    p_scope => 'apx_util.error_handler' ,
    p_logger_level => logger.g_error,
    p_extra => p_error.component.name
              ||'~'||p_error.component.type
              ||'~'||p_error.message,
    p_text => 'apx_util.error_handler()',
    p_call_stack => dbms_utility.format_call_stack,
    p_line_no => null,
    po_id => l_reference_id
);

Now the user has some context to report. I figured the wording could be softened a little, too ;p

Standard error message, with reference

So if we were to execute the following SQL, we could see further details.

select time_stamp, module, client_identifier, extra
from logger_user.logger_logs
where id = 12292314;

The log message prior to this ID may also help to provide clues as to the problem.

SQL results, slightly redacted

I also wanted to add some convenience to the developer, so I added this same information to the error popup, but only when an active session is also present within the App Builder (or some special privilege present).

Immediate context for the developer

This is toggled by checking a built in substitution string (or my application item). I note this built-in was only documented from 18.1, but I believe it has been present for a while. It's certainly returns a value in 5.1.

l_result.message := 'We had a problem completing this request. '||
    'Please contact IT Support'||
    ' for further investigation. Reference: '||l_reference_id
    ||case when v('F_SEC_DEV') = 'Y' -- anyone with privilege
    -- or has builder open (from oos_util_apex.is_developer)
    or coalesce(apex_application.g_edit_cookie_session_id
               ,v('APP_BUILDER_SESSION')) is not null then
      ' Dev only: '||p_error.component.name
      ||' ~ '||p_error.component.type
      ||' ~ '||p_error.message
    end;

This brightened the day of some of my colleagues.