Installing MLE Modules in the Oracle Database

Installing MLE Modules in the Oracle Database 23c


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 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:


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

Output of mle command without parameters

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:

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 13.11.0

And here is the result in SQLcl :

Output of successfully executed mle command to install the validator module from npm

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.

SQLcl buffer containing the SQL command to install the validator module

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('') 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):

Result of calling the is_email validator function in SQLcl

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

Result of calling the is_email validator function in SQL*Plus

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 5.0.22
mle install jimp_mod 0.22.10
mle install sql_assert_mod 1.0.3
mle install lodash_mod 4.17.21
mle install js_yaml_mod 4.1.0
mle install minimist_mod 1.2.8
mle install typeorm_mod 0.3.17

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


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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.