Custom Error Handling in APEX

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example:

If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon:

ORA-00001 Unique constraint violated

There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example:

The record you tried to create already exists. Please try again

In order to achieve this in APEX, the process is relativly simple.

Set up your Custom Error Page

1. Create a new page with the following attributes:

  • Page type: Blank Page
  • Page Number: your choice (for this example I used page 500)
  • Page Alias: ERRPAGE
  • Name: Custom Error Page
  • Title: Custom Error Page

2. Create a new HTML region on the page with the following attributes:

  • Title: Custom Error Page
  • Region Template: Wizard Region
  • Region Source: There was an error processing your request.<br><br>&P500_ERR_MSG.

3. Create the following hidden items (replace 500 with the page number of your error page):

  • P500_ERR_MSG
  • P500_GOTO_PAGE
  • P500_GOTO_PAGE_CC
  • P500_GOTO_PAGE_REQ
  • P500_GOTO_PAGE_ITEMS
  • P500_GOTO_PAGE_VALUES
  • P500_GOTO_PAGE_RP
  • P500_GOTO_PAGE_LINK_DIS

4. Create a button in the Custom Error Page HTML Region with the following attributes:

  • Button Name: OK
  • Label: &P500_GOTO_PAGE_LINK_DIS.
  • Button Type: HTML
  • Action: Redirect to URL without submitting page
  • Target is a: Page in this Application
  • Page: &P500_GOTO_PAGE.
  • Request: &P500_GOTO_PAGE_REQ.
  • Clear Cache: &P500_GOTO_PAGE_CC.&P500_GOTO_PAGE_RP.
  • Set these items: &P500_GOTO_PAGE_ITEMS.
  • With these values: &P500_GOTO_PAGE_VALUES.

Create the Error Handling Package


CREATE OR REPLACE PACKAGE error_pkg
AS
PROCEDURE error_redirect (
pv_custom_msg IN VARCHAR2,
pv_goto_page IN NUMBER DEFAULT v ('APP_PAGE_ID'),
pv_goto_page_cc IN VARCHAR2 DEFAULT NULL,
pv_goto_page_req IN VARCHAR2 DEFAULT NULL,
pv_goto_page_items IN VARCHAR2 DEFAULT NULL,
pv_goto_page_values IN VARCHAR2 DEFAULT NULL,
pv_goto_page_rp IN BOOLEAN DEFAULT FALSE,
pv_goto_page_link_dis IN VARCHAR2 DEFAULT 'Ok'

);
END error_pkg;
/


CREATE OR REPLACE PACKAGE BODY error_pkg
AS
PROCEDURE error_redirect (
pv_custom_msg IN VARCHAR2,
pv_goto_page IN NUMBER DEFAULT v ('APP_PAGE_ID'),
pv_goto_page_cc IN VARCHAR2 DEFAULT NULL,
pv_goto_page_req IN VARCHAR2 DEFAULT NULL,
pv_goto_page_items IN VARCHAR2 DEFAULT NULL,
pv_goto_page_values IN VARCHAR2 DEFAULT NULL,
pv_goto_page_rp IN BOOLEAN DEFAULT FALSE,
pv_goto_page_link_dis IN VARCHAR2 DEFAULT 'Ok'

)
IS
BEGIN
wwv_flow.g_unrecoverable_error := TRUE; -- Quit the APEX Engine

HTP.init;-- Initiate the HTML Header incase it was closed

-- Set session of items on the error page
apex_util.set_session_state ('P500_ERR_MSG', pv_custom_msg);
apex_util.set_session_state ('P500_GOTO_PAGE', pv_goto_page);
apex_util.set_session_state ('P500_GOTO_PAGE_CC', pv_goto_page_cc);
apex_util.set_session_state ('P500_GOTO_PAGE_REQ', pv_goto_page_req);
apex_util.set_session_state ('P500_GOTO_PAGE_ITEMS', pv_goto_page_items);
apex_util.set_session_state ('P500_GOTO_PAGE_VALUES', pv_goto_page_values);
apex_util.set_session_state
('P500_GOTO_PAGE_LINK_DIS', pv_goto_page_link_dis);


-- Resets the pagination scheme for the page redirected to
IF pv_goto_page_rp
THEN
apex_util.set_session_state ('P500_GOTO_PAGE_RP', ',RP');
END IF;

-- Redirect the URL to the custom error page
OWA_UTIL.redirect_url ( 'f?p='
|| v ('APP_ID')
|| ':ERRPAGE:'
|| v ('APP_SESSION')
);

END error_redirect;
END error_pkg;
/

You will need to change the page ID of the items that are being set in session in the error_redirect procedure, to the ID of your error page.



Re-Write your Exception Handlers



EXCEPTION
WHEN CUSTOM_EXCEPTION1
THEN
error_pkg.error_redirect
(pv_custom_msg => 'Your Custom Error Message Goes Here',
pv_goto_page => v ('APP_PAGE_ID'));
WHEN OTHERS
THEN
IF SQLCODE = -6502
THEN
error_pkg.error_redirect
(pv_custom_msg => 'A Non Numeric Character was Encountered',
pv_goto_page => v ('APP_PAGE_ID'),
pv_goto_page_cc => '1,500');
ELSE
error_pkg.error_redirect
(pv_custom_msg => 'Please contact Support for further assistance',
pv_goto_page => v ('APP_PAGE_ID'),
pv_goto_page_cc => '1,500',
pv_goto_page_rp => TRUE);
END IF;
END;

The only parameter that is required when calling the error_pkg.error_redirect procedure is pv_custom_msg

All the other parameters let you build a new URL that will be redirected to once the user presses the [OK] button on the Custom Error Page. These parameters are bound by the same rules as constructing a URL anywhere else in the APEX application. Please see: Understanding URL Syntax

The only parameter that behaves slightly differently to the documentation is pv_goto_page_rp
This is a Boolean switch where TRUE will reset the pagination on the page redirected to after the Custom Error Page.

The pv_goto_page_link_dis parameter allows you to set the text of the button on the Custom Error Page.

You now should have a generic approach to providing the user with a friendly error message on your own Custom Error Page in you APEX applications.

There is an example of this in operation here: http://apex.oracle.com/pls/otn/f?p=40923:1

Comments

Patrick Wolf said…
Hi Duncan,

by using a "Redirect to URL without submitting page" for your "Ok" button, aren't you loosing all the entered values in case of a tabular form?

Patrick
runcsmeduncs said…
Hi Patrick

I agree. This was only meant to be a bare bones example of how to deal with PL/SQL exceptions. I had some fairly good exception handlers throughout my app but I needed a way to hide oracle errors from the user when an exception fell into the when others. This is ideally how i saw it being used on the very basic level but it can obviously be extended to handle known exceptions as well. There is nothing to stop you adding to the logic to set in session any number of collections for you tabular forms etc.

If your requirement however was to carry out some advanced validation / error capture, I would always recommend that people use your APEX_LIB framework (which is really cool by the way).

Hope all that makes sense

Duncan
Patrick Wolf said…
Makes sense :-)

Great that you like the framework! Did you already have the chance to use it in one of your applications?

Patrick
Duncan said…
Yeah. We are building a new app now and have installed the APEX_LIB framework onto the server so all the apps can use it. I must day that the "out the box" validations of tabular forms, date items etc is a real time saver.

Keep up the good work with it.

Duncan
Dimitri Gielis said…
Hi Duncan,

Just saw your blog when I was writing my weekly APEX Forum wrap-up.
Nice solution!

Dimitri
T.Will said…
Hi Duncan
In apex where do you put the exception handlers.

In the comment below
pv_goto_page_cc => '1,500');
1,500 I take it that the 500 is Page 500, the other pg?

regards
Tony
Unknown said…
Hi Duncan,

Thanks for your nice solution , however i`m new to apex and would like to see this solution in operation , what is the username and password to login .

Tohamy
Many institutions limit access to their online information. Making this information available will be an asset to all.
Anonymous said…
Hi Duncoan,

I'm new to APEX, and would like to know where to put the exception handlers?
"Re-Write your Exception Handlers
etc..."

and how and where to call it in APEX page (ex:tabular region) ?

Do you have a full example that we can view or consult?

thanks
gauravkhapekar said…
hi Duncan,
i am presently using apex 4.0 will i be able to apply your solution in that?
Duncan said…
Yes you should be able to use this in All version fo APEX however you may wish to read about the new error handling features of 4.1 as it kind of removes the need to do most of this (save logging errors in a single table).

If it were me, I would hold off implementing this and wait until you upgrade to 4.1

Just me 2 cents.

Popular posts from this blog

Custom Authentication / Authorisation Schemes (Part 1)

Mac OSX, Bootcamp and a Missing Hash Key

Add / Delete a row from a SQL based Tabular Form (Static ID)