JavaScript in the Oracle Database

This page describes how to run JavaScript in an Oracle Database using the Oracle Database Multilingual Engine (MLE). MLE is powered by GraalVM: it can run JavaScript code in Oracle Database 23ai (and later) on Linux x64.

Preparation

  1. Get an Oracle Cloud Free Tier account and choose a home region that offers Oracle Database 23ai or later.
  2. Provision an Autonomous Database Transaction Processing instance and start your favorite SQL IDE, such as one of the following:

    Prerequisites: see the section titled “Prerequisites for using JavaScript in Oracle Database 23c Free - Developer Release” in Introduction to JavaScript in Oracle Database 23c Free - Developer Release. (Although targeted at an earlier release, they are still valid.)

Examples

The examples assume that you have created a database account with the necessary privileges to create MLE modules and environments, as well as PL/SQL functions, procedures, and packages. The account has also been granted the privilege to execute JavaScript code. (For more information, see MLE Security.)

  1. The first example shows how to create an MLE module.

     create or replace mle module helper_module_inline
 language javascript as

 /**
 * Convert a delimited string into key-value pairs and return JSON
 * @param {string} inputString - the input string to be converted
 * @returns {JSON}
 */
 function string2obj(inputString) {
     if ( inputString === undefined ) {
         throw `must provide a string in the form of key1=value1;...;keyN=valueN`;
     }
     let myObject = {};
     if ( inputString.length === 0 ) {
         return myObject;
     }
     const kvPairs = inputString.split(";");
     kvPairs.forEach( pair => {
         const tuple = pair.split("=");
         if ( tuple.length === 1 ) {
             tuple[1] = false;
         } else if ( tuple.length != 2 ) {
             throw "parse error: you need to use exactly one '=' between " +
                     "key and value and not use '=' in either key or value";
         }
         myObject[tuple[0]] = tuple[1];
     });
     return myObject;
 }

 /**
 * Convert a JavaScript object to a string
 * @param {object} inputObject - the object to transform to a string
 * @returns {string}
 */
 function obj2String(inputObject) {
     if ( typeof inputObject != 'object' ) {
         throw "inputObject isn't an object";
     }
     return JSON.stringify(inputObject);
 }

 export { string2obj, obj2String }
 /

    Note: MLE only supports ECMA Script modules. CommonJS and other popular techniques are not available.

    The above module defines two functions:

    1. string2obj()
    2. obj2Str()

    The export keyword at end of the module means that these functions can be called by other MLE modules, PL/SQL, as well as SQL.

  2. The second example shows how to make these functions available in SQL and PL/SQL. Functions within JavaScript modules cannot be called directly in SQL and PL/SQL. Instead, you create a call specification for them. A call specification can be thought of as a “wrapper” in PL/SQL. Depending on your requirements you can either use standalone functions and procedures, or group them in a package. Since these two functions belong to the helper_module they go in a package.

     create or replace package helper_pkg as

     function string2obj(
         p_inputString varchar2
     ) return JSON
         as mle module helper_module_inline
         signature 'string2obj';

     function obj2String(
         p_inputObject JSON
     ) return varchar2
         as mle module helper_module_inline
         signature 'obj2String';

 end helper_pkg;
 /

    With the call specification in place you can convert a string, delimited by ; into a JavaScript object and return it to the caller, as follows

     select json_serialize(
     helper_pkg.string2obj(
         'order_id=1;order_date=2023-04-24T10:27:52;order_mode=mail;promotion_id=1'
     )
     pretty
 ) string2obj;

    You should see the following output:

     STRING2OBJ
 ----------------------------------------
 {
     "order_id" : "1",
     "order_date" : "2023-04-24T10:27:52",
     "order_mode" : "mail",
     "promotion_id" : "1"
         }

  3. Beginning with Oracle Database 23.3 it is possible to provide the JavaScript code in line with the call specification if all you need to do is invoke a standard piece of JavaScript code, as shown below:

     create or replace function date_to_epoch(
   "ts" timestamp
 ) return number
 as mle language javascript
 ;
 /

    Inline JavaScript procedures have some limitations, e.g. they cannot import other MLE modules (they can, however require the libraries that ship with MLE). This is why you should consider using MLE modules for writing more complex logic and/or making it re-usable within other modules. You can now invoke the date_to_epoch() function directly from SQL as shown in this example:

     select
   date_to_epoch (
     to_timestamp('2000-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
   ) seconds_since_epoch
 /

  4. You can invoke MLE modules from other modules. This is typically the case when CREATING more complex logic, or third-party modules. Load the popular validator.js module into the database as described in Using JavaScript community modules in Oracle Database 23c Free - Developer Release—adhering to the caveats such as license and compliance. Now you can import the ECMASCript (ESM) version of the module from your favorite Content Delivery Network (CDN), as described in the article above. Assume the validator module is created as validator_module in the database. You can use the module in your own code as follows:

     create or replace mle module validator_usage_demo_module
 language javascript as

 import validator from 'validator';

 export function emailCheck(str) {
   return validator.isEMail(str);
 }

    The element that maps the import name (validator) to the module is called an MLE environment. Because there is no file system that MLE can use to resolve import names, you have to explicitly declare the mapping, as follows:

     create or replace mle env validator_env imports (
   'validator' module validator_module
 );

    The MLE environment helps map import names to modules in call specifications. If you want to expose the SQL function emailCheck(), then create the following call specification:

     create or replace function email_check(p_str varchar2)
 return boolean
 as mle module validator_usage_demo_module
 env validator_env
 signature 'emailCheck';
 /

    Now you can call email_check almost anywhere.

Type Conversions

Knowing how type conversions work is very important—whenever your code “leaves” SQL or PL/SQL, the types must be converted to their analogous JavaScript types. (For more information about Type Conversions, see MLE Type Conversions.)

You may know that a JavaScript Number is implemented as a double-precision 64-bit binary format IEEE 754 value. There might be problems with the maximum number it can store as well as loss of precision. The following examples demonstrate the potential loss of precision.

create table demo_table (
  id      number constraint pk_demo_table primary key,
  value   number not null
);

insert into demo_table(
  id,
  value
) values
  (1, 0.1),
  (2, 0.2);

create or replace procedure precision_loss(
  "id1" demo_table.id%type,
  "id2" demo_table.id%type)
as mle language javascript
;
/

begin precision_loss(1, 2); end;
/

The precision_loss() procedure prints a value of 0.30000000000000004 to the console (instead of 0.3). To circumvent the loss of precision you can follow many strategies laid out in Calling PL/SQL and SQL from the MLE JavaScript SQL Driver. One approach is to instruct the SQL driver to return the number as the type OracleNumber: a wrapper type for Oracle’s Number data type in JavaScript:

create or replace procedure precision_preserved(
  "id1" demo_table.id%type,
  "id2" demo_table.id%type)
as mle language javascript
;
/

begin precision_preserved(1, 2); end;
/

The precision_preserved() procedure now prints the correct result (0.3) to the console. Wrapper data types are documented in the mle-js-plsqltypes module. Whenever you require number precision, consider using one of the wrapper data types.

Dynamic JavaScript execution

Oracle Database 21c introduced MLE in form of the DBMS_MLE package. It is still available, however its focus has shifted to framework developers and embedding into REPL (Read Eval Print Loop) systems. APEX, Database Actions and others use the package under the covers.

Here is a basic example how to code with DBMS_MLE:

set serveroutput on;
declare
    l_ctx           dbms_mle.context_handle_t;
    l_source_code   clob;
begin
    -- Create execution context for MLE execution
    l_ctx    := dbms_mle.create_context();

    -- using q-quotes to avoid problems with unwanted string termination
    l_source_code :=
q'~

    const result = session.execute(
        `select 'hello, world'`,
        [],
        {
            outFormat: oracledb.OUT_FORMAT_ARRAY
        }
    );

    const message = result.rows[0][0];

    console.log(message);

   ~';
    dbms_mle.eval(
        context_handle => l_ctx,
        language_id => 'JAVASCRIPT',
        source => l_source_code,
        source_name => 'example01'
    );

    dbms_mle.drop_context(l_ctx);
exception
    when others then
        dbms_mle.drop_context(l_ctx);
        raise;
end;
/

The anonymous PL/SQL block prints hello, world to the console. If you want to use modules other than those built into MLE, you must use MLE environments. Building on the foundation laid by the previously created validator_env, you can verify an email using DBMS_MLE as follows:

set serveroutput on;
declare
    l_ctx           dbms_mle.context_handle_t;
    l_source_code   clob;
begin
    -- Create execution context for MLE execution and provide an environment
    l_ctx    := dbms_mle.create_context('VALIDATOR_ENV');

    -- using q-quotes to avoid problems with unwanted string termination
    l_source_code :=
q'~
(async() => {
  const { default: validator } = await import ('validator');
  const str = 'not an email address';

  console.log(`Is ${str} a valid email address? ${validator.isEmail(str)}`);
})()
   ~';
    dbms_mle.eval(
        context_handle => l_ctx,
        language_id => 'JAVASCRIPT',
        source => l_source_code,
        source_name => 'example02'
    );

    dbms_mle.drop_context(l_ctx);
exception
    when others then
        dbms_mle.drop_context(l_ctx);
        raise;
end;
/

You cannot use static import statements because DBMS_MLE evaluates JavaScript code in script mode. However, you can use dynamic imports as illustrated in the snippet above.

APEX and Database Actions are great tools because they allow you to focus on writing JavaScript (l_source_code in the above example).

Connect with us