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?
- 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.
- 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
- Will not show any APEX internal error messages (like invalid SQL statement, …) and instead display a generic error message
- Display all process errors “Inline in Notification”
- 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.
- Remove the technical information ORA-xxxx from Oracle errors or errors raised with RAISE_APPLICATION_ERROR in triggers or packages.
- Automatically set the associated page item based on the constraint meta data if a constraint violation occurs.
Let’s get started!
- 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 );
- Load the error handling stored function example below
- 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.
- Enter messages for some constraints into the new CONSTRAINT_LOOKUP table. If you just want to try it out with DEPT
- add a unique key to the DNAME column of DEPT
alter table DEPT add constraint DEPT_DNAME_UK unique (DNAME);
- 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!');
- add a unique key to the DNAME column of DEPT
- 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;