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, one 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 using the require function.

let bindings = require('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:

let param1 = bindings.importValue("param1");
let 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, one can change the default JavaScript type of the imported value by using the second parameter and providing the desired type constant, e.g. STRING:

let 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.