How to Configure Push Notifications in Progressive Web App in Oracle APEX

In this blog, we learned about how to create a Progressive Web App in Oracle Apex.

Progressive Web App offers many benefits including an app experience very close to a Native App, one of which is a Push Notification feature. Take an example of an Android or iOS App; we all have seen many of the Native apps on these platforms pushing various Push Notifications to us, whether to inform you that your package is out for delivery or just to deliver the daily news bites to you.

Progressive Web App (PWA) brings the Push Notifications feature to the Apex Apps. Let's see how we can configure and use it.

We can enable Push Notifications in two ways - While creating a brand new App or In an existing App

New App:

- Create a new App and click on 'Use Create App Wizard'

- Let's check the boxes 'Install Progressive Web App' and 'Push Notifications'

Existing App:

- Navigate to Shared Components

- Click on Progressive Web App

- Scroll down to Push Notifications section

- Enable Push Notifications and click on Generate Credentials

- Generate Credentials will automatically generate a new key pair credentials for your application which is needed so that the end users can subscribe to push notifications securely.

- Once done, it will update the Credentials and Setting Page details

We have now enabled PWA and Push notifications in our App.

Now, lets see how we can send the actual Push Notifications.

We can send Push Notifications via two methods - API call and Page Process

1. API call method:

The two main APIs we need are: 

apex_pwa.send_push_notification - This API creates the actual Push Notification and adds it to the Queue

apex_pwa.push_queue - This API Pushes all the Notifications that are ready to be processed in the Queue

- Let's create a new Button on our Home page and select Create Dynamic Action option

- Let's name the Dynamic Action as PushNotification and set the True Action as Execute Server-side Code

- In the PL/SQL Code editor, enter below code snippet. 

Replace Your_APEX_UserName and Your_APEX_AppURL with actual values from your App.

BEGIN

apex_pwa.send_push_notification (
            p_application_id => :APP_ID,
            p_user_name      => '',
            p_title          => 'Test Notification',
            p_body           => 'This is a test notification.',
            p_icon_url       => '#APP_FILES#icons/app-icon-512.png',
            p_target_url     => ''
);

apex_pwa.push_queue;

EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

To get the actual value for p_icon_url, follow these steps:

Navigate to Shared Components

Click on Static Application Files option

Here, you will see a  types of files and their Reference values. Pick any one and copy it's Reference value and use this for p_icon_url parameter in above API call.

- Update the Dynamic Action code and click Ok

We have now configured the Push Notification via API.

Now, let's see how the App looks like and how the Push Notification is delivered

- Let's open the App on our smartphone

- Click on Download icon to Install the App

- Install the App

- Now, go to Settings section in the installed App

- Here, you will see Push Notifications option which will be set to Off by default

- Let's Enable the Push Notifications option

- Based on your device (Android or iPhone), this step will show a pop-up asking for your confirmation in order to enable Notifications for this App on your device.

For example, here's how it will be shown on an Android device

- Click Allow

Now, let's go back to Home screen and click Send Notification button

- This will send the Test Push Notification to your smartphone based on the details mentioned in the API call

2. Page Process method:

Now that we've seen how to send Push Notifications through API call, let's take a look at second option which is through a Page Process

- Let's create a New Button on our page named PageProcessButton

- Now, navigate to Processes section in the App and create a new process named 'PushNotification' under After Submit section

- Change the Type to 'Send Push Notification'

- Under Settings section, set the values for To (APEX User Name), Title and Body of the Push Notification

- Save

Now let's see how this notification looks like

- Navigate to the App on your device

- Click on the new button 'Page Process Notification'

- And we should see our new Page Notification on our device

As shown above, notifications sent through the Page Process provide limited customization capabilities. This approach is well-suited for scenarios where a simple notification is sufficient. 

However, when greater control over push notifications is required, or when notifications need to be driven by custom business logic using data from a database, the API-based approach is the preferred option.

Read More

How to create Background Processes thru Automation feature in Oracle APEX

In the older releases of Oracle APEX, it was not easy to schedule a background process in Oracle APEX. If there was a requirement to schedule a background job, we would need to write custom code using DBMS_SCHEDULER API which was introduced in Oracle 10g.

Today's Oracle APEX has a built-in feature to create Automations which is certainly a much more developer friendly solution.

The Automation feature can be used for variety of use cases such as Sending Email Notifications as per a set schedule, Sending Push Notifications at certain interval, submitting/running long running processes in background, sending alerts by monitoring critical business activities etc.

Let's see what are the steps to create APEX Automations.

- Create a new App

- Navigate to Shared Components and locate Workflows and Automations section and click on Automations

- Click Create

- This will initiate a Wizard to create an Automation

- Here, we can name our Automation and also mention whether it will be a Scheduled automation or it will execute On-Demand

Let's select Scheduled for our use case

We will get an option to decide whether the Action gets triggered based on the output of a query Or it should Always trigger.

And in the end, we can define the Schedule of this Automation

- Now we will be presented with a screen where we need to mention the table name based based on which the Automation will execute and we also mention that whether to run it if Rows are returned or Not Returned.

- But what if we want to have a flexibility and mention a custom query which will decide whether the Automation should run or not ? No worries. We can switch to SQL Query option instead of Table and do so.

Let's say we have a custom table ORG_EMPLOYEES which holds employee information along with their Start Date and End Date. Now we want to create an Automation on this table to send an email if any employee has End Date in next 7 days. Let's see how to do it.

- Let's select SQL Query option and enter our query

Here, one can also utilize REST Enabled SQL or REST Data Source options instead of Local Database.

- Click Create

- We will see below message that says Automation has been created but it's in Disabled state

- We will also see all the details we entered so far in the below section

- Now let's create an Action in this Automation

- Scroll down to Actions section and click Add Action

- Let's select Send E-Mail in Type

- We can mention the standard Email parameters such as To, CC, BCC in this section

- Now, let's enter the Subject and Body for the Email Notification. In this case, it would be a message informing the recipient about Employee records set to expire soon.

- Click Create

- Now, let's click Save and Run. This will run the Automation as a one-off request. This is how we can test our Automation before Enabling it.

- We should receive the Email Notification like this.

- Now that the Automation has been tested successfully, we can Enable the Schedule so that it automatically runs in the background and will send email as per our conditions

This is how we can create a simple Automation in Oracle APEX to send Email Notifications. This feature has a big potential as it's not limited to sending notifications; instead we can call a PL/SQL procedure in the Action section so that we can execute custom code whenever an Automation runs and the process can take care of data processing, integrations as well as sending alerts etc. Since it will be a custom code, the possibilities are endless on what we can achieve through the Automation feature in Oracle APEX.

Read More

How to dynamically extract metadata definitions in Oracle APEX

I recently came across a requirement where I needed to dynamically extract metadata definitions (such as column names, data types, etc.) of a variety of objects such as Tables, Views etc.

I achieved this using apex_data_export API in Oracle APEX. It’s important to note that apex_data_export is primarily used to export data and not the metadata. So to extract metadata definitions dynamically, we must first query metadata from data dictionary sources, and then pass that query result to the API call.

Let's see it in action.

Here's a sample PL/SQL block using apex_data_export to get Metadata:

DECLARE

    l_context apex_exec.t_context; 

    l_export  apex_data_export.t_export;

BEGIN

    l_context := apex_exec.open_query_context(

        p_location    => apex_exec.c_location_local_db,

        p_sql_query   => 'select column_name, data_type, data_length from all_tab_cols where table_name=' || '''' || 'AJ_AP_INV' || '''');

    l_export := apex_data_export.export (

        p_context   => l_context,

        p_format    => apex_data_export.c_format_csv,

        p_file_name => 'AJ_AP_INV.csv' );

    apex_data_export.download( p_export => l_export );

    apex_exec.close( l_context );

EXCEPTION

    when others THEN

        apex_exec.close( l_context );

        raise;

END;

Now, let's incorporate this in an APEX page and invoke it upon a Button press.

- Let's create a Process in After Submit section

- Set above code in PLSQL section -

- Let's set the Server-side condition When Button Pressed to our export button

- That's it. Now when we run the app and click the Export button, it will automatically download the Metadata definition of our sample table AJ_AP_INV

- Now, if we open the downloaded file, we will be able to see the metadata of this table -

How it was used in my use case:

- My requirement was to obtain metadata definitions of Public View Objects (PVOs) and their underlying tables.

- In this case, the data lineage information for PVOs was ingested into Autonomous database which was accessed by APEX. The table holding all the PVO information was XXCUST_FSCM_DATA_LINEAGE

- To provide more perspective, let's take an example of FscmTopModelAM.AnalyticsServiceAM.TerritoriesPVO.

This PVO essentially contains columns from FND_TERRITORIES_B and FND_TERRITORIES_TL.

Now we want our solution to extract Metadata definition of this PVO as well as metadata definitions of both the underlying tables.

Let's see how this was achieved.

- I created a new page with a text box accepting the PVO name and three buttons to export metadata of PVO and the underlying tables.

- I created a new process to export PVO metadata by using below PL/SQL block and set it to trigger upon click of PVO button:

DECLARE

    l_context apex_exec.t_context; 

    l_export  apex_data_export.t_export;

BEGIN

    l_context := apex_exec.open_query_context(

        p_location    => apex_exec.c_location_local_db,

        p_sql_query   => 'SELECT LISTAGG(VIEW_OBJECT_ATTRIBUTE, '''|| ',' || ''') WITHIN GROUP (ORDER BY NULL) "Col"

FROM   XXCUST_FSCM_DATA_LINEAGE

WHERE  VIEW_OBJECT = :P_PVO_NAME' );

    l_export := apex_data_export.export (

        p_context   => l_context,

        p_format    => apex_data_export.c_format_csv,

        p_file_name => :P_PVO_NAME );

    apex_data_export.download( p_export => l_export );

    apex_exec.close( l_context );

EXCEPTION

    when others THEN

        apex_exec.close( l_context );

        raise;

END;

- Similarly, I created two more processes, one for each underlying table using below code and set it to trigger upon click of each table button:

DECLARE

    l_context apex_exec.t_context; 

    l_export  apex_data_export.t_export;

    CURSOR c1 IS

        SELECT DATABASE_TABLE TABLE_NAME

        FROM

        (

        SELECT DATABASE_TABLE

        ,row_number() over (order by NULL) rnum

        FROM(

        SELECT DISTINCT DATABASE_TABLE

        FROM   XXCGI_FSCM_DATA_LINEAGE

        WHERE  VIEW_OBJECT = :P_PVO_NAME

        ORDER BY 1

        )

        )

        WHERE rnum=1;

BEGIN

    FOR I IN c1

    LOOP

        

        l_context := apex_exec.open_query_context(

        p_location    => apex_exec.c_location_local_db,

        p_sql_query   => 'SELECT LISTAGG(DATABASE_COLUMN, '''|| ',' || ''') WITHIN GROUP (ORDER BY NULL) "Col"

FROM   XXCUST_FSCM_DATA_LINEAGE

WHERE  VIEW_OBJECT = :P_PVO_NAME

AND    DATABASE_TABLE='|| ''''||I.TABLE_NAME|| '''');

    l_export := apex_data_export.export (

        p_context   => l_context,

        p_format    => apex_data_export.c_format_csv,

        p_file_name => I.TABLE_NAME );

    apex_data_export.download( p_export => l_export );

    apex_exec.close( l_context );

    END LOOP;

EXCEPTION

    when others THEN

        apex_exec.close( l_context );

        raise;

END;

- Let's run the app, provide the PVO name and click the three buttons to see the result

- As we can see, it downloaded three files, one for PVO meta definition, one for table #1 and another for table #2.

Possible Use cases:

This mechanism would certainly come handy in below use cases and many more:

• Dynamically inspecting a table/view

• Building data dictionaries

• Exporting data structure to Excel

• Validating report configuration dynamically

Read More