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

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 query = "SELECT 9007199254740992 AS n FROM dual";
const options = { fetchInfo: { N: { type: oracledb.ORACLE_NUMBER } } };
const result = session.execute(query, [], options);
console.log(result.rows[0].N.add(new OracleNumber(7)));

In the next example we create an an OracleTimestamp and then add an OracleIntervalDayToSecond to it:

const timestamp = OracleTimestamp.fromString("2012-05-19", "YYYY-MM-DD");
console.log("Timestamp: ", timestamp);
const interval = new OracleIntervalDayToSecond(5, 2, 33, 55, 0);
console.log("TimeStamp + Interval: ", timestamp.addInterval(interval));