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