Wrapping PL/SQL types in 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-plsqltypes that ships with the database.

MLE allows importing SQL values from PL/SQL as well as fetching them from a SQL statement. By default, SQL values get converted to JavaScript values during that process, e.g. an ORACLE NUMBER gets converted to a JavaScript number. Sometimes it is required to have JavaScript values that behave exactly as if they were SQL values. The mle-js-plsqltypes module contains JavaScript APIs for such JavaScript objects that wrap PL/SQL objects.

Concretely, MLE offers the following PL/SQL types:

• OracleClob
• OracleBlob
• OracleNumber
• OracleDate
• OracleTimestamp
• OracleTimestampTZ
• OracleIntervalDayToSecond
• OracleIntervalYearToMonth

In order to use the PL/SQL types 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 plsqltypes = await import('mle-js-plsqltypes');

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

import plsqltypes from 'mle-js-plsqltypes';

The following example shows how to use the OracleNumber PL/SQL type to get an Oracle NUMBER from the MLE SQL driver and then add another Oracle NUMBER, namely 7, yielding the precise result of Oracle NUMBER arithmetic, 9007199254740999. If we performed the same operation with regular JavaScript number objects, we'd get an imprecise result caused by IEEE floating point arithmetic, namely 9007199254741000.

const {oracledb} = await import('mle-js-oracledb');
const OracleNumber = plsqltypes.OracleNumber;
const conn = oracledb.defaultConnection();
const query = "SELECT 9007199254740992 AS n FROM dual";
const options = { fetchInfo: { N: { type: oracledb.ORACLE_NUMBER } } };
const result = conn.execute(query, [], options);
console.log(result.rows[0].N.add(new OracleNumber(7)));

In the next example we import an Oracle TIMESTAMP WITH TIME ZONE from PL/SQL as OracleTimestampTZ and then add an OracleIntervalDayToSecond to it. Note that if we had imported the time stamp with the default conversion (to JavaScript Date), the addInterval method would not exist as it is not available on the JavaScript Date type. Plus, the precision would be limited to that of a JavaScript Date (millisecond precision) whereas OracleTimestampTZ can be configured to nanosecond precision if required.

const bindings = await import('mle-js-bindings');
const OracleIntervalDayToSecond = plsqltypes.OracleIntervalDayToSecond;
const interval = new OracleIntervalDayToSecond(5, 2, 33, 55, 0);
var timestamp = bindings.importValue('timestamp', bindings.JSTypes.ORACLE_TIMESTAMP_TZ);
console.log(timestamp);
console.log(timestamp.addInterval(interval));

Note that timestamp was previously exported from PL/SQL to MLE using dbms_mle.export_to_mle(ctx, 'timestamp', CURRENT_TIMESTAMP()).