Using JavaScript community modules in Oracle Database 23c Free - Developer Release

Perform Email Validation

Using JavaScript modules provided by the open-source community is one of the many benefits of Oracle Database 23c Free - Developer Edition.

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 SQL 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.

Get the ECMAScript version of validator.js

The Validator module can be downloaded from multiple sources, as long as you pick a trustworthy one it doesn't really matter where the file originates from. You need to ensure that you get the ECMA Script (ESM) version of the module from your preferred CDN as they are the only ones supported in Oracle. The following example  demonstrates how to get the module's ECMA Script version from JSDeliver, there are of course other options 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.

Create the MLE module in the database

JavaScript in Oracle Database 23c Free - Developer Release allows you to create JavaScript 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 jstest 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 23c Free - Developer release in chapter 2 of the JavaScript Developer's Guide.

Expose the module's functionality to PL/SQL and SQL

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.

Validate Email Addresses

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

Summary

Using JavaScript in Oracle Database 23c Free - Developer Release opens the door for integrating many interesting open source projects in your own applications. 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.

Appendix

Use the following snippet if you are unsure how to create a developer account on your Oracle 23c Free - Developer Release database. Note that the code will prompt you for a password.

connect / as sysdba
alter session set container = freepdb1;

drop user if exists jstest cascade;

create user jstest identified by &secretpassword
default tablespace users quota unlimited on users;

grant create session to jstest;
grant db_developer_role to jstest;
grant execute on javascript to jstest;

drop directory if exists javascript_src_dir;
create directory javascript_src_dir as '/home/oracle/devel/js/validator';
grant read on directory javascript_src_dir to jstest;