Oracle Apex: Error Handling Function Example

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:

Using custom error handling function in Oracle Apex.

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
  l_result          apex_error.t_error_result;
  l_reference_id    NUMBER;
  l_constraint_name VARCHAR2(255);
  l_column_name     VARCHAR2(255);
    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;
      l_result.display_location := CASE
                                     WHEN l_result.display_location =
                                          apex_error.c_on_error_page THEN
                                     ELSE l_result.display_location

      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)

              SELECT column_name
              INTO   l_column_name
              FROM   user_cons_columns
              WHERE  constraint_name = l_constraint_name;
              WHEN OTHERS THEN
          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.

Oracle Apex custom error message.

This Post Has 2 Comments

  1. Tyler

    Hi, Vinish.

    I appreciate the detailed examples you provide in all of your posts. You have come to my rescue a couple of times. One question, where exactly am I applying this error_handling function? I see where you applied the name of the function but I am not sure where this function goes -- do I create a validation? Or maybe I need to create the function in the form of a package in JDeveloper?

  2. Pravveen

    Hi , i’m having report 1 with intractive report through sql query and and i have added checkbox in the report1 and i have button on report1 based on checked values data need to display on report2 , but here the problem is while submiting the page the checked values as not there and i want the checked values as ticked after submitting also how can i do this (i have done through process i created collection and then store value and i ajax i used to process and return and in js

    var oDBGet = new htmldb_Get(null, &APP_ID., 'APPLICATION_PROCESS=get_checked_boxes', &APP_PAGE_ID.);
       var arrBoxes = jQuery.parseJSON(oDBGet.get());
              function( intIndex, objValue ){

    I want to assign to a checked value how can i do ? Please help me

Comments are closed.