The Early Adopter release of Oracle APEX 4.1 contains five new enhancements in the Error Handling area. In the next few blog postings I will highlight those. This first article features “Error Handling Function”

A long requested feature and something I also always wanted to have before I joined the APEX development team. Guess what, that’s why I always wanted to implement that when I joined the team and in 4.1 I finally had the opportunity to do it. So hopefully you find it useful what’s available now.

What can you do and when would you use this new error handling hook?

This feature can be used if a developer wants to have full control what is happening if an error occurs in an APEX application. This includes errors raised by validation, process, … and all errors raised by the Application Express engine itself. It can be used to just log the error, but it can also be used to modify the error message text, define where it should be displayed (Inline with Field and in Notification, Inline with Field, Inline in Notification and On Error Page) and it’s also possible to specify which page item/tabular form column should be highlighted. We have written a number of helper function to make your life as easy as possible. An example would be to extract constraint names out of error messages (working with all languages) so that you can use them for a lookup in your own error message table. Or a helper procedure to automatically detect the affected page item if a constraint error occurred. But first let’s summaries again why to use that feature.

Use cases why someone would you use this feature

• To log all errors which occur in an APEX application
• Map errors raised by the Oracle Database (like “Unique constraint XYZ violated) to friendlier error messages understandable by the business user
• To display all errors “Inline in Notification” without having to change the new “Error message display location” for all processes of an existing application
• Increase application security by showing a generic error message for internal APEX errors instead of the original message which most of the time includes the ORA error message which might expose sensitive information about the structure of the application/database. Such a generic error message could be “An unexpected internal application error has occurred. Please get in contact with XYZ and provide reference# 123 for further investigation.”
• Because it’s a cool feature

So how would you use this feature?

1. You have to create a function in a package or a stored function in the database with the interface outlined in the item level help of “Error Handling Function”. Or just see the example below.
2. The name of the created function has to be specified in the “Error Handling Function” attribute on page or application level. Specifying a function on page level will overwrite the setting on application level.

But let’s get started with an example

Our goal is to write an error handler which

1. Will not show any APEX internal error messages (like invalid SQL statement, …) and instead display a generic error message
2. Display all process errors “Inline in Notification”
3. Avoid of having to create APEX validations for table constraints to get error messages understandable by business users. An example would be if there is a unique key on the username, it’s not necessary to create an EXIST/not EXISTS validation. Instead we will have a table which contains the constraint name and the error message which should be displayed if that constraint fails.
4. Remove the technical information ORA-xxxx from Oracle errors or errors raised with RAISE_APPLICATION_ERROR in triggers or packages.
5. Automatically set the associated page item based on the constraint meta data if a constraint violation occurs.

Let’s get started!

1. Create a new super simple table with the following DDL which will hold the user friendly error message if a constraint is violated

   create table CONSTRAINT_LOOKUP
   (
     CONSTRAINT_NAME VARCHAR2(30)   primary key,
     MESSAGE         VARCHAR2(4000) not null
   );
   

2. Load the error handling stored function example below
3. Go to your application, click “Edit Application Properties” and enter apex_error_handling_example into the “Error Handling Function” attribute of the “Error Handling” region.
4. Enter messages for some constraints into the new CONSTRAINT_LOOKUP table. If you just want to try it out with DEPT
   1. add a unique key to the DNAME column of DEPT

      alter table DEPT
      add constraint DEPT_DNAME_UK unique (DNAME);
      

   2. Insert a user friendly error message into our CONSTRAINT_LOOKUP table

      insert into CONSTRAINT_LOOKUP
        (CONSTRAINT_NAME, MESSAGE)
      values
        ('DEPT_DNAME_UK', 'Name is already in use!');
      

5. Run your application and test the above use cases.

That’s it for now, I hope you like the new possibilities and any feedback is always welcome!

I will try to provide the API documentation of the new apex_error package in one of the following blog postings, but for now please have a look at the inline comment of the example or have a look at ALL_SOURCES with the following SQL statement

select text
  from all_source
 where name='WWV_FLOW_ERROR'
   and type='PACKAGE'
 order by line;

Example of an error handling function

create or replace function apex_error_handling_example (
    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);
begin
    l_result := apex_error.init_error_result (
                    p_error => p_error );

    -- If it's an internal error raised by APEX, like an invalid statement or
    -- code which can't be executed, the error text might contain security sensitive
    -- information. To avoid this security problem we can rewrite the error to
    -- a generic error message and log the original error message for further
    -- investigation by the help desk.
    if p_error.is_internal_error then
        -- Access Denied errors raised by application or page authorization should
        -- still show up with the original error message
        if p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED' then
            -- log error for example with an autonomous transaction and return
            -- l_reference_id as reference#
            -- l_reference_id := log_error (
            --                       p_error => p_error );
            --
            
            -- Change the message to the generic error message which doesn't expose
            -- any sensitive information.
            l_result.message         := 'An unexpected internal application error has occurred. '||
                                        'Please get in contact with XXX and provide '||
                                        'reference# '||to_char(l_reference_id, '999G999G999G990')||
                                        ' for further investigation.';
            l_result.additional_info := null;
        end if;
    else
        -- Always show the error as inline error
        -- Note: If you have created manual tabular forms (using the package
        --       apex_item/htmldb_item in the SQL statement) you should still
        --       use "On error page" on that pages to avoid loosing entered data
        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 it's a constraint violation like
        --
        --   -) ORA-00001: unique constraint violated
        --   -) ORA-02091: transaction rolled back (-> can hide a deferred constraint)
        --   -) ORA-02290: check constraint violated
        --   -) ORA-02291: integrity constraint violated - parent key not found
        --   -) ORA-02292: integrity constraint violated - child record found
        --
        -- we try to get a friendly error message from our constraint lookup configuration.
        -- If we don't find the constraint in our lookup table we fallback to
        -- the original ORA error message.
        if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
            l_constraint_name := apex_error.extract_constraint_name (
                                     p_error => p_error );
        
            begin
                select message
                  into l_result.message
                  from constraint_lookup
                 where constraint_name = l_constraint_name;
            exception when no_data_found then null; -- not every constraint has to be in our lookup table
            end;
        end if;
        
        -- If an ORA error has been raised, for example a raise_application_error(-20xxx, '...')
        -- in a table trigger or in a PL/SQL package called by a process and we
        -- haven't found the error in our lookup table, then we just want to see
        -- the actual error text and not the full error stack with all the ORA error numbers.
        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 no associated page item/tabular form column has been set, we can use
        -- apex_error.auto_set_associated_item to automatically guess the affected
        -- error field by examine the ORA error for constraint names or column names.
        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 apex_error_handling_example;