Using JavaScript modules provided by the open-source community is one of the many benefits of Oracle Database 23ai (on x86-64).
Let's assume for a moment that a table-API or maybe a trigger are used to validate DML operations on the HR.EMPLOYEES table. One of the table's columns stores email addresses, and you'd like to ensure that the data within that column represents a valid email address. There are many ways to ensure the validity of email addresses, for example using Use Case Domains.
Alternatively you could execute a piece of validation code before storing incorrect information in the database. Validating email addresses isn't a new problem, and there are plenty of solutions available. In this post you can learn about the open source validator.js module's isEmail() function. Validator is just one of many open-source solutions but since it's featured in JavaScript/MLE related posts previously it has been selected once more as an example.
Please refer to validator's GitHub project site for more details about the project's license and implications of use. The article assumes your legal and IT Security departments (as well as any other party) agreed that it's safe to use the module in your code. Using 3rd party code in your application typically requires certain compliance steps to be completed which are out of scope of this article.
The JavaScript module can be downloaded from multiple sources, make sure to pick a trustworthy one. You also need to ensure that you get the ECMA Script (ESM) version of the module from your preferred CDN as those are the only ones supported in Multilingual Engine (MLE). The following example demonstrates how to get the module's ECMA Script version from JSDeliver using a command line tool (cURL). There are of course other sources as well:
curl -Lo validator.min.js https://cdn.jsdelivr.net/npm/validator@latest/+esm
This will generate the source file to be used later. If you are after a specific version of the module substitute latest with the release number you need.
JavaScript in Oracle Database 23ai allows you to create modules using the BFILE clause, specifying a directory object and file name. In preparation of this call you need to make validator.min.js available on the database server. In this example the emily user has read access to the javascript_src_dir directory where the file resides.
With the file in place it is possible to create the module:
create mle module validator
language javascript
using bfile (javascript_src_dir, 'validator.min.js');
/
The database will tell you that the module has been created successfully. You can see for yourself:
col MODULE_NAME for a30
col LANGUAGE_NAME for a30
select
module_name,
language_name
from
user_mle_modules
where
module_name = 'VALIDATOR';
MODULE_NAME LANGUAGE_NAME
------------------------------ ------------------------------
VALIDATOR JAVASCRIPT
You can read more about creating JavaScript modules in Oracle Database 23ai in chapter 2 of the JavaScript Developer's Guide.
With the module successfully created in the schema, the hardest part is completed. The Validator module exposes quite a few string validators for nearly any purpose imaginable, the project's GitHub page lists them all. As per the introduction to this post, there is a need for validating email addressess. Exposing validator.isEmail() from the JavaScript module requires a call specification. A PL/SQL call specification links the module's JavaScript functions to SQL and PL/SQL. In this simple case a stand-alone function does the trick:
create or replace function isEmail(
p_str varchar2
) return boolean
as mle module validator
signature 'default.isEmail(string)';
/
In case where multiple JavaScript functions are made available to PL/SQL and SQL you should probably encapsulate them in a package to keep the logical grouping of related code.
Please refer to chapter 5 in the JavaScript Developer's Guide for more information about call specifications and module calls.
After the JavaScript module has been created in the schema and exposed to SQL and PL/SQL it can be used like any other PL/SQL code unit. Here are a couple of examples for email validation:
SQL> select isEmail('abc');
ISEMAIL('AB
-----------
FALSE
SQL> select isEmail('ab@c.de');
ISEMAIL('AB
-----------
TRUE
Using JavaScript in Oracle Database 23ai on Linux x86-64 opens the door for integrating many interesting open source projects in your own applications, provided that their licenses are compatible. Modules like validator.js can be integrated easily as you just observed, provided your compliance and security teams don't have any concerns using 3rd party code.
Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.
Next Post