In this post I would like to present a native Oracle APEX package which, in my view, is not very much addressed and I find it quite useful in situations where it is necessary to handle an exception or handle business rules, such as in a package, for example.
As you know, exception manipulations, whether they are user-defined or system-defined, are quite useful for handling the abnormal finalization of a program in Oracle. When we combine the APEX_ERROR package from Oracle APEX with exception handling, we have a very useful - and even more elegant - way of presenting the end user with a response to some incorrect action or abnormal program activity.
Note: The APEX_ERROR package need not necessarily be used in exception handling. In this post we'll cover its use in exceptions.
APEX_ERROR Package
The APEX_ERROR package provides procedures and functions that can be used to handle errors in an APEX application, either through page processes or through database objects (packages, procedures, etc).
The procedures and functions that are available in the APEX_ERROR package (Release 19.1) are:
ADD_ERROR Procedure
APEX_ERROR.HAVE_ERRORS_OCCURRED Function
AUTO_SET_ASSOCIATED_ITEM Procedure
EXTRACT_CONSTRAINT_NAME Function
GET_FIRST_ORA_ERROR_TEXT Function
INIT_ERROR_RESULT Function
For more information, see the official Oracle documentation.
Practical example
To better understand how we can work with exception handlers + APEX_ERROR, we will use a practical example, where we will have the following tables of employees and departments.
Employees
Departments
In our example, we will have an employee relocation screen by department where we will use the APEX_ERROR package in a system-defined and another user-defined exception.
The system-defined exception, which is defined and maintained implicitly by the Oracle Database and which we will cover in our example, is the NO_DATA_FOUND. An error message should be displayed to the user if the informed employee does not exist in the database.
For the user-defined exception, we will define some departments for which employees can not be relocated, and if the user tries to relocate the employee to these departments, we will politely say that he can not do that action.
The departments that do not accept relocations are those in the DEPARTMENTS table that have a value of 'N' in the ACCEPTS_RELOCATION column.
Finally, to update the employee's department, we will have the EMPLOYEE_UTIL_PKG package, with the CHANGE_DEPARTMENT procedure, with the appropriate exception handling added to the APEX_ERROR package.
EMPLOYEE_UTIL_PKG
create or replace package body employee_util_pkg
as
procedure change_department (
p_employee in varchar2,
p_department in number,
p_item_1 in varchar2,
p_item_2 in varchar2
) as
l_employee_id employee.id% type;
l_item varchar2(100);
not_authorized exception;
begin
-- Sets the employee name item
l_item := p_item_1;
-- Get Employee ID
-- If no exists then NO_DATA_FOUND
select id
into l_employee_id
from employee
where upper(name) = upper(p_employee);
for i in (select id
from department
where change_department = 'N')
loop
if p_department = i.id then
-- Sets the department item
l_item: = p_item_2;
raise not_authorized;
end if;
end loop;
-- Change Department
update employee
set department_id = p_department
where id = l_employee_id;
exception
when no_data_found then
apex_error.add_error (
p_message => 'The employee does not exist in the Data Base.',
p_display_location => zpex_error.c_inline_with_field_and_notif,
p_page_item_name => l_item
);
when not_authorized then
apex_error.add_error (
p_message => 'Sorry, the employee can not be transferred to this department.',
p_display_location => apex_error.c_inline_with_field_and_notif,
p_page_item_name => l_item
);
end;
end employee_util_pkg;Note that in the above package we have two types of exception handlers, one user-defined and another system-defined, where, the NOT_AUTHORIZED exception user-defined was explicitly set to be used in cases where the department does not allow employee changes to it, and the system-defined is implicitly used in the case of the SELECT statement if the employee with the name entered does not exist in the database (NO_DATA_FOUND).
At the end of the package it is possible to see the handling of exceptions using the APEX_ERROR package, treating the exception NO_DATAFOUND (system-defined) and NOT_AUTHORIZED (user-defined), where we use the procedure ADD_ERROR, passing as parameter the text of the message that we will display for the user (p_message), the location where the message will be displayed (p_display_location), and the item name that will be referenced in the generated error (p_page_item_name).
* In the APEX_ERROR package the message display location (p_display_location) options are the same as those found in the page validation error messages. Are:
c_inline_with_field
c_inline_with_field_and_notif
c_inline_in_notification
c_on_error_page
In our example, the message will be displayed inline with field and in the notification.
Note: In the example above we do not have good practices, such as using the employee's name to retrieve your ID, taking into account that we could have more than one employee with the same name. However, this is a controlled example, used only to demonstrate two types of exception. Stay tuned!
Department Change Form is:
Page processes are:
Note that we do not have any validation in page rendering, all validations are done inside the EMPLOYEE_UTIL_PKG package through the APEX_ERROR package.
Result
The result trying to relocate an employee who does not exist in the database (NO_DATA_FOUND):
The result trying to relocate an employee to a department that does not allow reallocations (NOT_AUTHORIZED):
In practice...
To see this example, go to the demo application below.
Demo Application
Link: Learning APEX App
User (default): demo
Password: demo