Friday, 7 March 2008

Messages from the database

My new employer asked me to show messages from triggers more user friendly than they are shown by default by Apex, i.e. in the separate error screen. I had previously been investigating this, but I wondered there should be someone with a solution out there in the Apex user's universe, represented by the Apex forum on OTN. So I posted my question, and got no response! This amazed me. Is there no one who came across this problem before?Lot's of organizations have databases with triggers to ensure data integrity, and sure some of them want to access their databases with Apex. Are their users content with the default error screen, or even a customized screen with only an error message and a Back button to return to the screen (having to memorize the error message).

I just saw there was a reply referencing Patrcik Wolff's blog and his impressive solution for error checking in tabular forms. But I just want a simple solution without extra programming. So I took some of Patrick ideas together with some other code snippets to extract the relevant part of the error message form the whole error stack and started off...

Developing Javascript I think is a tedious process, I much rather work with PL/SQL, which can be easily debugged and traced. So I always work step by step, testing a lot along the way to make sure that everything keeps working. And at the end I had a working generic solution, which shows trigger error messages as notifications without any additional coding in Apex.

Apex: Show Database Error Message in calling formThe purpose is to show a neat error message in the notification area of the calling form.Default Apex show the whole error stack raised by a DB trigger in a separate error page.With acknowledgement to Patrick Wolf, I lend parts of his ApexLIB code to create this solution.

Assumptions

The error message is raised from a DB trigger using RAISE_APPLICATION_ERROR(-20000,’errormessagetext’).

The relevant part of the error stack is contained within the strings ‘ORA-20000’ and the next ‘ORA-‘-string, i.e. if the error stack is ‘ORA-20000 Value should not be null ORA-6502 …’, than the relevant string is ‘Value should not be null’ .

Cookies are enabled on the browser of the user

ExplanationThe solution relies heavily on the use of Javascript. On the template of the error page Javascript is added to identify the error stack and to extract the relevant error message. This message is written to a cookie, and then the control is passed back to the calling page (equal to pushing the Back button).In the calling page a Javascript onLoad process is added. This process determines whether an error message has been written to a cookie. If so the error message is formatted as an error and written to the notification area.ImplementationThe solution redefines two template pages, the two level tab (default page template) and the one level tab (error page template).Javascript is added to implement the solution. This Javascript is contained in a small library errorHandling.js:

This code is loaded as a static file in Application Express (no application associated).

In both templates a reference to this code file is added in the Definition Header section.

This library is called from the two level tab page template to show the error message (if any) in het onLoad event of the body tag. Therefore the function show_message() is called in the onLoad event of the page. This function reads the cookie and displays the error messages stored in the cookie.

…<body onload="javascript:show_message();">#FORM_OPEN#…

This code checks whether a message has been written to a cookie and if found displays the message in the notification area.

The call to processErrorText() looks for the error message, extracts the relevant part of it (between ‘ORA-20000’ and the next ‘ORA-‘), writes it to a cookie and returns to the previous screen.The link to the previous page is added should an error in the Javascript occur. It provides the user with a path back to the application.

With these actions taken, the error messages issued from triggers are shown in the notification area of the form the user has entered his data in.The need for database driven messagingIn some cases the need exists to process error messages in the database before presenting them to the user. This is the case, when the triggers return message codes, associated to error messages in a message table.

This can be done by using a special Error Message Processing page in Apex.The error message page extracts the error message, redirecting to the Error Message Processing page with the error message as a parameter. In the EMP page a PL/SQL function can be called with the message as a parameter. This function returns the right message, which is written to a cookie using dynamically generated Javascript from PL/SQL. Than the contol is given back to the calling form.

The redirect is implemented by location.replace, so that the error message page does not exist within the browsing history. The normal history(-1) will return to the calling page.

Implementation of database driven messaging

The solution redefines two template pages, the two level tab (default page template) and the one level tab (error page template).Javascript is added to implement the solution. This Javascript is contained in a small library errorHandling.js already listed in a previous paragraph.

The call to processErrorText() looks for the error message, extracts the relevant part of it (between ‘ORA-20000’ and the next ‘ORA-‘), writes it to a cookie.Then the EPM-page (500) is called with the extracted message as parameter.

The link to the EPM page and the previous page is added should an error in the Javascript occur. It provides the user with a path back to the application.

2 comments:

Anonymous
said...

Hi Dick,

Thanks for the script. I am finding it difficult to locate the javascript function processErrorText() any where in your example. Could you please let me know the code inside this function to parse the error text and create a cookie?

Don't worry, many people are upset by the fact that Apex doesn't let us handle errors in an easy way !Your solution sounds good, however like Hari, I don't find the js function processErrorText() in your demo?