Exception Handling + APEX_ERROR [Oracle APEX 19.1]

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.


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:

  1. ADD_ERROR Procedure






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.



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.


create or replace package body employee_util_pkg
    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;
        -- 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')
            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;
        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 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:

  1. c_inline_with_field

  2. c_inline_with_field_and_notif

  3. c_inline_in_notification

  4. 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.


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

#orclapex #Oracle #APEX_ERROR

Never Miss a Post. Subscribe Now!

Happy is the man that findeth wisdom, and the man that getteth understanding. (Proverbs 3:13)

Lucas Rayner © 2018 

  • Branco Twitter Ícone
  • Branca Ícone LinkedIn