Everyone knows that JSON is very popular, and that includes its use in databases. Turns out that JavaScript is a popular way for working with JSON. Both can be used in Oracle AI Database 26ai Free. The question quickly arises: how do I pass a JSON variable from SQL or PL/SQL to a JavaScript function? In particular, how do I write the call specification?

Let’s extend an example I created a little while ago, using the popular string validation module validator.js. Many of its functions accept a JavaScript object providing further directives to the function. For example:

**isEmail(str [, options])**  ...

This article demonstrates how to provide these options to the JavaScript function.

WARNING: before you even start considering the use third-party/open-source code in your project make sure you get the sign-off from whoever is responsible for compliance, security, legal, … in your organisation. There are lots of caveats and potential problems that can arise using external code, and it’s always better to be safe rather than sorry.

Creating the validator.js module

Let’s say I’d like to create a function to verify if a string represents a valid email address. The aforementioned validator.js offers isEmail() for this purpose. So let’s give it a go and import the ECMAScript(!) version of the module into my Oracle AI Database 26ai Free instance. The easiest way to do so is to use SQLcl and its create mle-module command. Start by downloading the ESM module from your favourite Content Delivery Network:

$ curl -Lo '/tmp/validator.js' 'https://cdn.jsdelivr.net/npm/validator@13.15.26/+esm'
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  128k    0  128k    0     0   691k      0 --:--:-- --:--:-- --:--:--  695k

Next, create the module in the database.

SQL> mle create-module -filename /tmp/validator.js -module-name validator_module -version '13.15.26'
MLE Module validator_module created

The module has been created successfully.

Make isEmail() available to SQL and PL/SQL

With the module created, I can expose its functionality to SQL and PL/SQL by creating a call specification. Looking at validator.jss documentation I can see that isEmail() takes a string and an object as an optional argument. The function returns true if the string passed validation, false otherwise. Using this piece of information I choose a PL/SQL function over a procedure since a value is returned. I also know that I have to provide 2 IN parameters, a string representing the email address and optionally a JavaScript object containing the options. The corresponding database’s data type is JSON.

Here is an example of a call specification. The PL/SQL code unit is overloaded because you cannot provide default values for call specifications or else you get an PLS-00255: CALL Specification parameters cannot have default values error:

create or replace package validator_pkg as
  /* use default options */
  function isEmail(
    p_email_address varchar2
  ) return boolean
  as mle module validator_module
  signature 'default.isEmail';
  /* provide a JSON variable with options */
  function isEmail(
    p_email_address varchar2,
    p_options json
  ) return boolean
  as mle module validator_module
  signature 'default.isEmail';
end validator_pkg;
/

In a nutshell the call specification ties the JavaScript function isEmail() and module validator to a PL/SQL code unit named isEmail. In the above example the JavaScript engine is smart enough to deduce the parameter types.

If you like to stay in control of the type mapping (database to JavaScript), you can define the types in the signature clause like so:

create or replace package validator_pkg as
  /* use default options */
  function isEmail(
    p_email_address varchar2
  ) return boolean
  as mle module validator_module
  signature 'default.isEmail(string)';
  /* provide a JSON variable with options */
  function isEmail(
    p_email_address varchar2,
    p_options json
  ) return boolean
  as mle module validator_module
  signature 'default.isEmail(string, any)';
end validator_pkg;
/

All type mappings from SQL and PL/SQL to JavaScript and vice versa are documented in the JavaScript developer’s Guide, appendix A. Because p_options could potentially be anything, the database’s JSON type maps to JavaScript’s any “type”.

Testing for valid email addresses

Now that both SQL and PL/SQL can make use of isEmail() by means of invoking the call specification, it’s time for a test. SQL first:

select * from ( values
  (validator_pkg.isEmail('user@1.2.3.4', JSON {'allow_ip_domain': false } )),
  (validator_pkg.isEmail('user@1.2.3.4', JSON {'allow_ip_domain': true } )),
  (validator_pkg.isEmail('user@somewhere.org', JSON {'require_display_name': true } )),
  (validator_pkg.isEmail('SOMEONE ', JSON {'require_display_name': true } ))
) my_values (valid_email)
/

The query returns

VALID_EMAIL
-----------
FALSE
TRUE
FALSE
TRUE

PL/SQL is very similar:

declare
  l_valid_email boolean;
  l_options        JSON;
begin
  l_options := JSON( '{ "require_display_name": true }');
  l_valid_email := validator_pkg.isEmail(
    'SOMEONE ',
    l_options
  );
  if l_valid_email then
    dbms_output.put_line('that is a valid email address');
  else
    dbms_output.put_line('that is NOT a valid email address');
  end if;
end;
/

Unsurprisingly the result is the same as in the previous example:

SQL> declare
  2    l_valid_email boolean;
  3    l_options        JSON;
  4  begin
  5    l_options := JSON( '{ "require_display_name": true }');
  6    l_valid_email := validator_pkg.isEmail(
  7      'SOMEONE ',
  8      l_options
  9    );
 10    if l_valid_email then
 11      dbms_output.put_line('that is a valid email address');
 12    else
 13      dbms_output.put_line('that is NOT a valid email address');
 14    end if;
 15  end;
 16  /
that is a valid email address
PL/SQL procedure successfully completed.

Summary

JSON is the equivalent of a Swiss army knife for data processing, and Oracle AI Database 26ai features many ways to work with it efficiently.