The most valuable new feature in APEX 4.1 is definitely the possibility to implement an error handling function. It basically allows you to execute a PL/SQL function every time an error occurs in your application. An error handling function accepts one parameter of type apex_error.t_error which holds all relevant error information. The apex_error.t_error_result type is used as the return type and contains information on how the error message should be presented to the end user.

I won’t get into too much detail on how an error handling function should look like since all of this is well-documented. Then there is also Patrick Wolf who wrote a great article regarding the why and how of error handling.

locating the error handling function in application properties
locating the error handling function in application properties

A good starting point to build your own function is the template code which can be found here. The purpose of this article is to provide and explain a hands-on example of an error handling function that allows you to take advantage of following possibilities:

  • Error logging
  • Define an error message its display location
  • Detect severe internal exceptions and modify the error message
  • Map a constraint violation to a specific error message
  • E-mail notification when an unexpected error occurs

Getting started

The error handling function is being executed for all sorts of errors. I make a distinction between expected and unexpected errors. The occurrence of an unexpected error is vital information for a developer since it reveals serious shortcomings of the application logic or validations. I opted to only log this kind of errors, but nothing stops you from logging expected errors as well.

You first need a table to hold all logged errors. My ERROR_LOG table contains all the information on the encountered error (retrieved from the incoming parameter of type t_error), plus some general information derived from substitution strings, and a time stamp.

the "error_log" table
the ERROR_LOG table

P.S. I included the ID of the application in which the error occurred because I make use of one centralized error handling function for multiple applications. This can be easily achieved by creating private synonyms that point to the database object in which the error handling function resides.

Now let’s dig in some code. We start with the header section and first part of the error handling function:

FUNCTION fun_catch_error(p_error IN apex_error.t_error)
RETURN apex_error.t_error_result AS
  l_result apex_error.t_error_result;
  l_reference_id error_log.error_id%TYPE;
  l_constraint_name constraint_lookup.constraint_name%TYPE;
BEGIN
  l_result := apex_error.init_error_result(p_error => p_error);
  ...

The function header simply accepts the error as a parameter and returns the t_error_result type. Some comments on the variables we’re using:

  • l_result is our return variable
  • l_reference_id will be assigned a primary key generated on an insert of the ERROR_LOG table
  • l_constraint_name will be assigned the name of the violated constraint, if applicable

In the first line of code we initialize the l_result variable with the values stored in our incoming parameter p_error. For example, if an end user violates a unique constraint then l_result.message will be initialized by a value that looks like this: “ORA-00001: unique constraint (SCHEMA.CONSTRAINT_NAME) violated”.

Logging an error

I wrote a private function fun_log_error to perform an insert on the ERROR_LOG table. Please note that this function must be defined as an autonomous transaction. An autonomous transaction allows you to leave the context of the calling transaction, in this case our error handling function, so you can start a new independent transaction to perform the insert. This is absolutely mandatory since the calling transaction will always be rolled back every time we hit an unexpected error.

FUNCTION fun_log_error(p_error IN apex_error.t_error)
RETURN error_log.error_id%TYPE AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_error_id error_log.error_id%TYPE;
  l_is_internal_error error_log.is_internal_error%TYPE;
BEGIN
  IF p_error.is_internal_error THEN
    l_is_internal_error := 'Y';
  ELSE
    l_is_internal_error := 'N';
  END IF;
  --
  INSERT
    INTO error_log (message, additional_info, display_location, association_type
                  , page_item_name, region_id, column_alias, row_num
                  , is_internal_error, apex_error_code, ora_sqlcode, ora_sqlerrm
                  , error_backtrace, component_type, component_id, component_name
                  -- substitution strings
                  , application_id, app_page_id, app_user, browser_language
                  , timestamp)
  VALUES (p_error.message, p_error.additional_info, p_error.display_location, p_error.association_type
        , p_error.page_item_name, p_error.region_id, p_error.column_alias, p_error.row_num
        , l_is_internal_error, p_error.apex_error_code, p_error.ora_sqlcode, p_error.ora_sqlerrm
        , p_error.error_backtrace, p_error.component.type, p_error.component.id, p_error.component.name
        -- substitution strings
        , v('APP_ID'), v('APP_PAGE_ID'), v('APP_USER'), v('BROWSER_LANGUAGE')
        , SYSDATE)
  RETURNING error_id
       INTO l_error_id;
  COMMIT;
  --
  RETURN l_error_id;
END fun_log_error;

Unexpected errors

Let’s extend the error handling function with some logic to capture unexpected errors. To do so, construct an IF statement which is made up of two conditions:

  1. the encountered error is recognized as an internal error
  2. ignore errors related to authorization verification
IF p_error.is_internal_error AND p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED' THEN
  -- log the error and get the generated PK
  l_reference_id := fun_log_error(p_error => p_error);
  -- notify app. admin. via e-mail
  pro_send_email(p_reference_id => l_reference_id);
  -- display a generic error message
  l_result.message := fun_build_error_message(l_reference_id);
  l_result.additional_info := NULL;
  -- set the display location
  l_result.display_location := apex_error.c_inline_in_notification;
ELSE
  ...

No rocket science here. I first log the error by calling the fun_log_error function, then I send an e-mail to the application administrator (see below), and finally I specify how the error should be shown to the end user. You might be interested in the code for pro_send_email and fun_build_error_message:

PROCEDURE pro_send_email(p_reference_id IN error_log.error_id%TYPE)
AS
BEGIN
  apex_mail.send(p_to => 'name@domain.com'
               , p_cc => NULL
               , p_from => 'name@domain.com'
               , p_subj => 'Unexpected error in app. ID ' || v('APP_ID')
               , p_body => 'Please use an e-mail client that supports HTML messages.'
               , p_body_html =>
  '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
   <html xmlns="http://www.w3.org/1999/xhtml">
     <head>
       <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
       <meta name="viewport" content="width=device-width, initial-scale=1.0" />
       <style type="text/css">
         body {
           background-color:#fff;
           color:#000;
           font-family:Arial, Helvetica, sans-serif;
           font-size:12px;
         }
       </style>
     </head>
     <body>
       <p>An unexpected error has occurred in application ID ' || v('APP_ID') || ', page ID ' || v('APP_PAGE_ID') || '.<br/>
       Please refer to the error log for detailed information on incident <b>#' || p_reference_id || '</b>.</p>
     </body>
   </html>');
  --
  apex_mail.push_queue();
END pro_send_email;

FUNCTION fun_build_gen_error_message(p_reference_id IN error_log.error_id%TYPE)
RETURN VARCHAR2 AS
BEGIN
  RETURN 'An unexpected error has occurred. ' ||
         'Your application administrator has been notified of the error details and ' ||
         'will contact you shortly about incident <b>#' || p_reference_id || '</b>.';
END fun_build_gen_error_message;

Constraint violations

Another interesting feature of the error handling function is the ability to intercept database constraint violations. All you need is a lookup table in which you couple a constraint name with an error message. You can also specify the page item name or tabular form column that may cause the constraint violation. Having this information makes it possible to append the error message with a link that focuses the page item when clicked.

the "constraint_lookup" table
the CONSTRAINT_LOOKUP table

In our code, p_error.ora_sqlcode contains the error code returned by the Oracle database. You can filter out constraint violations by looking after the following ORA-errors:

  • ORA-00001: unique constraint violated
  • ORA-02091: transaction rolled back
  • ORA-02290: check constraint violated
  • ORA-02291: integrity constraint violated – parent key not found
  • ORA-02292: integrity constraint violated – child record found
IF p_error.ora_sqlcode IN (-1, -2091, -2290, -2291, -2292) THEN
  -- get the constraint name
  l_constraint_name := apex_error.extract_constraint_name(p_error => p_error);
  --
  BEGIN
    SELECT error_message
         , page_item_name
         , tab_form_col_alias
      INTO l_result.message
         , l_result.page_item_name
         , l_result.column_alias
      FROM constraint_lookup
     WHERE upper(constraint_name) = upper(l_constraint_name);
  EXCEPTION
    -- not every constraint has to be in our lookup table
    WHEN no_data_found THEN
      l_reference_id := fun_log_error(p_error => p_error);
      -- notify app. admin. via e-mail
      pro_send_email(p_reference_id => l_reference_id);
      -- display a generic error message
      l_result.message := fun_build_gen_error_message(l_reference_id);
  END;
  l_result.additional_info := NULL;
  -- set the display location
  l_result.display_location := apex_error.c_inline_in_notification;
END IF;
...

The built-in function apex_error.extract_constraint_name gets you the name of the violated constraint. This constraint name enables you to perform a SELECT … INTO … on the CONSTRAINT_LOOKUP table to fetch the corresponding error message data. However, it is possible that the constraint name is not present in the lookup table, hence the NO_DATA_FOUND exception handler. From that moment I treat the error as an unexpected error.

sample data "constraint_lookup" table
sample data CONSTRAINT_LOOKUP table

Finish it off

Complete the error handling function by including the code snippet below. It consists of two IF blocks to enhance certain error situations. More information can be found in the documentation.

  • get_first_ora_error_text returns the first ORA-error message text stored in p_error.ora_sqlerrm
  • auto_set_associated_item tries to automatically set the associated page item or tabular form column
    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 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 fun_catch_error;