Sunday, September 12, 2010

Custom Error Messages in APEX

As developers we try to prevent unhandled exceptions from occurring for end users. They can occur in any program or language, and APEX is no exception (pardon the pun).

When an unhandled exception happens, users are presented with an error message which is similar to the following:

This error message isn't very user friendly and most users won't know what an "ORA-..." message means. The other issue with this screen is that it does not provide any feedback to developers. If the user does not report this issue developers won't know that it is happening.

As part of my ODTUG presentation I demonstrated how to alter the default error page to provide a user friendly error message and provide an instant notification to developers that an error has occurred. This post will describe how to do implement a user friendly error handling method in APEX 4.0.

Special thanks to Roel Hartman and Learco Brizzi for providing the ideas behind this.

- Install Logger
Logger is an open source package written by Tyler Muth. It's an excellent tool to quickly allow developers to instrument their code. Though it is not required, this demo references it. A copy of logger is available here:

- Install Simple Modal Plug-in
Install the Simple Modal plugin into your APEX application: When you download this plugin, the zip file contains 2 plugins. One to show the modal window and one to close the modal window. It is recommended that you install both if you plan to use it in other applications. Only the "Simple Modal - Show" plug is required for this demo.

- Create Error Procedure
Compile this procedure in your schema. It will log all the page items, application items, and all other not-null page items.

* Logs unhandled error message to Database
* Logs:
* - APEX and Oracle error messages
* - All application level items
* - All page items for p_page_id
* - Items on other pages that are not null
* Logs are stored in logger_logs
* Requires:
* @param p_scope_prefix Scope prefix used in logger
* @param p_application_id
* @param p_page_id Page that error occured on
* @param p_oracle_error_msg Oracle error message
* @param p_apex_error_msg APEX error message
* @param p_email Email to be notified of error. If null, then no notification email sent.
* @author Martin Giffy D''Souza

CREATE OR REPLACE PROCEDURE sp_log_error_page (p_scope_prefix IN VARCHAR2,
p_application_id IN apex_applications.application_id%TYPE DEFAULT v ('APP_ID'),
p_page_id IN apex_application_pages.page_id%TYPE,
p_oracle_err_msg IN VARCHAR2 DEFAULT NULL,
p_apex_err_msg IN VARCHAR2 DEFAULT NULL,
v_db_name VARCHAR2 (30);
v_schema VARCHAR2 (30);
v_scope VARCHAR2 (255);
-- Set scope for logger
v_scope := p_scope_prefix;

-- Add Uniqe Identifier to scope
v_scope := LOWER (v_scope || 'unhandeled_exception{session_id: ' || v ('APP_SESSION') || ', guid: ' || SYS_GUID () || '}');

-- Log the initial error to be kept permanently
logger.log_error ('Unhandeled Exception', v_scope, 'Oracle Error: ' || p_oracle_err_msg || CHR (10) || CHR (10) || 'APEX Error Page Message: ' || p_apex_err_msg);

-- Log the information to help Dev team in production
FOR x IN (SELECT 'APP_USER' item_name, v ('APP_USER') item_val FROM DUAL
-- Include all the items from the current page
SELECT item_name, v (item_name) item_val
FROM apex_application_page_items
WHERE application_id = p_application_id
AND page_id = p_page_id
-- Include all the non-null page items
SELECT item_name, item_val
FROM (SELECT item_name, v (item_name) item_val
FROM apex_application_page_items
WHERE application_id = p_application_id
AND page_id != p_page_id)
-- Include all Application Items
SELECT item_name, v (item_name)
FROM apex_application_items
WHERE application_id = p_application_id)
logger.log_information (x.item_name || ': ' || x.item_val, v_scope);

-- Email Notification
-- Send Mail

apex_mail.send (p_to => p_email,
p_from => '', -- CHANGE THIS!
p_body => 'An unhandled exception happend in an application. Please search logger logs for: ' || v_scope,
p_subj => 'Unhandled Exception in: ' || p_application_id);
END sp_log_error_page;

- Create Error Page
This page will display a user friendly message to the user. For the purposes of this demo Page 200 will be created to handle error messages.

Create Page
Create Page 200

Create a HTML region
Region Name: Unknown Error
Source: An unhandled error occurred. A notification has been sent to the system administrator.

Create a Region Button:
Button Name: Back
Action: Redirect to URL
Execute Validations: No
URL Target: javascript:window.history.go(-1);

Add the following hidden items:

Create Computation:
Item: P200_ORA_MSG
Point: Before Header
Type: PL/SQL Expression
Computation: REPLACE(:p200_ora_msg,:p200_escape,':');

Create Dynamic Action:
(select Advanced)
Name: Show Error Message Modal
Event: Page Load
Action: Simple Modal - Show
- Esc Close: No
- Change Opacity and Background Color as desired
Select Type: Region
- Region: Unknown Error

Create Page Process:
Type: PL/SQL
Name: Log Error
Point: On Load - Before Header

sp_log_error_page (p_scope_prefix => 'apex.demo.', -- Enter what ever you want to help identify your apex errors in the log tables
p_application_id => :app_id,
p_page_id => :p200_page_id,
p_oracle_err_msg => :p200_ora_msg,
p_apex_err_msg => :p200_apex_msg,
p_email => '' -- Enter your email address here

- Change Error Template
Go to: Shared Components > Templates
Select the default Page Template (for my demo mine was: One Level Tabs - Right Sidebar (optional / table-based)
Error Page Template:

- End Result
When you have an unhandled exception the end users should see a message like:

You can view all the log information by running the following query:

FROM logger_logs
WHERE scope = 'apex.demo.unhandeled_exception{session_id: 652754467566839, guid: 901e0663a0896b35e040007f0100049a}'; -- Replace this scope with the scope that is sent in the email


  1. Hi Martin,

    I'm actually going to demo the logger package at OOW as part of my Debugging presentation.

    To get the APEX items, instead of doing a loop, you can just do:
    logger.log_apex_items('Debug APEX Items');

    To query the information you do:
    select *
    from logger_logs_5_min

    To see the log id

    select *
    from logger_logs_apex_items
    where log_id = ...

    Hope that helps,

  2. For some more background info:

    See my description in the Apex forum thread: .

    It’s a pity that for such a long time no neat solution has been made for error handling. I discussed it with Joel and he will take a look at it in the near future to enhance the errorhandling of database errors.


  3. Hi Dimitri,

    I used the loop since I wanted the log messages to be populated in production instances where the Logger level may only be set to "error".

    This is why I used logger.log_error. Taking another look at this I may be able to just use one call to logger.log_error and store all the apex values in the clob column.

    Thanks for the feedback,


  4. Martin,
    Nice posting. Ran into an issue with logger installing on a hosting companies site, and was wondering if you had any thoughts on why it was crashing?

    (Error returned: ORA-02012: missing USING keyword):

    – the following line is also used in a constant declaration in logger.pkb
    l_ctx_name varchar2(35) := substr(sys_context(‘USERENV’,'CURRENT_SCHEMA’),1,23)||’_LOGCTX’;
    execute immediate ‘create or replace context ‘||l_ctx_name||’ using logger accessed globally’;

    merge into logger_prefs p
    using (select ‘GLOBAL_CONTEXT_NAME’ PREF_NAME, l_ctx_name PREF_VALUE from dual) d
    on (p.pref_name = d.pref_name)
    when matched then
    update set p.pref_value = d.pref_value
    when not matched then
    insert (p.pref_name,p.pref_value)
    values (d.pref_name,d.pref_value);

    Thank you,

    Tony Miller
    Webster, TX

  5. Hi Tony,

    I think your context name is missing:

    Here's a quick test:

    create or replace package pkg_test as end pkg_test;

    -- In this line I excluded the context name
    create or replace context using pkg_test accessed globally;

    -- Gives me:
    ORA-02012: missing USING keyword

    Can you put a dbms_output statement after: l_ctx_name varchar2(35) := substr(...); to see what its value is?


  6. Martin,
    I think it is a good work, I can see it is a little bit slower than other pages with more items and processes. Can you tell this is the best practice for hacking error pages in Apex 4.0, or you can tell there is a better solution. I heard that Apex 4.0 will have a good handling for error page but I can't find where and how.


  7. Hi Omar,

    There is no set standard for hacking the error page in APEX. It all depends on your requirements are for the application.

    This page may appear to be slower than other pages since it essentially loads 2 pages. It first goes to the error page, then there's an HTML declaration to immediately go your custom error page. If this is an issue you can handle everything on the error page by modifying the error page template.

    APEX 4.0 has some excellent new features on item and page validations but unfortunately, to my knowledge, it still handles unhandled exceptions in the same way as it did in previous versions.


  8. Hello Martin,

    nice tutorial but could you upload the somewhere cause the from oracle is broken and invalid.


    does not work.

  9. I just tried to download it and it worked. Did you login before downloading it?

  10. Hi Martin,

    I tried using Firefox to download it and now it works. Guess my company where I work does not allow to unpack zip files properly.

  11. Hi Martin,

    I installed the logger to an existing schema.

    I tested it using :
    exec logger.log('hello world');
    select text from logger_logs_5_min;

    Then I installed both the Clarifit Simple plugins.

    I created your procedure, compiled it with no errors.

    I made a page '204' instead of '200' I edited 200 to 204 where necassary.

    In my default template (mine seemed to be 'One Level Tabs - Right Sidebar (fixed-width / DIV based)') I copied your javascript into the Error page template area and in the javascript I edited the 200 with 204.

    But when I raise an error I still get the ugly oracle error message.

  12. Hi Joey,

    What page template is your page using? On the page edit screen RClick on the page name (The root of the tree in the Page Rendering region) and click Edit.

    If the page template is not the default template then you'll need to either change it to the default template or modify the error page for that template.

    If you still have issues please send me an email.


  13. Hi Martin,

    Nice and very infomative post.
    Can I apply this solution in APEX 3.2 Version?


  14. Hi Trapz,

    Yes you can do this in APEX 3.2. I actually gave the ODTUG demo using APEX 4.0. Unfortunately I won't be supporting any APEX 3.2 requests for this solution. If you can install the plugins on a 4.0 instance it shouldn't be to hard to implement in 3.2

    Good luck,


  15. It is so frustrating that we need some kind of hacks for such a basic functionality.
    In a page definition should be a 'Error page' option where we can choose to use a default or a custom error display.

    Long live APEX ! :)

  16. Adixtx, Patrick Wolf mentioned that this may be a configurable/declarative solution in future versions of APEX.

  17. Nice post and very useful. Thanks Martin!

  18. Nice post, works very well.
    Just one thing. You should change
    otherwise only the first colon is replaced.

  19. Hi Ino,

    Good catch. I've updated the code to use the global replace.



    1. Hi Martin
      dosnot work Redirect URL javascript:window.history.go(-1) in internet explorer 8
      help please

  20. Hi Martin
    dosnot work Redirect URL javascript:window.history.go(-1) in internet explorer 8
    help please

    1. Can you please build a test case and post on