JavaScript as a Server-Side Language in Oracle APEX 20.2

Stefan Dobre
Feb 11 · 6 min read

In this post, we will take a closer look at the integration of GraalVM in Oracle Application Express 20.2 and above, and how it enables the execution of server-side JavaScript code.

When you run APEX 20.2 or later, you will notice a couple of differences to previous versions. For example, the “Execute PL/SQL Code” Dynamic Action has been renamed to a more generic “Execute Server-side Code” and the “Execute PL/SQL Code” Process has been renamed to just “Execute Code”.

In all of these places, a new attribute called “Language” has been introduced. On pre-21c databases, the only option will be PL/SQL. If you are on 21c however, you will also see “JavaScript (MLE)”. We aim to grow the list of available languages with future Database releases.

APEX ≥20.2 && DB ≥21c

Server-side JavaScript (MLE) code looks like this:

As this example shows, you can interact with page items via apex.env, and execute DML or PL/SQL via apex.conn.execute

The formal API documentation of these new APIs is still to come. They are however a subset of the Node.js Oracle Driver albeit with notable differences, such as the move from asynchronous to synchronous functions. You can also find examples in the respective attribute’s help text, and by navigating the code editor’s autocomplete.

While the above code could have been written even simpler in PL/SQL due to its tight coupling with SQL, there are other use cases where JavaScript might be the more obvious choice for a developer.

Take for example the case of Regular Expressions. The JavaScript regex engine is a robust and popular way to work with them. Here we use JavaScript (MLE) to validate an email address on the server based on a complex regular expression, while not having to worry about compatibility with the Oracle regex engine, as the two specifications don’t always overlap.

Another area where JavaScript can come in handy on the server is its advanced JSON support.

In the past decade APEX developers went from manually escaping and concatenating strings, to using apex_javascript, to using apex_json, and nowadays to using the native JSON capabilities of the database via json_object_t. These are amazing improvements in terms of usability and performance, but can still not compare with the native JSON capabilities of JavaScript.

In the following example we see an AJAX process endpoint, based on JavaScript (MLE) and how simple it is to work with JSON objects.

Under the hood

As the previous code snippets have shown, MLE is tightly integrated into APEX. We do not have to manually invoke the JavaScript code from within PL/SQL which means we do not have to worry about DBMS_MLE or creating contexts. We also don’t need to explicitly “require” modules like mle-js-bindings in our JavaScript code. APEX does all of this for us — but how, and when?

When we request or submit a page, a new database transaction will be initiated. APEX will then proceed to perform its processes as per usual. When it stumbles upon a user-defined JavaScript (MLE) snippet, be that an application process, pre rendering process, region source, etc., it will create an MLE context — if one does not already exist for the current database session and APEX request. If one has already been created, the current snippet will be evaluated within it. This has great performance benefits, as it eliminates the overhead of context creation.

What this also entails however, is that anything assigned to the global scope will still be reachable by proceeding snippets. Say we create one local variable, and one assigned to the global object. Note that globalThis in a JavaScript MLE context is the relative equivalent of the window object in a browser context.

We can of course assign anything to this namespace — functions, objects, variables, etc.

Snippets that follow, for example a Pre-Rendering Page Process, will have access to the same globalThis. Note that variable1 cannot be accessed, as it was not assigned to globalThis.

This is different from the way PL/SQL processes work, where the only way to share data between them is by assigning it to page items or package variables.

At the end of the transaction, be that after page rendering, after an AJAX call process, or after page submission, any MLE context created by APEX will be purged.

JavaScript Modules & npm

Leaving the best for last — JavaScript Modules. Perhaps the greatest benefit of using JavaScript in the database is the ability to tap into the vibrant npm ecosystem.

Native creation of JavaScript modules and proper dependency management is planned for future database versions. We can however achieve similar results, today, with a self-maintained sources table and a little bit of code.

Let’s first create the table which will store our JavaScript libraries.

Let’s also go ahead and populate this table. For the following examples, I’ll be needing qrcode-generator, validator.js, and marked.js.

In order to easily access these modules when we need them, we will need to expose a “require” function that is based on the module name, fetches the source out of the table, evaluates it, and returns the resulting object. We will name the function requireModule so it will not conflict with the built-in requirefunction. We can add this code to an Application Process that runs On Submit.

It’s also wise to add a server side condition to this process so it does not run on every submit, but only when we expect to run MLE code.

We can now use this infrastructure in an After Submit Page Process.

Example 1

Let’s first put the qrcodelibrary to test. If P1_QR_DATA is a Text Field item, and P1_QR_CODEis a Display Image Item, we can use this library to return the QR Code as a base64-encoded string.

On page submit, based on a URL entered in P1_QR_DATA we can execute:

Which will result in the following:

Of course we could have used this library directly in the front-end like we’re already used to, but rendering the QR Code server-side allows us to achieve other things, such as embedding it in an email or report.

Example 2

In this example we will use the validate.js library to add a server-side validation for an email address.

Having a page item P3_EMAIL and a validation on this item of type Expression with value requireModule('validator').isEmail(apex.env.P3_EMAIL) configured as follows:

helps us achieve the following behavior on page submit:

Example 3

The marked.js library can also come in handy if we store content as Markdown, but wish for it to be converted to HTML already on the server. Benefits of performing this conversion on the server include enhanced Search Engine Optimization, as well as the ability to embed the formatted content in emails.

To illustrate this, let’s convert the content of textarea P5_MARKDOWN into HTML and assign it to a Display Only item P5_HTML . The following code runs in an After Submit process:

Server-side JavaScript execution is an exciting new addition to Oracle APEX that unleashes new possibilities for both established APEX developers, as well as JavaScript developers just getting started with APEX.

You can spin up your own Oracle Database 21c with APEX 20.2, today, for free, on Oracle Cloud. Get started over at oracle.com/free or follow this step-by-step tutorial.

Thanks to Lucas Braun

graalvm

GraalVM team blog - https://www.graalvm.org

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store