Capturing Custom Error Messages from Database Triggers
Working with a large database we have over 1000 database triggers and we have a lot of business logic and functionality built into database objects. Included in these objects is validation which displays error messages we have defined. The validation generates a code which is then picked up in Oracle Forms and converted to a user friendly message which is displayed to the user. One of the main reasons for having this validation in the database rather than the front-end is because we have a number of forms that access the same tables, so instead of adding the validation to each of these forms we add it to the trigger instead.
Now that we’re using APEX, the standard error page template that APEX uses isn’t sufficient. It means nothing to the end user to see ‘ORA−20101: SSL-12345 ORA−06512: at “OWNER.COST_BTRG”, line 1393″‘. So how can we use APEX to convert this into more user-friendly information that users are used to seeing?
To illustrate this further consider the following piece of code:
begin select 'Y' into v_authorised from payments where cost_identifier = :new.identifier and status = 'A'; exception when no_data_found then v_authorised := 'N'; when too_many_rows then v_authorised := 'Y'; end; if v_authorised = 'Y' then raise_application_error(-20101, 'SSL-12345'); end if;
This is a check in a trigger on the Costs table. When updating, if there are any authorised payment records then we want to raise an error so that the user is informed that the cost cannot be updated. In Oracle Forms we look up SSL-12345 in a table of error messages and see that the message related to this code is ‘Authorised payments exist – Cost cannot be updated and this is displayed to the user.
APEX uses a built in error page template to display messages to the end user. However, we can achieve similar functionality to that described in our Oracle Forms product by customising this error page template and redirecting the user to an alternative page to the one APEX wants to display. In Shared Components > Definition > Template Defaults check which template is used as the error page in your theme. Now in Shared Components > Templates select that page template and insert the following code in the Error Page Template Control section:
Once the error text is determined, the user is redirected to page 200 and item P200_ERROR_MESSAGE is set to the error text. Page 200 can be customised how you like to display whatever information you feel is appropriate. In my case I’ve created a simple page with one HTML region and two items. Item P200_ERROR_MESSAGE holds the error message and item P200_ERROR_TEXT is conditional and populated from a select statement. The condition on this item is to only display this field if P200_ERROR_MESSAGE is like ‘SSL’ or ‘QMS’ and the select statement is:
SELECT error_text FROM error_messages WHERE error_code = :P200_ERROR_MESSAGE;
This method ensures that where an unexpected error occurs the full error message is displayed on the screen, but where a custom error message is fired from the trigger, a user-friendly message is displayed instead. One thing to note when using this method, the error page needs to have a back button. For Firefox users this needs to be history-1 and for IE this needs to be history-2.
Using a custom error page allows you to easily add further functionality, such as also hiding the full Oracle error message and emailing this to a system administrator along with other bits of useful information to help diagnose the problem.