Skip to content

Instantly share code, notes, and snippets.

@dgielis

dgielis/app_error_pkg.sql

Created June 30, 2018 21:21
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save dgielis/050d3f2169a44afc39514171f6b6095a to your computer and use it in GitHub Desktop.
Save dgielis/050d3f2169a44afc39514171f6b6095a to your computer and use it in GitHub Desktop.
Download ZIP
Custom Error Handling function for APEX
Raw
app_error_pkg.sql
create or replace package app_error_pkg
as
--
-- Function: apex_error_handling
-- Purpose: Try to elegantly handle errors that occur while using the application.
--
function apex_error_handling (
p_error in apex_error.t_error )
return apex_error.t_error_result;
--
-- Procedure: add_error_log
-- Purpose: logs application errors
--
procedure add_error_log (
p_error in varchar2 default null,
p_procedure_name in varchar2 default null,
p_page_item_name in varchar2 default null,
p_region_id in varchar2 default null,
p_column_alias in varchar2 default null,
p_row_num in varchar2 default null,
p_apex_error_code in varchar2 default null,
p_ora_sqlcode in varchar2 default null,
p_ora_sqlerrm in varchar2 default null,
p_error_backtrace in varchar2 default null,
p_arg1_name in varchar2 default null,
p_arg1_val in varchar2 default null,
p_arg2_name in varchar2 default null,
p_arg2_val in varchar2 default null,
p_arg3_name in varchar2 default null,
p_arg3_val in varchar2 default null,
p_arg4_name in varchar2 default null,
p_arg4_val in varchar2 default null,
p_arg5_name in varchar2 default null,
p_arg5_val in varchar2 default null,
p_arg6_name in varchar2 default null,
p_arg6_val in varchar2 default null,
p_arg7_name in varchar2 default null,
p_arg7_val in varchar2 default null,
p_arg8_name in varchar2 default null,
p_arg8_val in varchar2 default null,
p_arg9_name in varchar2 default null,
p_arg9_val in varchar2 default null,
p_arg10_name in varchar2 default null,
p_arg10_val in varchar2 default null
);
end app_error_pkg;
/
create or replace package body app_error_pkg
as
--
-- Function: apex_error_handling
-- Purpose: Try to elegantly handle errors that occur while using the application.
--
function apex_error_handling ( p_error in apex_error.t_error )
return apex_error.t_error_result is
l_result apex_error.t_error_result;
l_constraint_name varchar2(255);
procedure add_error_log ( p_error in apex_error.t_error ) is
pragma autonomous_transaction;
begin
-- Log the error.
insert into app_error (
err_time,
app_id,
app_page_id,
app_user,
user_agent,
ip_address,
ip_address2,
message,
page_item_name,
region_id,
column_alias,
row_num,
apex_error_code,
ora_sqlcode,
ora_sqlerrm,
error_backtrace )
values (
sysdate,
sys_context('APEX$SESSION','APP_ID'),
sys_context('APEX$SESSION','APP_PAGE_ID'),
sys_context('APEX$SESSION','APP_USER'),
owa_util.get_cgi_env('HTTP_USER_AGENT'),
owa_util.get_cgi_env('REMOTE_ADDR'),
sys_context('USERENV', 'IP_ADDRESS'),
substr(p_error.message,1,4000),
p_error.page_item_name,
p_error.region_id,
p_error.column_alias,
p_error.row_num,
p_error.apex_error_code,
p_error.ora_sqlcode,
substr(p_error.ora_sqlerrm,1,4000),
substr(p_error.error_backtrace,1,4000)
);
commit;
end add_error_log;
begin
l_result := apex_error.init_error_result(p_error => p_error );
-- If it is an internal error raised by APEX, like an invalid statement or
-- code which can not be executed, the error text might contain security sensitive
-- information. To avoid this security problem we can rewrite the error to
-- a generic error message and log the original error message for further
-- investigation by the help desk.
if p_error.is_internal_error then
-- mask all errors that are not common runtime errors (Access Denied
-- errors raised by application / page authorization and all errors
-- regarding session and session state)
if not p_error.is_common_runtime_error then
-- Submit to own error logging table
add_error_log( p_error );
-- Submit into Team Development as feedback
apex_util.submit_feedback (
p_comment => 'Unexpected Error',
p_type => 3,
p_application_id => v('APP_ID'),
p_page_id => v('APP_PAGE_ID'),
p_email => v('APP_USER'),
p_label_01 => 'Session',
p_attribute_01 => v('APP_SESSION'),
p_label_02 => 'Language',
p_attribute_02 => v('AI_LANGUAGE'),
p_label_03 => 'Error orq_sqlcode',
p_attribute_03 => p_error.ora_sqlcode,
p_label_04 => 'Error message',
p_attribute_04 => p_error.message,
p_label_05 => 'UI Error message',
p_attribute_05 => l_result.message
);
-- Logger
logger.log_error('There is an error in xxx');
logger.log_apex_items('Debug Items from Error log');
-- Send email
l_subject := 'Issue in XXX';
l_body := p_error.ora_sqlcode || CHR(10) || p_error.message;
l_body := l_body || CHR(10) || 'App User: ' || v('APP_USER');
l_body := l_body || CHR(10) || 'Session: ' || v('APP_SESSION');
l_body := l_body || CHR(10) || 'App: ' || v('APP_ID');
l_body := l_body || CHR(10) || 'Page: ' || v('APP_PAGE_ID');
wwv_flow_api.set_security_group_id;
apex_mail.send(
p_from => 'xxx',
p_to => 'xxx',
p_subj => l_subject,
p_body => l_body,
p_body_html => l_body);
apex_mail.push_queue;
-- Log an Issues Developer Portal / JIRA
-- see other entries in Gist Dimitri Gielis
--https://gist.github.com/dgielis/e97c94391058dcacb4a2b50e355d9445
-- Change the message to the generic error message which doesn't expose
-- any sensitive information.
l_result.message := 'An unexpected internal application error has occurred: ' || substr(p_error.message,0,3500);
l_result.additional_info := null;
end if;
else
-- Always show the error as inline error
-- Note: If you have created manual tabular forms (using the package
-- apex_item/htmldb_item in the SQL statement) you should still
-- use "On error page" on that pages to avoid loosing entered data
l_result.display_location := case
when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
else l_result.display_location
end;
-- If it's a constraint violation like
--
-- -) ORA-00001: unique constraint violated
-- -) ORA-02091: transaction rolled back (-> can hide a deferred constraint)
-- -) ORA-02290: check constraint violated
-- -) ORA-02291: integrity constraint violated - parent key not found
-- -) ORA-02292: integrity constraint violated - child record found
--
-- we try to get a friendly error message from our constraint lookup configuration.
-- If we don't find the constraint in our lookup table we fallback to
-- the original ORA error message.
if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
l_constraint_name := apex_error.extract_constraint_name (
p_error => p_error );
begin
select message
into l_result.message
from app_error_lookup
where constraint_name = l_constraint_name;
exception when no_data_found then null; -- not every constraint has to be in our lookup table
end;
end if;
-- If an ORA error has been raised, for example a raise_application_error(-20xxx, '...')
-- in a table trigger or in a PL/SQL package called by a process and we
-- haven't found the error in our lookup table, then we just want to see
-- the actual error text and not the full error stack with all the ORA error numbers.
if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
l_result.message := apex_error.get_first_ora_error_text (
p_error => p_error );
end if;
-- If no associated page item/tabular form column has been set, we can use
-- apex_error.auto_set_associated_item to automatically guess the affected
-- error field by examine the ORA error for constraint names or column names.
if l_result.page_item_name is null and l_result.column_alias is null then
apex_error.auto_set_associated_item (
p_error => p_error,
p_error_result => l_result );
end if;
end if;
return l_result;
end apex_error_handling;
--
-- Procedure: add_error_log
-- Purpose: logs application errors
--
procedure add_error_log (
p_error in varchar2 default null,
p_procedure_name in varchar2 default null,
p_page_item_name in varchar2 default null,
p_region_id in varchar2 default null,
p_column_alias in varchar2 default null,
p_row_num in varchar2 default null,
p_apex_error_code in varchar2 default null,
p_ora_sqlcode in varchar2 default null,
p_ora_sqlerrm in varchar2 default null,
p_error_backtrace in varchar2 default null,
p_arg1_name in varchar2 default null,
p_arg1_val in varchar2 default null,
p_arg2_name in varchar2 default null,
p_arg2_val in varchar2 default null,
p_arg3_name in varchar2 default null,
p_arg3_val in varchar2 default null,
p_arg4_name in varchar2 default null,
p_arg4_val in varchar2 default null,
p_arg5_name in varchar2 default null,
p_arg5_val in varchar2 default null,
p_arg6_name in varchar2 default null,
p_arg6_val in varchar2 default null,
p_arg7_name in varchar2 default null,
p_arg7_val in varchar2 default null,
p_arg8_name in varchar2 default null,
p_arg8_val in varchar2 default null,
p_arg9_name in varchar2 default null,
p_arg9_val in varchar2 default null,
p_arg10_name in varchar2 default null,
p_arg10_val in varchar2 default null )
is
begin
-- Remove old errors
--delete from app_errors where err_time <= localtimestamp - 21;
-- Log the error.
insert into app_error (
app_id,
app_page_id,
app_user,
user_agent,
--
ip_address,
ip_address2,
message,
page_item_name,
--
region_id,
column_alias,
row_num,
apex_error_code,
--
ora_sqlcode,
ora_sqlerrm,
error_backtrace,
arg1_name,
arg1_val,
arg2_name,
arg2_val,
arg3_name,
arg3_val,
arg4_name,
arg4_val,
arg5_name,
arg5_val,
arg6_name,
arg6_val,
arg7_name,
arg7_val,
arg8_name,
arg8_val,
arg9_name,
arg9_val,
arg10_name,
arg10_val )
select v('APP_ID'),
v('APP_PAGE_ID'),
v('APP_USER'),
owa_util.get_cgi_env('HTTP_USER_AGENT'),
--
owa_util.get_cgi_env('REMOTE_ADDR'),
sys_context('USERENV', 'IP_ADDRESS'),
substr(p_error,0,4000),
substr(p_page_item_name,0,4000),
--
p_region_id,
p_column_alias,
p_row_num,
p_apex_error_code,
--
p_ora_sqlcode,
substr(p_ora_sqlerrm,1,4000),
substr(p_error_backtrace,1,4000),
p_arg1_name,
p_arg1_val,
p_arg2_name,
p_arg2_val,
p_arg3_name,
p_arg3_val,
p_arg4_name,
p_arg4_val,
p_arg5_name,
p_arg5_val,
p_arg6_name,
p_arg6_val,
p_arg7_name,
p_arg7_val,
p_arg8_name,
p_arg8_val,
p_arg9_name,
p_arg9_val,
p_arg10_name,
p_arg10_val
from dual;
commit;
end add_error_log;
end app_error_pkg;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment