Error handling in Oracle APEX

In any application, errors are abnormal conditions that disrupt normal program execution. All applications must raise and handle errors in order to communicate when exceptional conditions occur. In Oracle APEX, errors come in the form of PL/SQL Exceptions. By the end of this guide, you will have implemented a complete error logging function, and your APEX applications will have taken a step up in both error handling and user experience.

Exception handling in PL/SQL

PL/SQL is the declarative programming language of APEX applications. In PL/SQL, an exception can either be user-defined or be predefined by PL/SQL. A user-defined exception is raised using the RAISE keyword. PL/SQL blocks can be extended to handle exceptions using the EXCEPTION keyword. Below is an example of a PL/SQL block that raises and catches a user-defined exception.

DECLARE
   e_my_exception EXCEPTION;
BEGIN
   RAISE e_my_exception;
EXCEPTION
   WHEN e_my_exception THEN
       dbms_output.put_line('The exception was caught!');
END;
/

This simple declare-raise-catch structure is the easiest way to create and handle user-defined exceptions. This can be applied to your application to handle conditions that should be considered an error, or to give feedback to end users when something goes wrong on the backend.

Handling predefined exceptions is just as easy. A list of PL/SQL predefined exceptions can be found in the Oracle Database PL/SQL Language Reference. Below is an example of a PL/SQL block that catches a predefined exception.

DECLARE
   v_variable VARCHAR2(100);
BEGIN
   SELECT 'HELLO'
   INTO v_variable
   FROM dual
   WHERE 1 = 2; -- this condition is false, so no data will be found
EXCEPTION
   WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('The query returned 0 rows!');
END;
/

Using page validations

Custom error handling like the above should not be used to validate user input. This should be done using Oracle APEX’s declarative page validation components which are specifically designed to check the validity of page item values before processing is done. Handling these conditions in custom PL/SQL blocks can make your application’s processes unnecessarily verbose and more difficult to maintain.

The application error-handling function

Handling exceptions manually in PL/SQL is effective, but what happens when the application throws an exception you weren’t expecting and haven’t caught? Will this error be reported to end users and expose your application’s implementation details? Fear not -APEX provides a tool just for this. The application-level error-handling function is a property of every APEX application’s definition. It can be found in the ‘Application Definition’ page:

This property can be used to assign a PL/SQL function that will be called whenever an APEX exception occurs. It may be a standalone database function, or a package function, however the function must have the following signature:

FUNCTION <name> (
   p_error IN apex_error.t_error
) RETURN apex_error.t_error_result

A complete example of an error-handling function can be found in the Oracle APEX API Reference, or you can build one for yourself using the approach in the next section.

The following section will explore a best-practice implementation of application-level error handling and reporting in Oracle APEX. This section provides the full implementation and, as such, is code-heavy.

Logging APEX Errors

When errors are encountered, there needs to be a way to log them in the database and provide end users with a reference to the corresponding log. This will allow you as the developer to investigate issues with your application and provide long-term support to end users much easier.

To start, we need to define a table in our database that can store error information.

CREATE TABLE error_log (
   error_log_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY NOT NULL
   , error_code VARCHAR2(4000)
   , error_message VARCHAR2(4000)
   , error_stack VARCHAR2(4000)
   , error_backtrace VARCHAR2(4000)
   , proc_values VARCHAR2(4000)
   , proc_user VARCHAR2(4000)
   , proc_time TIMESTAMP(6)
);

Next, we need a function that will act as our application’s error handler. Let’s walk through it step-by-step: 

First, we need a function that matches the signature we saw earlier.

CREATE OR REPLACE EDITIONABLE FUNCTION apex_handle_error (
   p_error IN apex_error.t_error
) RETURN apex_error.t_error_result IS
   PRAGMA AUTONOMOUS_TRANSACTION;

This will be a standalone function named ‘apex_handle_error’. We now need to initialize the APEX error result. This is a type defined in the apex_error package that holds information about errors. We are declaring this function as an autonomous transaction so that we can commit logging directly to the database immediately, without affecting any other database transactions, which may be incomplete due to the error that is being logged.

   v_result apex_error.t_error_result;
   v_error_log_id error_log.error_log_id%TYPE;
   v_proc_values error_log.proc_values%TYPE;
BEGIN
   v_result := apex_error.init_error_result(
       p_error => p_error
   );

Details around the apex_error.init_error_result function can be found in the Oracle APEX API Reference. Now, we are ready to start adding our own custom information to the error, and logging it in our table. We will first collect all of the page items that will be stored as the ‘proc values’ in the error log.

   IF p_error.ora_sqlcode IS NOT NULL THEN
       BEGIN
           SELECT JSON_OBJECT(
               'page' VALUE MAX(aapi.page_id)
               , 'user' VALUE apex_util.get_session_state('APP_USER')
               , 'items' VALUE JSON_ARRAYAGG(
                   JSON_OBJECT(
                       'name' VALUE aapi.item_name
                       , 'value' VALUE NVL(apex_util.get_session_state(aapi.item_name), '(null)')
                   )
               )
           )
           INTO v_proc_values
           FROM apex_application_page_items aapi
           WHERE UPPER(aapi.display_as) != UPPER('DISPLAY ONLY')
           AND aapi.application_id = apex_util.get_session_state('APP_ID')
           AND aapi.page_id = apex_util.get_session_state('APP_PAGE_ID');
       EXCEPTION
           WHEN OTHERS THEN
               -- ignore any errors raised while getting the proc values
               NULL;
       END;

This block builds a JSON array of APEX page items that exist in session state at the time of the error’s occurrence. This is very useful information to have when reviewing errors to debug and identify the root causes of issues. Now that we have that information, we need to create a record in our error_log table.

   INSERT INTO error_log (
           error_code
           , error_message
           , error_stack
           , error_backtrace
           , proc_values
           , proc_user
           , proc_time
       ) VALUES (
           p_error.ora_sqlcode
           , p_error.ora_sqlerrm
           , dbms_utility.format_error_stack()
           , dbms_utility.format_error_backtrace()
           , v_proc_values
           , apex_util.get_session_state('APP_USER')
           , CURRENT_TIMESTAMP
       ) RETURNING error_log_id INTO v_error_log_id;

Here, we use utility functions provided by the APEX API to get the call stack and backtrace which tell us where the error occurred in our codebase. We also log the APP_USER that encountered the error, and the timestamp of the error’s occurrence. The final error processing we need to do is to sanitise the error message that is displayed to our end users. We don’t want them to see the exact details of errors, since that may further expose vulnerabilities or issues in our codebase.

       -- apply a 'cleaner' message to be shown to end users
       v_result.message := 'An unexpected error occurred! Contact your application IT support with the error reference number: ' || TO_CHAR(v_error_log_id);
   END IF;

Now that all relevant error information has been captured, the last step is to assign a page item that the error will be displayed with, and return the APEX error result from our function.

   -- Associate a page item if one is not already associated
   IF (v_result.page_item_name IS NULL AND v_result.column_alias IS NULL) THEN
       apex_error.auto_set_associated_item(
           p_error => p_error
           , p_error_result => v_result
       );
   END IF;


   -- commit data changes and return the error result
   COMMIT;
   v_result.additional_info := NULL;
   RETURN v_result;
END;

Don’t forget to assign this function as your application’s error-handling function. This is what tells APEX to run the function when an error occurs.

Now, any APEX error that is thrown unexpectedly will be processed in our function, logged in our database, and reported back to our end users with a clean and concise message.

Next Steps

Error handling can be taken further within Oracle APEX by making use of the full apex_error API. Take a look at the Oracle APEX API Reference to find more details on functions and constants provided by this package and enhance your error handling even further. To see more insights and examples from the RADAPEX team, follow us on LinkedIn where we share regular Oracle APEX and SQLcl content.