A Simple Example¶
Our running example will be the use of a email validation system.
Luckily we only have to write minimal code for this since there is
already an npm package called validator.
This package has an
isEmail function, which can perform the tasks for us.
validator.isEmail('email@example.com'); //=> true
$ npm install validator /home/oracle/udf └── firstname.lastname@example.org npm WARN enoent ENOENT: no such file or directory, open '/home/oracle/udf/package.json' npm WARN udf No description npm WARN udf No repository field. npm WARN udf No README data npm WARN udf No license field.
to download and install the validator module (this requires network access).
Note that we are not building a full-fledged npm module here, which would
require us to run
npm init before and then save the dependency in
package.json. This is not needed for the deployment into the database; the presence of the
module at the time of deployment is sufficient. The warnings about missing
package.json file can therefore safely be ignored.
$ npm install webpack webpack-cli $ npx webpack-cli --entry=./node_modules/validator/index.js --output=./validator_bundled.js --output-library-target=commonjs
load.js, can be used to
deploy the module and make the
isEmail function available:
The load tool uses the package
async to run the four functions
doCleanup one-after-an-other to deploy the bundled validator code.
doConnect connects to the database using the user
dbPwd and the connection string in
doSetup function creates a table
mylobs, which will temporarily store the source of the bundled validator package.
In the function
createModule actually deployes the bundled package via
CREATE SOURCE and makes the the
isEmail function available via
The function takes a character string as input and produces a number. We choose the type
number as result since Oracle Database MLE does not support
some implicit conversions if we specified it. So to make things clearer, we
number and can look at the implicit conversions
applied by Oracle Database MLE. Thus, if an email is valid, we
get the value
Lastly, the function
doCleanup drops the table
mylobs, which is not needed anymore after the source is deployed.
We can execute the deployment code via nodeJS:
$ npm install oracledb async $ node load.js
Creating Test Data¶
To call the UDFs we first need some data. We will use SQL*Plus to connect to the database and create this test data. To connect with SQLPlus, start a command line and type
$ sqlplus scott/tiger@ORCLPDB1
to connect to the database with the scott schema.
You should see a command prompt starting with
SQL>. Here, we can issue
commands to interact with the database. We now create a table,
a single column (
SQL> CREATE TABLE emails (email VARCHAR2(100)); SQL> INSERT INTO emails VALUES ('email@example.com'); SQL> INSERT INTO emails VALUES ('not really an email'); SQL> INSERT INTO emails VALUES ('firstname.lastname@example.org'); SQL> INSERT INTO emails VALUES ('.@googlemail.com');
Executing the deployed UDF¶
SQL> SELECT * FROM emails WHERE validator.isEmail(email) = 1
In this query, we use the isEmail function we deployed with our deployment code previously
as a predicate in the WHERE clause such that the query returns only rows that
contain valid email addresses. Recall that we cannot directly use this
function as a filter, but still have to compare the result against a value.
In our case, we rely on the implicit conversion from the boolean value from
isEmail to a number (see conversion documentation for
details of type conversion). This will give us the value
1 for every input
In this example, the
isEmail function is called for every row of the
table. Note, however, that the values might appear in no particular order and
the UDF should not rely on any specific order. Furthermore, the UDF should be
purely functional and not keep any state between consecutive calls.