Oracle APEX - Show Friendly Messages While Improve Your Debug Log
Mauro Martins Pagnez
Deputy Coordinator | Oracle Developer | APEX | PL/SQL | IA | Oracle DBA | Oracle Certified🏅
When we are creating softwares, it is very important to pay attention to system messages because they can be explored as a source of information leaks. This task is frequently ignored during all application life cycle.
It's very common to see Oracle errors showed on the end user's screen. Fortunately, this is a easy problem to solve and in this article, I will show you how to make it.
APEX_DEBUG Package API
First, it's necessary to know a little bit about APEX_DEBUG package. This package provides functionalities that allow the developer to manage the debug log. It's important to know at least these three procedures:
apex_debug.error(sqlerrm);
apex_debug.warn("The l_fullname variable has an incorrect value. %", sqlerrm);
apex_debug.info('User enter an incorrect value on Postal Code.');
APEX_ERROR package API
This is another foundation package for this purpose. It contains necessary functionalities to handle errors and raise them on the screen. In this article, we will use only function called add_error.
The add_error function adds an error to the error stack, but logs it only if debugging is activated. This behavior can be problematic, as many errors might occur during execution, and the development team needs to be aware of them. For critical errors, the add_error function's behavior is insufficient, as we want to log the error regardless of the debugging state. Therefore, this functionality requires additional measures.
Creating a Auxiliary Procedure
The strategy here is to create a subprogram that can send only friendly messages to the end user's interface, while always logging issues simultaneously. This procedure avoid repeated code, because for all calls, you would need two commands that would be encapsulated into the procedure.
The following code provides the necessary DDL to create your procedure. You can chance this script as needed.
create or replace procedure log_error(p_message varchar2,
p_display_location varchar2 default apex_error.c_inline_in_notification,
p_display_item varchar2 default null) as
begin
apex_debug.error(sqlerrm);
apex_error.add_error(p_message => p_message, p_display_location => p_display_location);
end;
Messages into PL/SQL Blocks or Developer Subprograms
Whenever an unhandled error is thrown from a PL/SQL block or subprogram, an error message is displayed on the end user's interface. This can potentially expose sensitive information about the database and application's internal structure, such as variable names, table names, and other database objects. To mitigate this risk, a simple solution is using a Exception clause, like the following example:
declare
r_employee employees%rowtype;
begin
-- I've forced an error here, in the into clause to throw "others" exception
select e.employee_id, e.name, e.phone_number,
e.email,
d.department_name
into r_employee
from employees e
inner join department d on
e.department_id = d.department_id
where e.employee_id = :P10_EMPLOYEE_ID;
exception
when no_data_found then
...
when others then
log_error('Oops, something went wrong! We''re working on fixing the issue. Please try again later or contact our support team.');
end;
When an error happens in this code, this message is displayed this way:
While the friendly message is displayed on the user's screen, a real error message is always logged. The log can be viewed on View Debug page:
This error message can also be viewed in Workspace -> My Application -> Utilities -> Debug Messages, where the developer typically tracks the errors that have occurred over time on his application:
Remember that PL/SQL blocks are used in many parts of your application: processes, some dynamic actions, server-side conditions etc.
Error Handling Function
Oracle APEX enables you to create a centralized error handling function for both: page-level and application-level errors. The official documentation offers an example for creating this function. However, many developers tend to copy and paste this example without making necessary modifications. A significant issue with this approach is the potential loss of valuable error messages, as the example function doesn't include any logging mechanisms.
To correct this function, you can easily add a log function to its body. This will allow you to capture and record error information. See the example below:
create or replace function apex_error_handling_example (p_error in apex_error.t_error)
return apex_error.t_error_result is
l_result apex_error.t_error_result;
l_reference_id number;
l_constraint_name varchar2(255);
begin
l_result := apex_error.init_error_result (p_error => p_error );
-- +-------------------------------------------+
-- | To simplify, you may log the error here |
-- +--------------------------------------------+
apex_debug.error(l_result.ora_sqlerrm);
-- -----------------------------------------------
...
if p_error.is_internal_error then
if not p_error.is_common_runtime_error then
...
end if;
...
else
end if;
end;
In a future article, I can give some tips and show most details about this function.
CONSTRAINT_LOOKUP Table
Inside the error handling function you may add a code snippet to handle errors related with constraints. In the example function refered before, there is a part of the function body that contains it:
...
/*
------------------------------------------------
Here, the code treats the ORA errors which have these numbers, that
correspondent constraint errors:
-) 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
You can add another as needed
-------------------------------------------------
*/
if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
-- The function below extract the constraint name
l_constraint_name := apex_error.extract_constraint_name (
p_error => p_error );
begin
--- Now, the constraint name is searched
select message
into l_result.message
from constraint_lookup
where constraint_name = l_constraint_name;
exception when no_data_found then null; -- It happens if not every constraint has to be in our lookup table
end;
end if;
...
Looking at the code, you see the CONSTRAINT_LOOKUP table, which has the following structure:
You can create this table as:
create table constraint_lookup (
constraint_name varchar2(30),
message varchar2(4000),
primary key (constraint_name)
)
organization index
overflow tablespace <your_tablespace_here>;
To work with your error handling function, you must fill the table with the constraint name and a correspondent friendly message. For example:
/*
-------------------------------------------------------
Ticket table has a column called state, which may have this values: B (Backlog), P (In Progress), D (Done). There is a check constraint to verify this values called ck_ticket_state.
-------------------------------------------------------
*/
insert into constraint_lookup (constraint_name, message) values
('CK_TICKET_STATE', 'This is not a valid value for a ticket status. Choose one of these values: Backlog, In Progress or Done.');
/*
---------------------------------------------------------
Model table has a column called name, which is unique, defined by uk_model_name constraint.
---------------------------------------------------------
*/
insert into constraint_lookup (constraint_name, message) values
('UK_MODEL_NAME', 'Model name already exists. This name must be unique.');
Text Messages
Text Messages is a very useful feature in Oracle APEX that allows your application to standardize all system messages and translate them into other languages. Moreover, there is another powerful functionality: text messages allow you to replace database and APEX error messages.
To access this feature, go to App Builder -> Your Application -> Shared Components -> Globalization -> Text Messages. There, you can save the messages that will replace native error messages. For this purpose, you can follow the patterns below:
For reference, you can see all Oracle Database messages on the Database Error Messages, in the official documentation. You can research the native Oracle APEX messages on the pages below:
In a future article, I will demonstrate how to use Text Messages in a more professional and detailed manner.
Validations
A good way to prevent attacks in your application is validating all data entries. Validations also prevent unhandled messages and improve the end user's experience because the validation message can instruct the users to fill correctly.
I used to create validations of Function Body (returning Error Text) type because it is simple and direct. This type of validation works as following:
declare
l_message := 'you_message_here';
begin
if my_condition() = false then
-- This message is displayed
return l_message;
end if;
end;
When the user entry is invalid, Oracle APEX displays the validation message on the error message dialog and log it. You can see this message in Develop Bar -> View Debug, if the debug is activated, and in App Builder -> My Application -> Utilities -> Debug Messages.
X-Forwarded-For header
When your infrastructure contains reverse proxies, Oracle APEX cannot register the end user's remote address (IP) in your native log tables, but instead registers the IP address of the nearby proxy server instances in the load balance because APEX doesn't see the beyond the proxies. For this reason, it's needed a simple configuration in your APEX instance.
To solve this issue, you must authenticate on your Administration Services and look for Manage Instance -> Security. On this page you must search the section Authentication Control -> General Settings and identify the field Inbound Proxy Servers. In this field you will insert all reverse proxy IP adresses. Now, it's possible catch the correct end user's remote address, because the X-Forwarded-For information obtained from the reverse proxies.
Final Considerations
In this article, we've explored the importance of effective error handling and logging in Oracle APEX applications. I hope to contribute to your growth and help you handle these kinds of issues. See you soon in the next article! Farewell!