Building a minimal Progressive Web App (PWA) on Autonomous APEX

February 25, 2020

Johannes Michler PROMATIS Horus Oracle

Senior Vice President – Head of Platforms & Development

While there are quite some documentation / blogs / samples that describe how to build a progressive web app based on Oracle APEX in general, so far there i no real sample that describes in detail on how to build such an app on top of the Oracle Autonomous Database (ATP). This post will give some guidance on how we were able to build a PWA on top of the ATP - in this case even on the "free tier" version of ATP on OCI.

Progressive web applications (PWA)

In many use cases having a "mobile" access to business applications has become crucial. Not always providing just a "responsive" layout, e.g. by using the Oracle APEX Universal or a dedicated Mobile-Theme is enough. Nowadays users expect their applications to be available "from the home screen" (if not from the App Store), are "always signed in", "offline capable" and furthermore allow push notifications. Progressive Web Apps can cover most of these requirements (

From a technical perspective the most important parts of a PWA are a manifest.json describing the PWA (and containing e.g. references to a start_url, a logo, ...) and a so called service worker (javascript) that is responsible for especially background notifications and offline usage/caching. to kickstart your PWA creation

The websites helps to build your first progressive web app. In a wizard like way you can define all the relevant attributes and further configure a "caching" service worker according to your needs. The tool furthermore checks your current configuration for conformance to all the PWA requirements in a nice dashboard.

PWAs on Autonomous Database

One crucial requirement for the service worker and the application manifest is: they need to reside in either a dedicated (quite high level, e.g. at root level /sw.js) URL or they need to provide a special HTTP Header.

On an Oracle Autonomous Database (e.g. ATP) both is difficult to achieve:

  • Serving static application/workspace ressources serves them under a "cryptic" URL such as /ords/r/test/100/files/static/v36/app.js
  • Serving static ressources does not allow to set a dedicated HTTP Header variable.

There are two possible workarounds for this:

  • Build a load balancer in combination with some path rules in front of the ORDS server. This load balancer further needs a SSL certificate. This is probably the best way for production environments, where the URLs/Hostname of APEX on ATP is not "nice enough" anyway.
  • Serve especially the service worker javascript over an ORDS REST service. That allows setting any HTTP Header variables. This is the way I've used for my free tier demo environment which I'll detail further down.

Step by Step to your first PWA on ATP

Step1: Install Sample App

On your ATP instance load the Sample App:

Step 2: Build manifest.json and service worker

The core files that a progressive web app needs to provide are a manifest.json describing the application in detail and a service worker that implements handling e.g. offline caching and background notifications. An easy way to generate those files is the web app

The first step on that page is to specify the URL to provide as a PWA. While the pwabuilder can directly check if everything is done correctly (if your app is publicly available) there are currently some issues of this feature for the APEX way of session/Cookie-Handling (I've created a Pull Request at to fix this already).

In the manifest tab of pwabuilder fill in the name, icons, start_url, ... of the APEX pwa and save the generated manifest.json:

In addition the "Service Worker" tab of the pwabuilder provides a number of pre-built service workers for various caching patterns:

Copy the upper javascript into an app.js and the service worker into sw.js.

Finally zip up the java scripts plus the manifest.json and the icons into a zip that is similar to the one attached here.

Step 3: Upload and register manifest and service worker

In the next step upload the above files as "Static Application Files". An easy way to load the manifest.json is the favicon section of the apex application:

The app.js that registers the service worker can be put into the "File Urls" of the APEX template:

Step 4: provide the service worker

Unfortunately you cannot simple provide the service worker java script as a static application file: It can only handle URLs that lay "below" the location of the service worker java script itself. Since this is nested (e.g. /ords/r/test/100/files/static/v48/sw.js), this does not allow "real" PWA APEX applications. I decided to use ORDS to serve the service worker instead. The implementation can then fetch the actual sw.js from the Application Static Files:

The code for this can look like:

v_clob blob;
apex_session.create_session(p_app_id => 100, --Replace with APP ID
p_page_id => 1, --Replace with PAGE ID
p_username =>'TEST' --Replace with USERNAME
) ;

nOWA_UTIL.mime_header('text/javascript', FALSE);
HTP.p('Service-Worker-Allowed: /');
select blob_content into v_clob from wwv_flow_files where flow_id=100 and filename='sw.js';

Related Work

The following articles and demo applications provide examples of PWAs on top of Oracle APEX: