Monday, July 11, 2011

APEX Region Errors - Part 3

Disclaimer: This is an advanced post that discusses and modifies some of the inner workings of APEX.

In APEX Region Errors - Part 2 I discussed how to add triggers on the APEX Activity Log tables to store information in custom error tables when a user encounters an APEX region error.

Instead of storing the information in custom error tables you can leverage the APEX Feedback tool and trigger an automatic feedback entry. This may be a preferred option as you don't need to create custom tables and the feedback tool provides a lot of information.

Before continuing it's important that you know how to use the new APEX Feedback tool. If you don't know about the APEX Feedback tool I suggest that you read about how to create the feedback link (http://dgielis.blogspot.com/2010/03/apex-40-feedback-link.html) and how to access the feedback tool (http://dgielis.blogspot.com/2010/03/apex-40-looking-at-feedback-through.html). Try implementing it in a dummy application to see what you can do with it.

The following triggers will enter a feedback entry each time a region error occurs:

Disclaimer (again): Modifying anything in the APEX schema will put your APEX instance in an unsupported state. Please proceed with caution. I take no responsibility for any negative outcomes from this.

-- NOTE: Do this as the APEX_040000 user or a privlidged user such as SYSTEM
CREATE OR REPLACE TRIGGER apex_040000.trg_apex_activity_log1_air
AFTER INSERT
ON apex_040000.wwv_flow_activity_log1$
FOR EACH ROW
WHEN (new.SQLERRM IS NOT NULL)
DECLARE
BEGIN
-- Log as feedback
apex_util.submit_feedback (
p_comment => 'AUTO MSG: Region Error', -- Put a comment here that can be used to easily identify auto generated feedback messages
p_type => 3, -- Bug. See API documentation for different values
p_application_id => :new.flow_id,
p_page_id => :new.step_id,
p_email => null,
p_label_01 => 'Error Message', -- You can add up to 8 label/attributes. See API documentation for more information
p_attribute_01 => :new.sqlerrm,
p_label_02 => 'Component Type',
p_attribute_02 => :new.sqlerrm_component_type,
p_label_03 => 'Component Name',
p_attribute_03 => :new.sqlerrm_component_name
);

-- Could use APEX_MAIL to send a notification to a list of developers to take a look at problem
-- This is entirely optional. Modify as required
apex_mail.send(
p_to => 'someone@yourorg.com',
p_from => 'someone@yourorg.com',
p_body => 'Region Error Occured. Please Check Feedback',
p_body_html => '',
p_subj => 'APEX Region Error');

END;
/
And... (differences are highlighted)

-- NOTE: Do this as the APEX_040000 user or a privlidged user such as SYSTEM
CREATE OR REPLACE TRIGGER apex_040000.trg_apex_activity_log2_air
AFTER INSERT
ON apex_040000.wwv_flow_activity_log2$
FOR EACH ROW
WHEN (new.SQLERRM IS NOT NULL)
DECLARE
BEGIN
-- Log as feedback
apex_util.submit_feedback (
p_comment => 'AUTO MSG: Region Error', -- Put a comment here that can be used to easily identify auto generated feedback messages
p_type => 3, -- Bug. See API documentation for different values
p_application_id => :new.flow_id,
p_page_id => :new.step_id,
p_email => null,
p_label_01 => 'Error Message', -- You can add up to 8 label/attributes. See API documentation for more information
p_attribute_01 => :new.sqlerrm,
p_label_02 => 'Component Type',
p_attribute_02 => :new.sqlerrm_component_type,
p_label_03 => 'Component Name',
p_attribute_03 => :new.sqlerrm_component_name
);

-- Could use APEX_MAIL to send a notification to a list of developers to take a look at problem
-- This is entirely optional. Modify as required
apex_mail.send(
p_to => 'someone@yourorg.com',
p_from => 'someone@yourorg.com',
p_body => 'Region Error Occured. Please Check Feedback',
p_body_html => '',
p_subj => 'APEX Region Error');

END;
/
When a region error occurs you can now view the information in Team Development > Feedback.

If you click the Edit button you can get a lot of detailed information about was happening when the user encountered the error including all of the values in session state.

7 comments:

  1. Hi Martin,

    Would it not be a "safer" (i.e. supported) option instead of creating a trigger like that, create a scheduled job (say every 5 minutes), that queries the apex_workspace_activity_log view (which is a valid apex dictionary view that it is supported to query), looping round the records created in the last 5 minutes and then performing the apex_util.submit_feedback, apex_mail.send etc?

    Obviously this method would lose the immediacy of the trigger method you've outlined, but it wouldn't affect your core APEX installation and you would remain in a supported position.

    John.

    ReplyDelete
  2. Hi John,

    You could use a scheduled job that queries the apex_workspace_activity_log view every 5 minutes to email notifications about region errors. However if you want the information about the user's session state at that moment in time I think you'll need to keep the trigger.

    Martin

    ReplyDelete
  3. I know this is not question to you but loudly thinking...
    Why Apex has not offer public synonyms for many of it's objects, which might be interested? In this way we could make easy generalizing solutions for such an objects with some procedures in privileged schema (not SYS or SYSTEM but custom one ... i.e. TOOLS)
    Rg,
    Damir
    P.S.
    Trigger approach might be performance benefit if this table has many records.

    ReplyDelete
    Replies
    1. Hi Damir,

      You're in luck, APEX does contain a lot of views which lets you access a lot of information about your application. Most of the views can be found querying the "main view" called APEX_DICTIONARY. See: http://www.talkapex.com/2008/11/how-to-list-apex-dictionary-views-using.html

      They're always a few that don't make it into APEX_DICTIONARY list. You can view them here: http://www.talkapex.com/2010/07/missing-views-from-apexdictionary.html

      Delete
  4. Martin,

    what is difference in using "WWV_FLOW_ACTIVITY_LOG1$" and "WWV_FLOW_ACTIVITY_LOG2$" tables?

    Rg,
    Damir

    ReplyDelete
    Replies
    1. Hi Damir,

      Read the first paragraph from this article: http://www.talkapex.com/2009/05/apex-logs-storing-log-data.html It will explain why they're two tables.

      Use the "wrapper" view APEX_WORKSPACE_ACTIVITY_LOG instead of querying against the individual tables.

      Delete