Oracle Apex: Error Handling Function Example

In this tutorial, I am giving an example to handle errors at the page level in Oracle Apex.

We handle the errors in Processing and in Validations, etc, but what if an unhandled error occurred?

For example, a unique constraint error (ORA-00001: unique constraint violated). To handle any kind of error, Oracle Apex provides APEX_ERROR package, by using it we can write a custom error handling function and can specify it at the page level. As shown in the below image:

The function must match the following interface:

function <name of function> (
    p_error in apex_error.t_error )
    return apex_error.t_error_result

Oracle Apex Error Handling Function Example

Create the following database function in your schema:

CREATE OR replace FUNCTION Error_handling (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);
  l_column_name     VARCHAR2(255);
BEGIN
    l_result := apex_error.Init_error_result (p_error => p_error);

    IF p_error.is_internal_error THEN
      IF NOT p_error.is_common_runtime_error THEN
        l_result.message :=
        'An unexpected internal application error has occurred. '
        ||
        'Please get in contact with your system administrator and provide '
                            || 'reference# '
                            ||To_char(l_reference_id, '999G999G999G990')
                            || ' for further investigation.';

        l_result.additional_info := NULL;
      END IF;
    ELSE
      l_result.display_location := CASE
                                     WHEN l_result.display_location =
                                          apex_error.c_on_error_page THEN
                                     apex_error.c_inline_in_notification
                                     ELSE l_result.display_location
                                   END;

      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;

      IF p_error.ora_sqlcode = -1 THEN
        IF l_result.page_item_name IS NULL
           AND l_result.column_alias IS NULL THEN
          l_constraint_name := apex_error.Extract_constraint_name (
                               p_error => p_error)
          ;

          BEGIN
              SELECT column_name
              INTO   l_column_name
              FROM   user_cons_columns
              WHERE  constraint_name = l_constraint_name;
          EXCEPTION
              WHEN OTHERS THEN
                NULL;
          END;
        ELSE
          l_column_name := Nvl(l_result.page_item_name, l_result.column_alias);
        END IF;

        l_result.message := 'Field '
                            || Initcap(Replace(l_column_name, '_', ' '))
                            || ' must be unique.';
      END IF;
    END IF;

    RETURN l_result;
END error_handling; 

To learn more about the APEX_ERROR API package, check the APEX_ERROR help document.

Now if an error will occur, it will show the error message customized by you in the above function. You can also log the errors in a table using the autonomous transaction.