Created
June 30, 2018 21:21
-
-
Save dgielis/050d3f2169a44afc39514171f6b6095a to your computer and use it in GitHub Desktop.
Custom Error Handling function for APEX
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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