Oracle APEX, get your act together!

Don’t get me wrong, I have worked with Oracle APEX for many years now with satisfaction. But in my humble opinion they are moving us as developers to an unsustainable situation.
Since the beginning the focus of developing APEX applications has been on the single developer, single application, multiple users paradigm. So we build applications… first in our free time, later perhaps for a customer. Then we paired up with some colleges, formed several teams and now we are looking at a landscape of a lot of applications; some of them very large with a lot of pages.

So now we have large operations with one or many APEX applications, having to manage bug fixes and build new functionality, preferably in a continuous integration life cycle.
And here is where APEX falls short in my opinion.

The focus of APEX deployment has always been on full application export and still is. Even sqlcl has a APEX EXPORT command, but no APEX EXPORT PAGE command (as of this day, please correct me if I am wrong). Sure, you kan export a single part of an application like a theme or a page, but please do this exercise for me:

1) create in a local APEX environment an application
2) Export the application
3) Import the application in e.g. apex.oracle.com

All works well, but now:

4) export locally a single page of the application
5) try to import the page in apex.oracle.com

It won’t work (exported in another workspace-id etc.).

Now the savvy guys will tell you that you have to run som APEX installation packaged procedures and that you have to set workspace-id and the offset before importing the page export,
but when you come to think of it this is ridiculous. The APEX application import succeeds, the partial page import fails.
Try getting in your next sprint the requirements that when a user wants to use part X of your application, she first has to align with some internal keys… nonsense.

Today we are facing a development environment where alterations to the application can’t be managed when faced with more than say 5 developers. Even when you lock a page you’re still
able to export the application. Even when someone is working in it. A bugfix on page x that isn’t done yet is able to be present in an export. To capture these situations you have to rely on the test cycles afterwards, but the framework should help us preventing these situations.

Make the page lock more restrictive, make the export/import process work without the nonsense of workspace-ids and offsets.

Let’s take APEX development further and strive for a page-per-page low-code development cycle.

please!

Advertisements

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) {
  console.log(state);
}
if ('serviceWorker' in navigator) {
  navigator.serviceWorker.register('/m2b_service_worker.js', {
    scope : './'
  }).then(function (registration) {
    var serviceWorker;
    if (registration.installing) {
      serviceWorker = registration.installing;
      printState('installing');
    } else if (registration.waiting) {
      serviceWorker = registration.waiting;
      printState('waiting');
    } else if (registration.active) {
      serviceWorker = registration.active;
      printState('active');
    }
    if (serviceWorker) {
      printState(serviceWorker.state);
      serviceWorker.addEventListener('statechange', function (e) {
        printState(e.target.state);
      });
    }
  }).catch (function (error) {
    printState(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:

var
VERSIE = "2",
FILES = [
  '/i/myimage.gif',
  'offline.html'
],
CACHENAME = 'omy-cache-' + VERSIE,
EXTENTIES = ['gif', 'jpg', 'ico', 'css', 'js', 'png'];

self.addEventListener('install', function (event) {
  event.waitUntil(caches.open(CACHENAME).then(function (cache) {
      return cache.addAll(FILES);
    }));
1});

self.addEventListener('activate', function (event) {
  return event.waitUntil(caches.keys().then(function (keys) {
      return Promise.all(keys.map(function (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;
  
  event.respondWith(
    caches.match(event.request)
    .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) {
        var 
      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 caches.open(CACHENAME).then(function (cache) {
            cache.put(event.request, response.clone());
            return response;
          });
        } else {
          return response;
        }
      }).catch(function(error){
      // 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 caches.open(CACHENAME).then(function(cache){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.

Component view in Apex 5.1

So I’m a dinosaur. I don’t care if I’m not one of the cool kids. I like component view, I started with component view, I’m comfortable in component view and I get the “flow” of component view.
Of course I’m using page designer, but when things get “buggy”, I switch in dinosaur-modus.

And than there is Apex 5.1….
Where is my component view?

First, there is Two pane mode
1_switch_to_two_pane
For me that is a big step in the right direction. I’m not always in the position to ask for a 24″ monitor for development. So, less is more in this case.
And look
2_two_pane_layout
Component view!

But it’s not the same. This Component View imposter doesn’t give me the original page for e.g. a region. It jumps to the right section of the page-designer.
Not fair! That’s cheating! …. given that it is also very handy and perhaps my new MO.

But look in the upper right corner.
3_goto_preferences
Here you can set some developer specific preferences, like

4_enable_component

This will change the original panel to
4_legacy_button

Legacy, deprecated, whatever. It’s still there.

Like I said, I will be using Two Pane Mode as default with component view as my prefered way of developing (it could be nice if I could set this somewhere in my preferences),
but it’s comforting to know I can still switch “back”.

ora-01403 in wwv_flow_api.import_begin

Two new databases, two fresh new apex installations. Yeah! One for development, one for test. Let’s create a new Workspace X in every environments.
Everything is ready, let the development begin!

After a while the first iteration of the application was ready and we imported it in the the second environment and everything failed miserably.

The site exploded in an error page giving the dreaded ORA-01403 after the first statement in the install script WWV_FLOW_API.IMPORT_BEGIN.

But after running the script in SQLPlus with spooling enabled I got the following :

ORA-02291: Integriteitsbeperking (APEX_050000.WWV_FLOWS_FK) is geschonden – bovenliggende sleutel
is niet gevonden.
ORA-06512: in “APEX_050000.WWV_FLOW_API”, regel 2750
ORA-06512: in regel 2

That’s dutch for an integrity constraint violation, parent key not found

Digging into dba_constraints it gave me that the workspace was not found.

The provisioning_company_id of wwv_flow_companies for workspace X had a different value than development!

Note to myself: when deploying into a new environment, don’t create the workspaces by hand, but import it from a source installation.

Note to the APEX development team: it wouldn’t kill you when you stop the process with a simple message like ‘Workspace-id XXXXXX not found’ instead of “I didn’t find it” without giving┬áthe “it” some meaning.

Node.js scripts for Oracle Cloud Storage Service

Working with Oracle Cloud Storage Service I noticed that it’s not really customer-ready (in my humble opinion).

e.g. Creation of a storage container is not yet supported from the dashboard. You’ll have to create a container using a magical Java library or a REST-API using Curl.

But we are on Windows.

So we don’t have Curl.

And I refused to install Cygwin just for this purpose.

However, node.js is installed in our Windows environment, so I created a small repository of node.js scripts to handle some of the basics of the Oracle Cloud Storage Service.

For everybody who is interested : https://github.com/emoracle/OracleCloudStorage

gReport.search in APEX 5

yes, gReport was undocumented, but it was sooo handy when you wanted to refresh your interactive report from within javascript.

Just google for it and you will find dozens of examples using gReport.search(‘SEARCH’);

And then you upgraded to APEX 5

Because now we can have multiple IR’s on one page and the team made the IR a jquery widget, gReturn┬áno longer exists.

It’s a widget, so this has worked for me:

 

$('#your_static_id_of_the_report_ir').data('apex-interactiveReport')._search();

So If your static Id is “batchRuns” it becomes:

  $('#batchRuns_ir').data('apex-interactiveReport')._search();

And of course, it’s not documented, you’re not supposed to use the private method, but until the APEX team creates a public method to refresh my IR given the static Id of the region, I will be using this.

Until the next upgrade…