Page Process – Dive Into APEX

Pushing the PWA Envelope

If you have a smartphone, you’re already familiar with push notifications. They’re the timely alerts with some useful info from your favorite apps. In 23.1, your APEX solutions can send them, too, using a page process or send_push_notification() API. You simply set the message details and APEX securely notifies the user on all their subscribed devices, if any.

In this article, see a sample app in action, how to try it out yourself, and where to download the slides from my recent APEX Alpe Adria conference talk with more in-depth info about push notifications in APEX.

Overview of the Sample App

The sample app uses APEX’s application access control with roles for Customer and Staff. App pages use an appropriate authorization scheme so customers can request reimbursements while staff members approve them. The app declaratively enables Progressive Web App (PWA) installation and push notifications, and the overview video below shows you how the app works on iPhone and Mac desktop PWAs for a customer user BO and a backoffice user PAT.

Regenerating the Signing Key Credentials

After downloading and importing the sample app, the first required setup step is regenerating the public/private key pair credentials. It’a is a one-time step that’s necessary the first time an APEX app using push notifications is imported into a new workspace. This is a new kind of credential used to cryptographically sign the push notifications your app sends.

In practice, it’s a one-click operation. Under Shared Components > Progressive Web App > Push Notifications, click the (Regenerate Credentials) button, and then in the confirmation dialog that appears click (Regenerate Credentials) again to confirm. Ok, you got me. It’s a two-click operation! You can regenerate the credentials any time in the future as well, but be aware that doing so invalidates and removes any existing user push notification subscriptions. Therefore, should you decide later to regenerate the credentials, your users will need to opt-in again to receive your app’s push notifications.

Creating the Two Sample Users

The sample app depends on a backoffice staff user named PAT who is configured as the approver on the Reimbursement Approval task definition and another user BO who is a customer. As shown in the demo video above, customer BO uses the app to request reimbursement of an expense, then backoffice user PAT approves or rejects the request, which results in sending the requesting user a push notification to alert them of the outcome.

If you have recently tried the Sample Approvals app from the gallery, you might already have workspace users PAT and BO, but if not then you’ll need to create them. Login to your APEX workspace as a workspace admin user and click the Administration (i.e. “person with wrench”) icon in the toolbar and choose the Manage Users and Groups option. Add the missing account(s) on this page using the Create User button.

Assigning Sample Users an Application Role

Once users PAT and BO exist, next you need to assign them an appropriate application role so that the staff member user PAT sees the approval pages and the customer BO sees the reimbursement request pages. To do this, in the context of the sample application in the App Builder, click Shared Components > Application Access Control. Use the (Add User Role Assignment) button twice on this page to add two user role assignments:

PAT -> Staff
BO -> Customer

HTTPS & Trusted Certificate Requirement

Keep in mind if you’re trying the sample on your own APEX instance that both on-device PWA installation as well as subscribing to push notifications depend on a secure, trusted connection. This means your desktop or mobile browser needs to access the APEX app over HTTPS and the client device must trust the server’s security certificate. If your APEX is running plain HTTP or it’s using a self-signed certificate that you haven’t configured your client device to trust, exploring the sample won’t work as expected. In that case, I recommend trying it on apex.oracle.com or your Oracle Cloud always free tier APEX instance instead.

Installing the Sample App as a PWA

The public APEX PWA Reference App’s Push Notifications page documents the compatibility matrix of supported operating systems and devices. If you are an iPhone user, notice that subscribing to push notifications on iOS and iPadOS require version 16.4 or later, as well as your installing the app as a PWA first. Other supported combinations allow push notifications either from the browser or when installed as a PWA. In the video above, I used Chrome on MacOS Ventura to install the PWA for user BO, Microsoft Edge to install the PWA for user PAT, and an iPhone 11 Plus running iOS 16.4 to install the mobile PWA for BO. You can use any of the supported combinations highlighted in the compatibility matrix.

I recorded the demo video on an APEX instance with instance-level settings Allow Persistent Auth set to Yes and Rejoin Sessions set to Enabled for All Sessions, so users can stay logged in for a month by default and where tapping on the push notification does not require the user to login again to see the related detail information in the APEX PWA application.

Opting-In to Receive Push Notifications

As shown in the demonstration video above, each user of your app needs to opt-in to receive push notifications from your app. And they need to do this on each device where they want to receive the notifications. When creating a new app with the Push Notification feature enabled in the Create App wizard, APEX generates a user settings modal drawer page containing the link to the Push Notification Settings page. For an existing app, there is a button on the Push Notifications page of the PWA app settings to generate the user settings page with a single click. At runtime, if the settings page shows a “Not Supported” badge, check to make sure you’re using HTTPS and a trusted certificate. If tapping or clicking on the Enable push notifications checkbox produces an error, that’s a signal you probably forgot to regenerate the push notification key pair credentials after importing the app the first time.

Using an App Page as Push Notification Target URL

When sending a push notification, you can configure a target URL that the device uses when the user taps or clicks on the notification. It must be an absolute URL, so for example the Reimbursement Approval task definition in the sample app contains an action that executes in response to the Complete event. Its action PL/SQL code sends the push notification about the task approval or rejection using the following code:

apex_pwa.send_push_notification(           
    p_user_name  => :CREATED_BY,
    p_title      => 'Reimbursement '||initcap(:APEX$TASK_OUTCOME),
    p_body       => 'Your reimbursement of ' || :AMOUNT || 
                    ' from ' || :RECEIPT_FROM || 
                    ' was ' || lower(:APEX$TASK_OUTCOME)||'.',
    p_target_url => apex_util.host_url||
                    apex_page.get_url(
                       p_page   => 'reimbursement-notification',
                       p_items  => 'p7_id',
                       p_values => :APEX$TASK_PK)          
);

Notice how the value being passed to the p_target_url parameter prefixes the result of the apex_page.get_url() result by the apex_util.host_url expression. This ensures that the URL is a fully-qualified absolute URL. When using a declarative Send Push Notification page process, the APEX engine handles this for you, so this tip only pertains to the send_push_notification() API.

When the target URL is a page of your APEX app, the page must have the following properties configured:

Authentication = Public
Deep Linking = Enabled
Rejoin Sessions = Enabled for All Sessions
Page Protection = Arguments Must Have Checksum

This public target page will typically use a Before Header branch to redirect to an authenticated page in the app, passing along appropriate parameters to show the user the expected detail information for the notification. When combined with the use of APEX’s persistent authentication “Remember me” functionality, this combination gives the most seamless user experience. Page 7 in the sample app, whose alias reimbursement-notification appears in the send_push_notification call above, meets all of these requirements and performs the forwarding to the authenticated page 6, passing along the reimbursement request id to show the end-user the request details.

Granting Outbound Network ACLs for Push Services

The APEX engine sends push notifications by invoking secure notification REST services from Apple, Google, Microsoft, and Mozilla depending on the subscribing user’s device. If you try the sample app on apex.oracle.com, you won’t have to worry about granting outbound network access for these notification service domains, however it’s a task you must perform when you use push notifications on your own APEX instance. In case you need it, the following PL/SQL block is an example of how to grant the appropriate ACLs to the push notification REST API domains and optional a proxy server if you are behind a corporate firewall. If your APEX applications are already using REST services, you will likely already be familiar with these steps. It’s included here for reference:

-- Run as SYS or DBA user
declare
    l_principal varchar2(20) := 'APEX_230100';
    l_proxy varchar2(100)    := null; -- e.g. 'proxy.example.org'
    l_proxy_port number      := 80;
    l_hosts apex_t_varchar2  := apex_t_varchar2(
                                '*.push.apple.com',
                                '*.notify.windows.com',
                                'updates.push.services.mozilla.com',
                                'android.googleapis.com',
                                'fcm.googleapis.com');
    procedure add_priv(p_priv varchar2, p_host varchar2, p_port number) is
    begin
        dbms_network_acl_admin.append_host_ace (
            host       => p_host, 
            lower_port => p_port,
            upper_port => p_port,
            ace        => 
                xs$ace_type(privilege_list => xs$name_list(p_priv),
                            principal_name => l_principal,
                            principal_type => xs_acl.ptype_db));
    end;
    procedure add_priv_resolve(p_host varchar2) is
    begin
        dbms_network_acl_admin.append_host_ace (
            host       => p_host,
            ace        => 
                xs$ace_type(privilege_list => xs$name_list('resolve'),
                            principal_name => l_principal,
                            principal_type => xs_acl.ptype_db)); 
    end;
begin
    if l_proxy is not null then
        add_priv('connect',l_proxy,l_proxy_port);
        add_priv_resolve(l_proxy);
        add_priv('http',l_proxy,l_proxy_port);
    end if;
    for j in (select column_value as hostname from table(l_hosts)) loop
        add_priv('connect',j.hostname,443);
        add_priv_resolve(j.hostname);
        add_priv('http',j.hostname,443);
    end loop;
    commit;
end;

Configuring Wallet to Validate Certificates

On your own APEX instance, in addition to the outbound REST service ACLs required for push notification, you may also need to add certificates into the wallet your instance is using for validating secure HTTP communications. If you are running APEX in the Oracle Cloud, this step should not be necessary. However, on your own instance you may find my colleague Daniel Hochleitner’s open-source Oracle CA Wallet Creator script useful for that purpose.

Downloading the Sample App

You can download the sample app from here.

Downloading My Slides

You can download the slides from my recent talk on this topic at the APEX Alpe Adria 2023 conference from here: APEX 23.1: Native Push Notifications & Easy Background Processing.

Page Processing Power Unchained

APEX 23.1 execution chains greatly expand the power of your page processes. This new process type lets you assign a name to a group of child processes that will execute in sequence if the parent’s condition is true. Any of the chain’s child processes can be another chain if needed, so you can organize page logic into a more self-documenting tree structure.

In addition, you can configure any branch of this “processing tree” to run in the background with the flick of a switch in the Property Editor. Your end-users move on to tackle other tasks in your app while longer-running work happens behind the scenes. In this article, you’ll learn more about this new feature, how to try out a sample app, and where to download the slides from my recent APEX Alpe Adria conference presentation about it.

Chains Enable Increased Clarity

APEX developers know that their page processes execute in sequence. But many page processes, like the three in the figure below, have a Server-side Condition configured. When a teammate opens a page like this, to understand the “flow” of the page logic they must select each conditional page process and study its conditional expression.

Three conditional page processes: which runs when?

The figure below shows how introducing two execution chains with meaningful names helps to clarify the intent of the processing logic. If we are rendering the page in order to duplicate a Venue record, then two of the page processes indented inside that “branch” of the tree will execute. Otherwise, the Initialize from Venue process will run. Notice how we’ve “refactored” common conditions on the individual page processes to instead be associated with the owning parent chain.

“Refactored” page processing logic using well-named execution chains with common conditions

Defining an Execution Chain

To define an execution chain, just create a page process and set its type to Execution Chain. To add child processes to it, choose Add Child Process from the chain’s right-click context menu. As shown in the figure below, for any page process you select in the component or processing tab, its Execution Chain property shows the name of the execution chain it belongs to, or displays None if it is a top-level page process. To change which execution chain a page process belongs to, simply use this Execution Chain select list in the Property Editor and change it to the new parent execution chain name.

The *Execution Chain* property of a page process shows the parent chain it belongs to

Switching On Background Execution

While APEX Automations let you schedule periodic background jobs, beyond sending email they are a essentially a code-focused affair. Execution chains extend your processing capabilities deeper into the declarative realm. You can organize page processes of any type into chains whose sequence of child processes either run immediately in the user session or else in the background. A chain can send an email, send a push notification, perform data loading, initiate a human approval task, execute PL/SQL code, or engage any other page process type, including custom plug-in types. Used with the InvokeAPI page process type, it offers a simple way to orchestrate the conditional execution of APIs implemented in PL/SQL packages or as REST services.

When running in the background, your chain’s child processes automatically have access to a cloned copy of all the session state, and they can update page item values in the usual way. You can configure the chain to move selected temporary files to the background session, or to make a copy if both the foreground and background need to access a file for some reason. The only difference to be aware of is that, by design, changes to session state performed by page processes running in the background are not reflected in the end user’s application session that originally initiated the background execution chain.

A Deeper Processing Tree from the Sample

The figure below shows the processing tree of the Department page in the sample app we explore below. Notice how we can read the processing logic like an outline to more easily understand what it’s doing. After first processing the Department form, if the (Process) button was pressed, it first checks whether the current department is being processed already. If it’s not being processed the Else if Deptno Available to Process… chain runs. It starts by registering the username who is kicking off the processing of the current department. Then it proceeds to run the Process Department in Background chain. Notice in the Property Editor that the Execute in Background switch is turned on for this chain. It’s configured to return the unique ID of the execution background process into the P3_EXECUTION_ID page item.

Page processing tree with multiple levels and one branch marked to Execute in Background

Once the background processing is enqueued — which happens very quickly — the current chain proceeds to execute the next child process at the same level: If Background Process Succeeded… This process can test whether the value of P3_EXECUTION_ID is not null to conclude that enqueuing the background work was successful. Finally, the Close Dialog process closes the modal dialog. In short, the rule of thumb is that every page process at the same level of the tree runs sequentially, with background chains getting enqueued to be executed as soon as is feasible. We explore below why that might not be immediately, due to limits imposed on concurrent background jobs.

Overview of the Sample App

The short video demonstration below shows off the sample app for this article. The Departments page lets you open a modal drawer to edit the department. That Department page also contains a (Process) button to trigger the execution of the When Process Button Pressed… action chain shown above. This action chain’s “tree” of child processes ultimately contains the Process Department in Background action chain that is configured to run in the background. The Processing Status page shows the progress of each background process. The Processing History page shows average running time of the background processes, and the average time background processes wait before starting.

Short video demonstrating the sample app

What’s Going on in Process Department?

The long-running Process Department process in the sample calls the PL/SQL procedure PROCESS_DEPTNO in the EBA_DEMO_BG_PROC package using InvokeAPI. This code simulates a long-running process that will take between 60 and 100 seconds. Notice it uses the SET_PROGRESS() procedure in the APEX_BACKGROUND_PROCESS package to report progress of the background processing to the APEX engine. The Processing Status page references this information about units of total work and units completed so far using the APEX dictionary view APEX_APPL_PAGE_BG_PROC_STATUS.

procedure process_deptno(p_deptno number) 
is
    c_total_secs constant integer := 60 + round(dbms_random.value(0,40)); 
    c_steps      constant integer := 10;
begin 
    -- Simulate long-running process taking 60-100 secs 
    apex_background_process.set_progress(p_totalwork => c_steps,
                                         p_sofar     => 0); 
    for j in 1..c_steps loop 
        dbms_session.sleep(round(c_total_secs/10)); 
        apex_background_process.set_progress(p_totalwork => c_steps,
                                             p_sofar     => j); 
    end loop; 
end;

Imposing Limits on Background Processing

APEX lets developers set a per-session limit on the number of times an end user can simultaneously enqueue a background execution chain. You accomplish this by setting a value for the Executions Limit property of the chain. It also allows configuring both an application-level and workspace-level maximum number of concurrent background processes. The per-session limit will raise an error if a user tries to exceed the limit. In contrast, the limit on concurrent executions throttles the processing speed of the background execution chain jobs queue. As shown in the demo video above, background processes may have to wait longer to get scheduled and execute. To show the difference, the figure below shows the same sample app running on an APEX instance where the concurrent background processing limit is at least three or higher. You can see that the user scheduled three DEPT rows to be processed and they are all in some stage of execution progress simultaneously.

With higher concurrent background process limits, multiple background processes run in parallel

An obvious result of a system with higher resource limits is that enqueued background execution chain jobs get scheduled and execute more quickly. As shown in the figure below, the average time a job waits to start is around one second on the system I used. In the demo video I recorded using apex.oracle.com, the average wait-to-start time was over a minute where resources were more constrained.

Background execution chain process jobs start quickly when concurrency limits allow it

Handling Failure to Enqueue Background Process

On page 3 in the sample app, the Process Department in Background execution chain is configured with an Executions Limit property value of 3. This means a user session can initiate a maximum of three background execution chain jobs. An attempt to launch a fourth one will produce the error shown in the figure below.

Error message when user tries to launch a fourth simultaneous background process

We configure the error message using the Error Message property on the execution chain. However, this sample application inserts a row into the table EBA_DEMO_BG_PROC_PROCESSES to track which user is processing which department, and later the background execution chain process updates that row with the execution chain id once the background job gets scheduled and begins to run. In the case that the user encounters an error while the background execution chain is enqueued due to having hit the Executions Limit, I need to delete the tracking row in the EBA_DEMO_BG_PROC_PROCESSES table that had been inserted previously. I accomplished this by registering the page 3 error handler function to use the p3_handle_bg_processing_error function below (in the eba_demo_bg_proc package). It checks whether the error encountered relates to the Process Department in Background process, and if so, it calls unregister_bg_proc_for_deptno() to delete the tracking row.

function p3_handle_bg_processing_error(p_error apex_error.t_error)   
return apex_error.t_error_result is  
    l_result apex_error.t_error_result;   
begin   
    l_result := apex_error.init_error_result(   
                p_error => p_error);  
    if is_process_error(p_error,'Process Department in Background') then   
        unregister_bg_proc_for_deptno(   
            p_deptno  => V('P3_DEPTNO'),   
            p_orig_id => V('P3_ONGOING_WORK_ID'),   
            p_success => false   
        );   
    end if;  
    return l_result;   
end;

Getting the Sample App

Download the sample app from here. Before running the application, first ensure you’ve installed the DEPT/EMP sample dataset. If you’re not sure, choose SQL Workshop > Utilities > Sample Datasets to verify and install it if needed.

Downloading My Slides

You can download the slides from my recent talk on this subject at the APEX Alpe Adria 2023 conference from here: APEX 23.1: Native Push Notifications & Easy Background Processing.