Oracle Database has the MultiLingual Engine (MLE) for JavaScript built directly into the kernel since the 21c release. MLE allows running JavaScript code inside the Oracle database. It is powered by the GraalVM.
JavaScript support was first introduced in Oracle 21c specifically targeting Application Express developers, allowing them to use an additional programming languge. In Oracle 23c the feature set has been greatly expanded to include the creation of JavaScript ES Modules (ESM for ECMAScript Modules).
Remark: The following scripts are run on an Oracle database 23c FREE - Developer Release using sqlplus.
First things first, let's start a 23c FREE Developer Release Docker image using Podman:
The command will configure the SYS and SYSTEM passwords as free (in lower case), it will allow connecting to the FREEPDB1 pluggable database using local port 1521 and it will mount our local folder mle into the container as /home/oracle/mle.
Next, let's create a new user named developer and grant the right privileges for getting started:
For the rest of this post, we'll focus on the Chance.js module from Victor Quinn under MIT License. This module is really interesting when working on data as it provides numerous ways to "generate random numbers, characters, strings, names, addresses, dice, and pretty much anything else"; in brief 100+ new data generators.
The latest version available at the time of writing is 1.1.11, so let's download it into our mle folder so that it can be used by the database:
Now it's time to connect as our developer user and do the real things, connect with sqlplus (from inside the container or using other tools such as SQLcl or SQL Developer from outside the container):
And now connected as developer, run:
So far, we've installed the Chance.js module and an extended one that exports properly each of the function with a signature compatible with the Oracle database data types (including the native JSON and new 23c BOOLEAN).
We've also created an MLE environment that will help us for the next setup phase: integrating the SQL engine with the JavaScript MLE using PL/SQL functions, and more precisely, how JavaScript imports will be managed:
That's all!
You can now use all the new functions to generate random data. See the following examples.
Our first example will focus on very basic data generation:
If you want more than 1 row, you can leverage this trick:
Remark that DATE is an Oracle datatype and thus you need to use lowercase call to distinguish it from the function, that's why the double-quotes are mandatory and the lower case must be used here (as for "integer"). In the previous example, the first date is 15th of September 2049 because of my localized session (France).
Here you can see some limitations or flexibility (depending on your point of view) of interoperability between JavaScript and PL/SQL.
When passing a JSON parameter, we can ask for an extended generation mode for a given data generator. But the PL/SQL function signatures have to
be compatible because PL/SQL is strongly typed. Hence the trick here is to have a third signature for that case:
If we had a second parameter then we allow the resulting type to be an Oracle database DATE instead of a VARCHAR2:
You can see how it is now easy to generate random data, it's up to you... but one last word!
If you've taken a look at the extended MLE module above, you may have seen some additional code. In fact, while looking for JavaScript data generators, I found out that Fony.js (MIT License) from [Safia Abdalla](https://github.com/captainsafia) provided an interesting concept of `JSON template`. So I've decided to integrate it and improve it to benefit from the additional possibilities offered by `Chance.js` in terms of generator customization using a JSON document as a parameter. The result is that the `template()` function can now be used to generate random JSON documents based on JSON templates!
Let's see some examples:
You'll note that the ordering of the JSON fields is not respected. This is expected since the template() function leverages the Object.keys() function which in the latest ES standard doesn't guarantee the ordering anymore. But that's not really a problem, right?
This would bring some interesting ability to populate the JSON Relational Duality Views examples from the 23c FREE documentation.
We've seen how to import an ES Module inside the Oracle database 23c FREE Developer Release thanks to the JavaScript MultiLingual Engine. We've also seen how dependencies resolution work using an MLE environment.
As you can see, the possibilities are infinite and benefiting from such a data generator for both simple values but also JSON documents is incredibly useful. This could be used for Oracle Application Express (APEX) applications, demos, random data for unit testing, etc.
And now, it's up to you!
As Jeff Smith said in his blog post on MLE JS Modules & Snippets, we highly recommend reading the 23c JavaScript Developer Guide (Docs.).
You may want also to review Martin Bach's blog post on Using JavaScript community modules in Oracle Database 23c Free - Developer Release which I've stolen some of the commands used here.
Don't forget to provide us your feedback on the 23c FREE community forum that we monitor and where you can provide feedback.
I would never have discovered the possibilities highlighted here without the help of Martin Bach and Lucas Victor Braun-Lohrer, respectively Product Manager for JavaScript MLE and Program Manager at Oracle Labs; so thanks a lot!!!
Previous Post
Next Post