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
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
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.
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.
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 https://email@example.com 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.
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)
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.
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('firstname.lastname@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
And in SQL*Plus 23.3 the result looks like this (boolean values as
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://email@example.com 5.0.22
mle install jimp_mod https://firstname.lastname@example.org/browser/lib/jimp.js 0.22.10
mle install sql_assert_mod https://email@example.com 1.0.3
mle install lodash_mod https://firstname.lastname@example.org 4.17.21
mle install js_yaml_mod https://email@example.com 4.1.0
mle install minimist_mod https://firstname.lastname@example.org 1.2.8
mle install typeorm_mod https://email@example.com 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.