Exchanging values between PL/SQL and JavaScript

Oracle Database allows executing user-provided JavaScript code since version 21c by using the Multilingual Engine (MLE). This documentation describes the JavaScript module mle-js-bindings that ships with the database.

The MLE Bindings module can be used to exchange values between PL/SQL and JavaScript. The module also takes care of converting values from PL/SQL types to JavaScript types and vice-versa automatically as required. Where the default conversion is not appropriate, you can also specify the target JavaScript type explicitly and use for example PL/SQL wrapper types (see the documentation of mle-js-plsqltypes).

Getting Started

In order to use the bindings module, it needs to be imported first. If executed as a snippet in DBMS_MLE, a dynamic import needs to be used and wrapped within an async function:

const bindings = await import('mle-js-bindings');

If used within MLE modules, the regular ECMAScript module import syntax can be used:

import bindings from 'mle-js-bindings';

The following example shows how to import values from PL/SQL into JavaScript and how to export JavaScript values back to PL/SQL:

const param1 = bindings.importValue("param1");
const result = param1 + 7;
bindings.exportValue("result", result);

In this example, the importValue function imports a number identified by its name, param1 which was previously exported from PL/SQL to MLE using dbms_mle.export_to_mle(ctx, 'param1', 42). Later on in the example, the exportValue function is used to export the result (identified as result). After the script has been executed, this result can be obtained by calling dbms_mle.import_from_mle(ctx, 'result', result) where result is a PL/SQL variable.

Note that when importing values, you can change the default JavaScript type of the imported value by using the second parameter and providing the desired type constant, e.g. STRING:

const param1 = bindings.importValue("param1", bindings.JSTypes.STRING);

Please refer to the conversion mapping tables in the documentation of mle-js-oracledb for an overview of default conversions and available alternatives.