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:

<script language="javascript">
 errorElements = document.getElementsByTagName("div");
 if (errorElements.length > 0 ) {
  errorText = errorElements[2].innerHTML;
  search_term = "SSL";
  str_check = errorText.indexOf(search_term);
  if (str_check==-1) {
    search_term = "QMS";
    str_check = errorText.indexOf(search_term);
  }
  if (str_check > 0) {
    errorText = errorText.substr(str_check);
    errorText = errorText.substr(0,errorText.indexOf("ORA")-1);
  }
  else {
    errorText = errorText.replace(/:/g,"|");
  }
  url_location = "f?p=&APP_ID.:200:&APP_SESSION.::NO::P200_ERROR_MESSAGE:"
+errorText;
  window.location = url_location;
 }
</script> 

This code will fire whenever an error is generated in APEX. The error page that is displayed has one div which contains the text of the error that is displayed on the screen. This javascript picks up the error message text and searches for first SSL and then QMS in the text. I know that all the error messages generated from the triggers start with one of those strings. If one of those strings is found then we grab only the text from the start of the searched text to the ORA error, this ensures that the we only pick up the error code we are interested in e.g. SSL-12345. If we don’t find a custom error code then we pick up the full error message so that it can be displayed instead. We need to replace colons with another character, in this case I’ve used pipe, as APEX uses colons to separate items and values. If we don’t replace the colons the error text is truncated.

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.

Sara

Sara Blair is a freelance Oracle developer with over 10 years Oracle experience. She has been working with Oracle APEX for nearly 4 years since htmldb 1.5, and is available to hire through Silvercore Solutions.

7 Responses to “Capturing Custom Error Messages from Database Triggers”

  • Patrick Wolf says:

    Nice tip! What I have read the next version of APEX will bring some improvements in that area so that it’s not necessary to create that kind of hacks.

    Patrick
    PS: BTW is it by intention that your name is missing in the About page?

  • Sara says:

    Hi Patrick

    If there are improvements in this area then they’ll be welcomed! I hate using hacks like this, but luckily this has been the only major one we’ve had to resort to using.

    It was by intention that by name was missing. However the about page is probably a bit out of date, so needs revising. I’ll look to do that this week.

    Regards
    Sara

  • This is almost the same solution I wrote 4 years ago.

    See my description in the Apex forum thread:
    http://forums.oracle.com/forums/thread.jspa?messageID=820808&#820808820808
    and the ‘promise’ of Sergio.

    It’s a pity that for such a long time no neat solution has been made for error handling.

    Learco

  • Steve LaBorde says:

    I love your solution, but instead of P200 showing the error message, I am getting:

    I have a unique constraint in the DB and want to return a better message that what APEX gives.

    What have I done wrong?

    • Sara Blair says:

      Hi Steve

      Sorry but I didn’t receive the error you’re getting. If it contains an Oracle error could you please put a space between ORA and the dash (e.g. ORA -20001) as WordPress cannot display this for some reason.

      Thanks
      Sara

  • Steve LaBorde says:

    I will try again.

    span id=”t13Customize”

    it has some begin and end tabs with this

    • Sara Blair says:

      Hi Steve

      Could you take a look at the HTML for this field, by searching for the item name in the page source, and let me know what is being produced for this field. It sounds like the item HTML isn’t being produced correctly (possibly due to the error message being produced), but as I haven’t come across this problem before I can’t be sure.

      Thanks
      Sara

Comments can no longer be submitted.