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

Oracle Apex custom error message.

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

2 Comments

  1. 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. 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());
    
       $.each(arrBoxes,
              function( intIndex, objValue ){
                 $("input[name='f01'][value='"+objValue+"']").attr("checked","checked");
              });
    

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

Comments are closed.