Skip to content

JavaScript User Defined Functions (UDFs)

Introduction

One way to extend the database with user-provided functionality is through user defined functions (UDF for short). These functions can occur at different places in the statement execution. Let us consider a SELECT query as an example. Assume the query has the form SELECT <selector> FROM <table> WHERE <filter>. A UDF can occur either in the selector or in the filter expressions. If a UDF occurs in a selector, it will be called for every row in the result of the query. A UDF in a filter, however, might also be called on rows that will not be in the result. In the remainder of this document we show how to write UDFs in JavaScript that can be used in either part.

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('foo@bar.com'); //=> true

Dependency Setup

We first install the dependency validator from npm in the current folder. To not destroy any existing JavaScript projects we recommend using an empty folder to start from. Then use

$ npm install validator

/home/oracle/udf
└── validator@8.1.0 

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.

Since an MLE JavaScript module is always described as a single file that contains all code, we can use Webpack to create a bundle that exports all functions:

$ npm install webpack webpack-cli
$ npx webpack-cli --entry=./node_modules/validator/index.js --output=./validator_bundled.js --output-library-target=commonjs

JavaScript Code

The following JavaScript code, stored in a file named load.js, can be used to deploy the module and make the isEmail function available:

const async = require('async');
const oracledb = require('oracledb');

// source file that keeps the bundled JS code
const sourceFile = 'validator_bundled.js';

// the name of the module being created in the database
const moduleName = 'validator.js';

// database user
const dbUser = 'scott';

// database user password
const dbPwd = 'tiger';

// database connection string
const dbInstance = 'hr.us.example.com/hr.rdbms.dev.us.oracle.com';

oracledb.autoCommit = true;

const doConnect = function(cb) {
  oracledb.getConnection(
      {
        user: dbUser,
        password: dbPwd,
        connectString: dbInstance,
      },
      cb);
};

const doSetup = function(conn, cb) {
  conn.execute(
      'CREATE TABLE mylobs (id NUMBER, data CLOB)',
      function(err) {
        return cb(err, conn);
      });
};

const doCleanup = function(conn) {
  conn.execute(
      'DROP TABLE mylobs',
      function(err, result) {
        if (err) {
          console.error(err.message);
        }
        conn.close(function(err) {
          if (err) {
            console.error(err.message);
          }
        });
      });
};

const createModule = function(conn, cb) {
  const fs = require('fs');
  const data = fs.readFileSync(sourceFile, 'utf-8');
  conn.execute(
      'INSERT INTO mylobs VALUES (:id, :data)',
      {id: 1, data: data},
      function(err, result) {
        if (err) {
          return cb(err, conn);
        }
        conn.execute(
            'CREATE OR REPLACE JAVASCRIPT SOURCE NAMED "' + moduleName +
            '" USING CLOB SELECT data FROM mylobs WHERE id = 1',
            function(err, result) {
              if (err) {
                return cb(err, conn);
              }
              conn.execute(
                  'CREATE OR REPLACE FUNCTION isEmail (address VARCHAR2) ' +
                  'RETURN NUMBER AS LANGUAGE JAVASCRIPT NAME ' +
                  '\'validator.js.isEmail(a string) return number\';',
                  function(err, result) {
                    if (err) {
                      return cb(err, conn);
                    }
                    return cb(null, conn);
                  });
            });
      }
  );
};

async.waterfall(
    [
      doConnect,
      doSetup,
      createModule,
      doCleanup,
    ],
    function(err, conn) {
      if (err) {
        console.error('In waterfall error cb: ==>', err, '<==');
      }
      if (conn) {
        doCleanup(conn);
      }
    });

The load tool uses the package async to run the four functions doConnect, doSetup, createModule, and doCleanup one-after-an-other to deploy the bundled validator code.

The function doConnect connects to the database using the user dbUser, password dbPwd and the connection string in dbInstance.

The 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 CREATE FUNCTION. 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 the boolean type of JavaScript and TypeScript natively and would apply some implicit conversions if we specified it. So to make things clearer, we specified number and can look at the implicit conversions applied by Oracle Database MLE. Thus, if an email is valid, we get the value 1, and 0 otherwise.

Lastly, the function doCleanup drops the table mylobs, which is not needed anymore after the source is deployed.

Deployment

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, emails, with a single column (email) that contains text data. Some of the column values should be valid email addresses, others not.

SQL> CREATE TABLE emails (email VARCHAR2(100));
SQL> INSERT INTO emails VALUES ('some.body@googlemail.com');
SQL> INSERT INTO emails VALUES ('not really an email');
SQL> INSERT INTO emails VALUES ('somebody@gmail.com');
SQL> INSERT INTO emails VALUES ('.@googlemail.com');

Executing the deployed UDF

Now that we have our test data we can execute our first JavaScript UDF. The goal is to filter the table to remove every row that does not represent an email address. To do this, connect again to the database via SQL*Plus. Then issue:

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 that returns true.

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.