End-user: "Hey there, I'm receiving an error in the app."
Developer: "Oh, sorry to hear that. What is the message saying?"
End-user: "Unable to process row of table EBA_PROJ_STATUS_CATS. ORA-02292: integrity constraint (XXX.SYS_C0090660) violated - child record found"
Developer: "Oh, what are you trying to do?"
End-user: "I'm trying to delete a category."
Developer: "Oh, most likely this category is in use, so you can't delete the category, you first need ..."
End-user: "Ehh?!"
You might ask yourself, what is wrong with this conversation?
The first thing is that the end-user gets an error which is hard to understand. She probably got the error before but tried a few times before calling the developer (or support). Most likely Sarah has a tight deadline and these errors don't really help their mood.
The other problem is that the developer was most likely just busy working on some complex logic and now gets interrupted. It takes some minutes before Harry can understand what Sarah is talking about. He needs to ask a few questions to know what Sarah is doing and doesn't have much context. He might ask to send a screenshot of the error and a few minutes later he receives this (app in APEX 5.1):
Harry is a smart cookie, so he knows in which schema to look for that constraint name, so he knows which table it's linked to. If Harry read my previous blog post on how to remotely see what Sarah was doing, he has more context too.
If the application is running in APEX 18.1, it's a different story. The screenshot will look like this:
APEX 18.1 actually enhanced the default error message. The user gets fewer details and sees a debug id. With this debug id the developer can get actually more info in Your App > Utilities > Debug Messages:
You might also want to check this blog post by Joel Kallman where to find more info when receiving an internal error with debug id.
Although APEX 18.1 captures more info, there's a more recommended way to deal with errors.
In APEX you can define an Error Handling Function which will kick in every time an error occurs. You can define this function in the Application Definition:
When you look in the Packaged applications that are shipped with Oracle Application Express (APEX), you find some examples. The above screenshot comes from P-Track.
The error handling function has this definition:
function apex_error_handling (p_error in apex_error.t_error )
return apex_error.t_error_result
The example used in P-Track gives a good overview (read the comments in the package) of the different errors you want to capture:
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);
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
add_error_log( p_error );
-- 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.';
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 eba_proj_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
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;
When defining this error handling function the error the user gets is more like a notification message and embedded in your app. You can also define a custom message, in the above package there's a lookup in an error_lookup table, but as it can't find the constraint name, it falls back to the normal message.
The real power comes when you start to combine the error handling function with a call to also log session state information. Then you know exactly which record this error was produced for.
There are a couple of ways to include the session state:
Team Development
I typically include a feedback page in my apps. When the user logs feedback by clicking on the feedback link, this is saved in Team Development. The really cool thing is that whenever feedback is logged, automatically the session state of items and some other info like the browser that was being used at the moment of the logging is included. But you can also log feedback through an APEX API:
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 is a PL/SQL logging and debugging framework. If you don't know it yet, you should definitely check it out. In my opinion, Logger is the best way to instrument your PL/SQL code. Logger has many cool features, one of them is the ability to log your APEX items:
logger.log_apex_items('Debug Items from Error log');
Be pro-active
Now, to prevent the conversation from happening again, you can take it one step further and start logging and monitoring those errors. Whenever errors happen you can, for example, log it in your own error table, or in your support ticket system and send yourself an email or notification.
Then instead of the end-user calling you, you call them and say "Hey, I saw you had some issues...".
By monitoring errors in your application, you can pro-actively take actions :)
Note that APEX itself also stores Application Errors. You find under Monitor Activity > Application Errors:
The report gives the error and the session, so you look further into what happened:
So, even when you didn't have an error handling function in place, you can still start monitoring errors that happen in your app. I know the readers of this blog are really smart so you might not see any errors, but still, it might be worthwhile to check it once and a while :)
You find another example of the error handling function in my Git account. I included an example of logging in your own error table and sending an email.
