Notifying End Users

Notify end users with messages, emails, and push notifications from declarative settings or code.

• Displaying Success and Error Messages
  Show users success and error messages, and customize errors with an Error Handling Function.
• Sending Emails with Templates
  Send email from a native page process or workflow activity.
• Delivering Push Notifications
  Send push notifications from native processes or PL/SQL to users who opted in.

Show users success and error messages, and customize errors with an Error Handling Function.

Use success and error messages to show users when operations have succeeded or failed. To tailor how end users see unexpected errors or ones raised by constraint violations or trigger exceptions, use a custom Error Handling Function.

• Configuring Success and Error Messages
  Configure page process messages with inline text, substitution syntax, or translatable text messages.
• Using Validations for Multiple Errors
  All validation errors show at once so users can review and fix them in one step.
• Auto-Dismissing Success Messages
  Configure success messages to dismiss automatically, with care for users who may need more time.
• Showing Messages with Dynamic Actions
  Show success or error messages from dynamic actions, including eager validation messages returned by server-side checks.
• Showing Messages Programmatically
  Show and clear success or error messages from PL/SQL or JavaScript code.
• Customizing Error Message Display
  Use an Error Handling Function to customize how users see errors raised from database constraints, trigger exceptions, or the APEX engine.

Send email from a native page process or workflow activity.

Use the native Send E-Mail page process or workflow activity to deliver a message. The native action type in task definitions and automations works the same way. When you need to send a mail from business logic, use the SEND function or procedure in the APEX_MAIL package. To monitor the queue of outgoing emails, use the APEX_MAIL_QUEUE view, and to view recently sent email consult the APEX_MAIL_LOG view.

• Setting Up Default From Email Address
  Configure the default From address your app uses for native email features.
• Sending Email Without a Template
  Send emails with inline plain text or HTML and substitution strings.
• Defining Email Template with Directives
  Define an email template with placeholders and directives for conditional email content.
• Using Email Template and Placeholders
  
• Sending Emails from Business Logic
  Send templated email from PL/SQL business logic with APEX_MAIL.SEND.

Send push notifications from native processes or PL/SQL to users who opted in.

Use the native Send Push Notification page process or workflow activity to send a push notification to your app users. APEX automatically delivers them only to users who have opted-in to receive them. The native action type in task definitions and automations works identically. To send a push notification from business logic, use the SEND_PUSH_NOTIFICATION procedure in the APEX_PWA package. To monitor the queue of outgoing push notifications, use the APEX_PUSH_NOTIFICATIONS_QUEUE view.

• Enabling Push Notifications in an App
  Enable PWA and push notification support so users can install your app and opt in to notifications.
• Generating Key Pairs for Push Notifications
  Generate a key pair credential to encrypt and sign push notifications.
• Letting Users Manage Notification Settings
  Add notification settings pages so users can manage push notification opt-in.
• Opting-In to Receive Push Notifications
  Users opt in to push notifications from the user settings menu on each device.
• Pushing a Smile to a Colleague
  Send a push notification to an opted-in user from a native page process.
• Overriding Notification Target Link
  Configure a push notification target link to open a specific app page.
• Pushing Notification from Business Logic
  Send push notifications from PL/SQL business logic with APEX_PWA.SEND_PUSH_NOTIFICATION.

Configure page process messages with inline text, substitution syntax, or translatable text messages.

Your order number is &P113_ORDER_ID. and will ship &P113_SHIP_DATE.

&{ORDER_CONFIRMATION ordnum="&P113_ORDER_ID." ships="&P113_SHIP_DATE."}.

&{ORDER_CONFIRMATION ordnum="&P113_ORDER_ID." ships="&P113_SHIP_DATE."}!RAW.

If multiple page process include a success message, they get concatenated as shown below. The figure shows a success notification with a green check mark icon and concatenated messages from two page processes: "Success Message 1 and Success Message 2".

<br>Success <strong>Message</strong> 2

This produces the result shown below. The figure shows a success notification with a green check mark icon. "Success Message 1" appears on the first line, and "Success Message 2" appears on the second line with Message in bold.

In contrast, multiple page process' Error Messages do not accumulate. The first error halts processing to display the error message as shown below. The figure shows an error notification with an exclamation point inside an orange warning triangle. The first line reads "1 error has occurred", and the second line contains a single bullet: "Houston, we have a problem".

All validation errors show at once so users can review and fix them in one step.

• Validating By Returning True/False Result
  Validate input with a PL/SQL expression that returns true for valid data and false for invalid data.
• Validating By Returning Error Text
  Validate input with a function body that returns null for success or error text for failure.

Configure success messages to dismiss automatically, with care for users who may need more time.

By default, success and error messages stay until the user dismisses them. However, you can opt for success messages to disappear after five seconds. On your app's User Interface Attributes page, just enable the Auto Dismiss Success Messages switch.

The figure highlights the Auto Dismiss Success Messages switch on the Attributes tab of the Shared Components User Interface settings page.

Show success or error messages from dynamic actions, including eager validation messages returned by server-side checks.

Use the native Show Success Message and Show Error Message dynamic actions to show a message to the user in reaction to an event in your page. When necessary, set a Client-side condition to only run the action under certain conditions. For example, suppose you have the following CHECK_EMPNO function in a MESSAGES_APP package that validates an employee id. If the p_empno passed in is valid, it returns null; otherwise, it returns an appropriate error message.

-- In package messages_app
function check_empno(
    p_empno in number)
    return     varchar2
is
begin
    for j in (select null
                from emp
               where empno = p_empno)
    loop
        return null; -- valid
    end loop;
    return apex_lang.get_message('INVALID_EMPNO'); -- invalid
end;

A Change event handler on the P4_EMPNO field shown below can eagerly validate the user's entry without submitting the page. If the value is invalid, it uses the Show Error Message dynamic action step to display the message in the page.

:P4_INVALID_MESSAGE := messages_app.check_empno(:P4_EMPNO);

Notice below that it configures the Items to Submit and Items to Return to send the latest value of P4_EMPNO over to the server and return the value of P4_INVALID_MESSAGE assigned there.

As shown below, the Show Error If Invalid dynamic action of type Show Error Message follows the eager employee ID check. It configures a client-side condition to only execute if P4_INVALID_MESSAGE hidden page item is not null. The message it shows comes from the same page item using the &P4_INVALID_MESSAGE. substitution syntax.

As shown below, the Check Valid Empno validation of type Function Body (returning Error Text) calls the same CHECK_EMPNO function to verify the data at page submit time as well.

This combination yields the behavior shown below. The user sees an error message about an invalid employee ID as soon as they tab out of the Employee ID field.

Show and clear success or error messages from PL/SQL or JavaScript code.

Use APEX_ERROR.ADD_ERROR to signal an error in server-side code with a message the user sees in the notification area. In your page, you can react to events and call JavaScript APIs to show success messages. You can also use JavaScript to both show and clear error messages.

• Adding an Error Message in PL/SQL
  Use APEX_ERROR.ADD_ERROR to add an error message from server-side processing.
• Displaying Messages with JavaScript
  Show and clear page or field-level messages from JavaScript, including errors returned by eager server-side checks.

Use an Error Handling Function to customize how users see errors raised from database constraints, trigger exceptions, or the APEX engine.

• Studying Default Error Message Behavior
  Review default error messages to see why an Error Handling Function can improve what users see.
• Specifying an Error Handling Function
  Define an Error Handling Function to rewrite server-side errors before APEX reports them.
• Examining Error Handling Function Code
  Examine an Error Handling Function that logs internal errors and replaces constraint errors with friendlier messages.
• Logging Error Info for Troubleshooting
  Log error details in a separate transaction so troubleshooting data survives the rollback.
• Experiencing Custom Error Handling
  Test custom error handling with constraint violations, database exceptions, and logged internal errors.

Configure the default From address your app uses for native email features.

The native Send E-Mail facilities default the sender in the From property using the &APP_MAIL. substitution. Configure its value in the Application Email From Address field on the Properties section of your app definition.

Send emails with inline plain text or HTML and substitution strings.

You can send one-off emails using inline text and HTML with substitution strings for the email body. As shown below, the Welcome Packet email from Choosing Descriptive Process Names used this approach. The figure shows the Email Recipient page process selected in the processing tree and highlights:

• Its Type is Send E-Mail
• A To field value of &P34_EMPLOYEE_EMAIL.
• Subject and Body Plain Text are configured
• The SQL statement it uses to attach a ZIP file of welcome packet documents.

Define an email template with placeholders and directives for conditional email content.

Using an email template simplifies the job by separating the formatting from the data and attachments that change with each use. When you send a mail, you can reference an existing template, set values for any placeholders it contains, and include any optional attachments required. The Employee Salary Lottery Winner email template from the example in Defining Automation Actions in Sequence appears below. Notice its Static Identifier that's used when using the APEX_MAIL.SEND API, and that it supports HTML Template Directives for conditional formatting in the email body text and HTML markup.

This email template specifies the HTML markup for the Header and Body sections, and includes placeholders EMPLOYEE_NAME, NEW_SALARY, and NEW_COMMISSION underlined for emphasis in the template Body. Notice the body uses the {if/}⋯{endif/} template directive to conditionally include the phrase about a new commission only if NEW_COMMISSION has a value.

{if NEW_COMMISSION/}, and your commission is now #NEW_COMMISSION#{endif/}

When a native Send E-Mail action, process, or activity uses the email template, as shown below, you set placeholder values for the current message to send using substitution syntax. You can reference the value of columns, page items, workflow variables, application items, or other system substitutions, depending on the context.

Send templated email from PL/SQL business logic with APEX_MAIL.SEND.

The equivalent code to send the same email from an Execute Code action looks like this. No p_from parameter is required because it defaults to using the value of APP_EMAIL. Notice the p_placeholders parameter expects a JSON document with the placeholder names and values. The PLIST_TO_JSON_CLOB function in the APEX_STRING package is handy to produce it. Just pass the name/value pairs as an APEX_T_VARCHAR2 string list. The values in the odd positions are the JSON property names and the ones in even positions are the corresponding values.

apex_mail.send(
   p_to                 => :EMPLOYEE_EMAIL,
   p_template_static_id => 'EMPLOYEE_SALARY_LOTTERY_WINNER',
   p_placeholders       => apex_string.plist_to_json_clob(apex_t_varchar2(
                              'EMPLOYEE_NAME' , :ENAME,
                              'NEW_SALARY'    , :NEW_SALARY,
                              'NEW_COMMISSION', :NEW_COMMISSION)));

Enable PWA and push notification support so users can install your app and opt in to notifications.

To enable push notification support, visit the Progressive Web App section in Shared Components to switch on both Enable Progressive Web App and Enable Push Notifications as shown below.

The figure shows the Progressive Web App tab of the Application Definition page in Shared Components in App Builder. It highlights the two switches you need to enable to send push notifications from your application.

Generate a key pair credential to encrypt and sign push notifications.

Add notification settings pages so users can manage push notification opt-in.

Push notifications only deliver to users who opt-in. As shown below, to add pages and menu entries to your app that let users manage their push notification preferences, click the (Add Settings Page) button in the Push Notifications section of the Progressive Web App app settings page.

Users opt in to push notifications from the user settings menu on each device.

An application user like LUCY manages her settings under the logged-in user drop down menu. Lucy opts-in to receive push notifications by following the sequence shown below. She access the Settings menu, taps on Push Notifications, taps to Enable push notifications on this device, and taps to Allow notifications. The user preference persists until Lucy disables it. However, if you ever change the Key Pair credential, users would need to opt-in again.

Send a push notification to an opted-in user from a native page process.

Push notifications usually flag something important; they can also deliver a laugh. In a Send Chuckle page like the one below, users can "gift" a colleague a random line that might make them smile.

The page uses a Get Random Chuckle page process of type Invoke API to call the RANDOM_CHUCKLE function. It configures the hidden P11_RANDOM_CHUCKLE page item to receive the result. Then, it uses the Send Push Notification page process shown below to deliver the push notification to the username selected in the P11_USERNAME select list.

APEX optimizes delivery to alert only opted-in users, and automatically sends to all their subscribed devices. If you push a notification to a user who has not opted in, no problem. APEX simply ignores it for that user. This saves you from having to keep track of subscribed users yourself.

Since Lucy has opted-in to push notification, she receives the line in a notification on her smartphone and paired watch and smiles thinking about one of her favorite movies.

Configure a push notification target link to open a specific app page.

Send push notifications from PL/SQL business logic with APEX_PWA.SEND_PUSH_NOTIFICATION.

The equivalent code to send the same push notification from an Execute Code page process looks like this. Use optional parameters p_target_url and p_icon_url to override the defaults.

apex_pwa.send_push_notification(
   p_user_name => :P11_USERNAME,
   p_title     => 'Need a Chuckle?',
   p_body      => :P11_RANDOM_CHUCKLE));

Validate input with a PL/SQL expression that returns true for valid data and false for invalid data.

The Error Message you configure on a PL/SQL Expression-type validation shows if the expression evaluates to false. A validation named Can't both start with a 4 uses the expression below. You can read it:

• the data is valid if it's "not the case that both page items' first character is a 4"

not(    substr(:P3_MULTIPLE_OF_TEN,1,1)      = '4'
    and substr(:P3_ODD_MULTIPLE_OF_FIVE,1,1) = '4')

   substr(:P3_MULTIPLE_OF_TEN,1,1)      != '4'
or substr(:P3_ODD_MULTIPLE_OF_FIVE,1,1) != '4'

Cannot both start with a %first_digit

&{CANNOT_BOTH_START first_digit="4"}.

The figure shows the Can't both start with a 4 validation selected in the processing tree and highlights the PL/SQL Expression it uses to test validity as well as the Error Message configured to display when the validation fails by evaluating to false.

Validate input with a function body that returns null for success or error text for failure.

A validation can also use type Function Body (returning Error Text). If it returns null, then the validation is a success. Any other return value is used as the error message text. It can use APEX_LANG.GET_MESSAGE as shown below to return a translatable message with placeholders. The validation named At least one over 100 uses this approach.

At least %mincount must be greater than %limit

return case
         when     :P3_MULTIPLE_OF_TEN < 100
              and :P3_ODD_MULTIPLE_OF_FIVE < 100
         then
            apex_lang.get_message('AT_LEAST_N_ABOVE_M',
                                  apex_t_varchar2('mincount','1',
                                                  'limit'   ,'100'))
end;

Use APEX_ERROR.ADD_ERROR to add an error message from server-side processing.

It halts further processing, abandons data changes, and shows the error in the notification area.

• Enforcing Rules with Read-Consistency
  Use a page process after the data-saving processes to validate pending changes before APEX commits them.
• Applying Aggregate Constraints with SQL
  Use SQL aggregation in a page process after the data-saving processes to reject changes that violate cross-row rules.

Show and clear page or field-level messages from JavaScript, including errors returned by eager server-side checks.

Use the apex.message.showPageSuccess JavaScript function to show a success message as part of custom client-side logic. apex.message.showErrors lets you display one or more errors inline on a particular page item or at page-level. Use the companion apex.message.clearErrors to clear all errors, or just the error for a specific page item. To explore an example, combine clearErrors and showErrors with an Execute Server-side Code dynamic action to eagerly validate user data entry and show field-specific error messages before the user submits the page.

Start by adding the following CHECK_DEPTNO function to complement the CHECK_EMPNO from Showing Messages with Dynamic Actions. If p_deptno is a valid department ID, it returns null; otherwise it returns an appropriate error message.

-- In package messages_app
function check_deptno(
    p_deptno in number)
    return     varchar2
is
begin
    for j in (select null
                from dept
               where deptno = p_deptno)
    loop
        return null; -- valid
    end loop;
    return apex_lang.get_message('INVALID_DEPTNO'); -- invalid
end;

The goal is eagerly validating separate P5_EMPNO and P5_DEPTNO form fields with error messages showing inline with the relevant field as shown below. The figure shows a page with three fields. The user has entered 1234 in Employee ID and 99 in Department ID, then tabbed into Vacation Days. Below Employee ID, a red field-level error appears with a small red circular icon with a white diagonal slash: "Enter a valid Employee ID". Below Department ID, a similar error appears: "Enter a valid Department ID".

apex.message.clearErrors('P5_EMPNO');

:P5_INVALID_MESSAGE := messages_app.check_empno(:P5_EMPNO);

Finally, the Show Error If Invalid action of type Execute JavaScript Code finishes the recipe by conditionally showing the field-level error with the showErrors call below. It passes the error message returned by the server-side check using v$('P5_INVALID_MESSAGE') and indicates the error is inline on the P5_EMPNO item. Notice its Client-side condition is set to execute only if P5_INVALID_MESSAGE is not null.

apex.message.showErrors( [
    {
        type:       "error",
        location:   [ "inline" ],
        pageItem:   "P5_EMPNO",
        message:    $v('P5_INVALID_MESSAGE')
    }
] );

To repeat the same field-level validation on the second field P5_DEPTNO, just reuse the same recipe. Add a Check Valid Deptno validation of type Function Body (returning Error Text) that calls CHECK_DEPTNO. In the Eagerly Check Deptno server-side code, pass P5_DEPTNO to the CHECK_DEPTNO function instead, then show the error on P5_DEPTNO field. You can reuse the same hidden P5_INVALID_MESSAGE field.

Review default error messages to see why an Error Handling Function can improve what users see.

alter table emp
add constraint comm_less_than_twice_sal
check (comm < 2 * sal);

ORA-02290: check constraint (SCHEMA.COMM_LESS_THAN_TWICE_SAL) violated

No Primary Key item has been defined for form region Employee

Define an Error Handling Function to rewrite server-side errors before APEX reports them.

The APEX engine calls your error handling function before reporting any server-side error. When appropriate, your function can change the error message to something more user-friendly. The function can have any name, but it must have the single parameter and return type as shown below. The APEX engine passes it a T_ERROR record, and it returns a T_ERROR_RESULT one. Optionally, define the function in a package like MESSAGES_APP. Then, configure a page to use it by setting the Error Handling Function property of the page to the function's name as shown below.

-- In package messages_app
function apex_error_handling (
    p_error in apex_error.t_error )
    return     apex_error.t_error_result;

Examine an Error Handling Function that logs internal errors and replaces constraint errors with friendlier messages.

The following APEX_ERROR_HANDLING function initializes the error result, then checks to see if the error is an APEX internal error. For uncommon internal errors, it calls the ADD_ERROR_LOG helper procedure to log the error. It then sets user-facing error message by assigning the alternate text to the message field in the l_result return record. It uses a generic message with the name UNEXPECTED_INTERNAL_ERROR.

For other errors, it checks p_error.ora_sqlcode for a database constraint code. If it finds one, it calls EXTRACT_CONSTRAINT_NAME in the APEX_ERROR package. Using that constraint name, say XXX, it calls APEX_LANG.GET_MESSAGE with message name CONSTRAINT_XXX. If that message name exists, it uses the the corresponding translatable text. Otherwise, GET_MESSAGE returns the original message name itself. So if the returned message is different from the message name passed in, it sets that text as the error message to show the user.

If no other path changed the error message yet, it simplifies the error by calling GET_FIRST_ORA_ERROR_TEXT in the APEX_ERROR package. Then, finally, it returns the l_result to the APEX engine.

-- In package messages_app
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);
    l_error_message   varchar2(255);
begin
    -- Always start by initializing the error result
    l_result := apex_error.init_error_result(p_error);
    -- For uncommon internal errors, log and show a generic message instead
    if p_error.is_internal_error then
        -- only log errors that are not common ones
        if not p_error.is_common_runtime_error then
            add_error_log( p_error );
            -- Change the error message to a generic one
            l_result.message         := apex_lang.get_message('UNEXPECTED_INTERNAL_ERROR');
            l_result.additional_info := null;
        end if;
    else
        -- Always show the error as inline error
        l_result.display_location := apex_error.c_inline_in_notification;

        -- 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
        --
        -- try to get a friendly error message from our constraint lookup table.
        -- If the constraint in our lookup table is not found, 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
                -- Try to find text message CONSTRAINT_XXX for constraint name XXX
                l_error_message := apex_lang.get_message('CONSTRAINT_'||l_constraint_name);
                -- If we found a text message (message will NOT just be the name itself)
                if l_error_message != 'CONSTRAINT_'||l_constraint_name then
                    l_result.message := l_error_message;
                end if;
            exception
                -- not every constraint has to be in our lookup table or
                when no_data_found then null;
            end;
        end if;

        -- If its an error that we didn't find a message for yet, simplify it
        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;
    end if;
    return l_result;
end apex_error_handling;

Log error details in a separate transaction so troubleshooting data survives the rollback.

The ADD_ERROR_LOG helper procedure receives the T_ERROR record the APEX engine passed your error handling function. It writes useful information this record contains to the following MESSAGE_APP_ERRORS table.

create table messages_app_errors
(
    id number generated by default on null as identity not null,
    err_time        timestamp(6) default systimestamp,
    app_id          number,         /* Application ID           */
    app_page_id     number,         /* Page ID                  */
    app_user        varchar2(512),  /* Application User         */
    user_agent      varchar2(4000), /* Browser User Agent       */
    ip_address      varchar2(512),  /* Browser IP Address       */
    ip_address2     varchar2(512),  /* ORDS IP Address          */
    message         varchar2(4000), /* Error Message            */
    page_item_name  varchar2(255),  /* Page Item Name           */
    region_id       number,         /* Region ID                */
    column_alias    varchar2(255),  /* Column Alias             */
    row_num         number,         /* Row Number               */
    apex_error_code varchar2(255),  /* APEX Error Code          */
    ora_sqlcode     number,         /* Oracle SQL Code          */
    ora_sqlerrm     varchar2(4000), /* Oracle SQL Error Message */
    error_backtrace varchar2(4000), /* Error Backtrace          */
    procedure_name  varchar2(1000), /* Procedure Name           */
    error_text      varchar2(4000), /* Error Text               */
    constraint message_app_errors_id_pk primary key (id)
);

The procedure operates in a separate transaction by including the following in its declare section.

pragma autonomous_transaction;

This requires also that it perform an explicit commit, which it does at the end. This combination ensures the logged row is saved even though the APEX engine performs a rollback to abandon pending data changes when encountering an error.

-- In package messages_app
procedure add_error_log (
    p_error           in apex_error.t_error,
    p_procedure_name  in varchar2 default null,
    p_error_text      in varchar2 default null)
is
pragma autonomous_transaction;
begin
    -- Remove old errors
    delete from messages_app_errors where err_time <= current_timestamp - 21;
    -- Log the error.
    insert into messages_app_errors (
        app_id,
        app_page_id,
        app_user,
        user_agent,
        ip_address,
        ip_address2,
        message,
        page_item_name,
        region_id,
        column_alias,
        row_num,
        apex_error_code,
        ora_sqlcode,
        ora_sqlerrm,
        error_backtrace,
        procedure_name,
        error_text)
    select v('APP_ID'),
        v('APP_PAGE_ID'),
        v('APP_USER'),
        owa_util.get_cgi_env('HTTP_USER_AGENT'),
        owa_util.get_cgi_env('REMOTE_ADDR'),
        sys_context('USERENV', 'IP_ADDRESS'),
        substr(p_error.message,0,4000),
        p_error.page_item_name,
        p_error.region_id,
        p_error.column_alias,
        p_error.row_num,
        p_error.apex_error_code,
        p_error.ora_sqlcode,
        substr(p_error.ora_sqlerrm,0,4000),
        substr(p_error.error_backtrace,0,4000),
        p_procedure_name,
        p_error_text
    from dual;
    commit;
end add_error_log;

Test custom error handling with constraint violations, database exceptions, and logged internal errors.

With the error handling function in place, suppose you add the following trigger on the EMP table to simulate two additional kinds of database errors. One comes from an unexpected ZERO_DIVIDE error the trigger encounters if the user sets COMM to the value 1234. The second error is a custom exception from RAISE_APPLICATION_ERROR. This happens when a user sets COMM to 1235.

create or replace trigger emp_conditional_error_test
before insert or update on emp
for each row
begin
    -- Intentionally cause a divide-by-zero error for educational purposes
    if :new.comm = 1234 then
        if :new.comm / 0 > 1 then
            null;
        end if;
    -- Raise a custom exception message from the database
    elsif :new.comm = 1235 then
        raise_application_error(-20020,'Some error from the database');
    end if;
end;

Now, if the user enters a commission greater than twice the employee's salary, they see the custom error message provided by the translatable text message named COMMISSION_COMM_LESS_THAN_TWICE_SAL.

Commission must be less than twice salary

Some error from the database

divisor is equal to zero

An unexpected internal application error has occurred.

When users report the problem, you check a page only application administrators can see that shows the logged error information in the MESSAGES_APP_ERRORS table. As shown below, you see immediately that the problem relates to a primary key field setting in page 8 of application 100.

Clicking the details link, you see additional error info your error handling function logged to the table. Using these logged details, you quickly apply the fix, deploy a new version to production, and get users back working happily again.

Use a page process after the data-saving processes to validate pending changes before APEX commits them.

When a user submits a page, page processes save any changes in the database. However, the APEX engine only commits the pending changes after all page submission processing succeeds. If any page process signals an error, this "vetoes" all pending changes so nothing is saved.

This gives you a powerful way to validate pending changes. Just add a page process after the ones that save user-submitted data changes. It can perform complex data validation using simple SQL statements. Oracle's read consistency model lets any queries it performs "see" the pending-but-not-yet-committed changes along with other unmodified data. Any aggregations like SUM or COUNT you perform always get an accurate answer. You can then reject the changes if an aggregate condition is invalid, just by signaling an error with an actionable message that helps the user fix the situation.

Use SQL aggregation in a page process after the data-saving processes to reject changes that violate cross-row rules.

Consider a constraint that the total monthly compensation for all employees in a department cannot exceed a limit of 12000. The following SQL query using SUM, GROUP BY, and HAVING returns any departments that violate the rule. The COALESCE function returns its first non-null argument. So a NULL salary or commission is treated as a zero (0) while computing the total compensation by department.

-- Return any departments over monthly compensation limit
select deptno,
       sum(coalesce(sal,0) + coalesce(comm,0)) as total_comp
  from emp
 group by deptno
having sum(coalesce(sal,0) + coalesce(comm,0)) > 12000

The following CHECK_DEPARTMENTS_OVERBUDGET procedure uses this query in a FOR loop to check for offending departments. For any it finds, it adds their department number and total compensation amount to the l_overbudget string list using APEX_STRING.PUSH.

Departments exceed the %amount budget: %department_list

It passes values for the two message placeholders amount and department_list when calling APEX_LANG.GET_MESSAGE to retrieve the text.

-- In package messages_app
procedure check_departments_overbudget
is
    l_overbudget apex_t_varchar2;
begin
    for j in (select deptno,
                     sum(coalesce(sal,0) + coalesce(comm,0)) as total_comp
                from emp
               group by deptno
              having sum(coalesce(sal,0) + coalesce(comm,0)) > 12000
               order by deptno)
    loop
        apex_string.push(
           l_overbudget,
           apex_string.format('%s (%s)',j.deptno,j.total_comp));
    end loop;
    if l_overbudget is not null then
        apex_error.add_error (
                p_message => apex_lang.get_message(
                                'OVERBUDGET_DEPARTMENTS',
                                apex_t_varchar2(
                                   'department_list',apex_string.join(l_overbudget,', '),
                                   'amount'         ,'12000')),
                p_display_location => apex_error.c_on_error_page);
    end if;
end check_departments_overbudget;

The result looks like the figure below at runtime.

In the figure, a user editing an interactive grid of salaries and commissions clicks (Save Changes), and any offending departments with total compensation exceeding the 12000 monthly limit appear in the error message. No changes are saved. The user can correct their data entry and try again. The error notification error shows one error message with the text: "Departments exceed the 12000 budget: 10 (18750), 30 (32100)"