Changing the status of an Oracle Apex application with pl/sql

I needed this. I needed this badly. And for once the Oracle Apex forum gave me the answer for my need..

create or replace procedure set_application_status
  ( p_application in apex_applications.application_id%type
  , p_status      in varchar2
  if p_status not in ( 'AVAILABLE'        , 'AVAILABLE_W_EDIT_LINK', 'DEVELOPER_ONLY'
                     , 'RESTRICTED_ACCESS', 'UNAVAILABLE'          , 'UNAVAILABLE_PLSQL'
	             , 'UNAVAILABLE_URL')
    raise_application_error(-20000, 'Status '||p_status||' is not supported');
  end if;
  for r_i in ( select app.workspace_id
               ,      app.application_id
               from   apex_applications app
               where  app.application_id = p_application
      ( p_security_group_id => r_i.workspace_id
      ( p_flow_id     => r_i.application_id
      , p_flow_status => p_status
  end loop;

Using a serviceworker with Oracle APEX

After a question from a colleague of mine about caching JavaScript, css, images ed. in APEX I started to look at the new way : service workers.
With service workers we have the opportunity to manage caching programmatically with JavaScript.

I’m not going to tell about service workers. There are a lot of people who know more about it and have excellent posts on blogs and YouTube.
This post is more about how I implemented a service worker in a website I developed.

First, in the template of the LOGIN page I added a script section with the code:

function printState(state) {
if ('serviceWorker' in navigator) {
  navigator.serviceWorker.register('/m2b_service_worker.js', {
    scope : './'
  }).then(function (registration) {
    var serviceWorker;
    if (registration.installing) {
      serviceWorker = registration.installing;
    } else if (registration.waiting) {
      serviceWorker = registration.waiting;
    } else if ( {
      serviceWorker =;
    if (serviceWorker) {
      serviceWorker.addEventListener('statechange', function (e) {
  }).catch (function (error) {

With this script I registered the service worker file m2b_service_worker.js. Notice that the printState function is just some overhead for debugging.
This seems all to simple, but it has one small pitfall: scoping.

As an APEX developer I wanted to upload the script as static file in the framework. However, when you do that the maximum scope of the caching would be something like domain/pls/dad/workspace/r/….
That’s no good. I also want to cache static files like domain/i/apex.min.css and those files are out of the mentioned scope. Uploading it as a static file will result in a console.log message:

DOMException: Failed to register a ServiceWorker: The path of the provided scope ('/i/') is not 
under the max scope allowed ('/pls/apex/workspace/r/****'). Adjust the scope, move the Service 
Worker script, or use the Service-Worker-Allowed HTTP header to allow the scope.

Luckily I use a reverse proxy in front of our ORDS so I was able to install the script at the root of the reverse proxy and register it at root /, such that all requests to the domain could be cached if necessary.

The Service Worker:

VERSIE = "2",
CACHENAME = 'omy-cache-' + VERSIE,
EXTENTIES = ['gif', 'jpg', 'ico', 'css', 'js', 'png'];

self.addEventListener('install', function (event) {
  event.waitUntil( (cache) {
      return cache.addAll(FILES);

self.addEventListener('activate', function (event) {
  return event.waitUntil(caches.keys().then(function (keys) {
      return Promise.all( (k) {
          if (k != CACHENAME && k.indexOf('omy-cache-') == 0) {
            return caches.delete (k);
          } else {
            return Promise.resolve();

self.addEventListener('fetch', function (event) {
  var isGet = event.request.method;
    .then(function (response) {
    // Cache hit - return the response from the cached version
      if (response) {
        return response;

    // Not in cache - return the result from the live server
      return fetch(event.request)
          .then(function (response) {
      shouldCache = false,
      reqWithoutQuery = event.request.url.split("?")[0],
          ext = reqWithoutQuery.split(".").pop();
    if (EXTENTIES.indexOf(ext) >= 0 ) {
      shouldCache = true;
        if (shouldCache) {
      //before we return the response from the server
      //we cache the response for the next time
          return (cache) {
            cache.put(event.request, response.clone());
            return response;
        } else {
          return response;
      // Is I understand it, fetch throws an exception when offline
      // but a valid HTTP response, e.g. 404, will go tho then(), not to catch()
      return{return cache.match('offline.html');});

With APEX you don’t want to cache all GET requests. E.g. all GETs from \f are dynamic, dependent from session state. Your application will behave not as expected when you’ll cache \f.
I only want to cache the components that are truly static. Hence the array with exceptions.
I also want to show a static file [offline.hml] when the user has no internet connected, to increase user experience. This static file (and its image) is added to the cache on installation of the service worker.

The meat of the worker is the fetch event. When the request is found in the cache, the cached response is returned. When the request is unknown in the cache, the request is fetched from the server and when the requested item is within the array, it is cached for the next cycle.

When you look in Developer Tools > Application > Cache Storage you will notice your new cache with all the static files that were cached.

To emulate an offline connection, just set the checkbox “offline” in Network and hit F5. This should serve the mentioned offline.html from cache.