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 Database 23c 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 Database 23c Free instance. In preparation for this task I downloaded the latest version to a directory on my database VM, and exposed this directory as SRC_CODE_DIR to the EMILY account. Then it’s just a matter of calling create mle module:
create mle module validator
language javascript
using bfile(src_code_dir, 'validator.min.js');
/
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 the JavaScript object containing the options.
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
signature 'default.isEmail';
/* provide a JSON variable with options */
function isEmail(
p_email_address varchar2,
p_options json
) return boolean
as mle module validator
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
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
signature 'default.isEmail(string, any)';
end validator_pkg;
/
All type mappings from SQL and PL/SQL to JavaScript and vice versa are documented in appendix A of the JavsScript Developer’s Guide. 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 <user@somewhere.org>', 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 <user@somewhere.org>',
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 <user@somewhere.org>',
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 Database 23c features many ways to work with it efficiently.
Martins Blog 