Installing MLE Modules in the Oracle Database

Introduction

In my previous blog post, I’ve shown how you can deploy an npm module from a URL and a custom ESM module from a local file into a remote Oracle Database 23c using JavaScript and SQLcl. This works well. However, for two MLE modules, I had to write 22 lines of code with duplications. I do not like that. I have therefore developed a small SQLcl custom command that greatly simplifies the installation of MLE modules in the Oracle Database.

Installing the mle Custom Command

Start SQLcl and run the following command to install the custom command mle in the current session.

script https://raw.githubusercontent.com/PhilippSalvisberg/mle-sqlcl/main/mle.js register

The SQLcl script command can read a JavaScript file from the local file system or from a URL. If you feel uncomfortable running JavaScript files directly from a URL, you can have a look at the GitHub repo first and download and run a chosen version of the script from the local file system.

The register subcommand registers the mle script as an SQLcl command. However, this registration is not permanent. It will be lost after closing SQLcl. To make the custom command available in every new SQLcl session add the command above to your login.sql or startup.sql. SQLcl executes these files on start-up or after establishing a new connection. Just make sure that you have configured the SQLPATH environment variable accordingly.

Providing Help

Now you can run the mle command like this:

mle

Without parameters, an error message is displayed along with information on how to use this command.

Installing Validator

Now we know the syntax to install an MLE module from a URL. However, what’s the URL for an npm module? We use the free OpenSource CDN jsDelivr for that. They provide a service returning an npm module as an ECMAScript module. The result can be used in a browser and also in an Oracle Database. The URL looks like this:

https://esm.run/npm-package-name@npm-package-version

We want to install the current version 13.11.0 of the npm module validator. Based on the information provided above our SQLcl command for that looks like this:

mle install validator_mod https://esm.run/validator@13.11.0 13.11.0

And here is the result in SQLcl :

Please note that the response message by SQLcl 23.3 is not 100% correct for MLE modules. However, our module is installed correctly. We verify that later.

Maybe you’d like to know what the SQL statement looks like to install this module. The last statement is still in SQLcl’s buffer. Therefore we can type l followed by enter to see the content of the buffer.

SQLcl's buffer

The first line contains the start of the SQL statement. Lines 2 to 7 are comments generated by jsDelivr. Line 8 contains the complete module. Yes, as a single line. The code is minified. All unnecessary whitespace is gone and internally used identifiers are shortened to save space. On line 9 we would see a JavaScript comment with a pointer to a map file that points to the original, nicely formatted source code. This is interesting when debugging in other environments. It’s currently not used in the database.

Querying MLE Modules

The following SQL statement shows some data regarding the previously deployed MLE module:

set sqlformat ansiconsole
select module_name, version, language_name, length(module)
  from user_mle_modules;

The result in SQLcl looks like this:

We see the version of the module and also the size in bytes of the blob column where the module is stored. It is one byte larger than the result of the URL since It contains a final line feed character due to the way we built the SQL statement.

IMO it is helpful to provide the version of the external ESM as long as there is no proper package registry within the Oracle Database.

Verifying Installation

Let’s write a call specification in order to verify the successful installation of the validator module.

create or replace function is_email(
   in_email in varchar2
) return boolean as mle module validator_mod signature 'default.isEmail(string)';
/

Now we can run the following query to verify e-mail addresses and the installation of the validator module:

select is_email('jane.doe@example.org') as jane_doe,
       is_email('john.doe@example') as john_doe;

In SQLcl 23.3 the result looks like this (boolean values as 1 and 0):

And in SQL*Plus 23.3 the result looks like this (boolean values as TRUE and FALSE):

Installing More Modules

Let’s install some other modules I find useful. You find them on npm if you want to know what they do.

mle install sentiment_mod https://esm.run/sentiment@5.0.2 5.0.22
mle install jimp_mod https://esm.run/jimp@0.22.10/browser/lib/jimp.js 0.22.10
mle install sql_assert_mod https://esm.run/sql-assert@1.0.3 1.0.3
mle install lodash_mod https://esm.run/lodash@4.17.21 4.17.21
mle install js_yaml_mod https://esm.run/js-yaml@4.1.0 4.1.0
mle install minimist_mod https://esm.run/minimist@1.2.8 1.2.8
mle install typeorm_mod https://esm.run/typeorm@0.3.17 0.3.17

Installing all modules is just a matter of a few seconds.

Conclusion

Using the SQLcl custom mle command to install ECMAScript modules from a file or from a URL is not only easy and fast, but it is also an excellent way to provide tested functionality within the database.

Technically it should be possible to generate the PL/SQL call specifications based on the information that is provided for IDEs to better support code completion (type definitions). I hope that Oracle will provide such a feature in one of the coming releases of SQLcl or as part of a dedicated MLE tool (similar to dbjs which was part of the experimental version of the MLE back in 2017). Even if I do not want to provide access to all underlying functionality of an MLE module within the database or provide the functionality with the same signature, a PL/SQL package with all call specifications would simplify the work for a wrapper PL/SQL package that exposes just the relevant subset in a suitable way for the use in PL/SQL or SQL.