MLE SQL Driver (mle-js-oracledb)

Executing SQL 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-oracledb that ships with the database.

If JavaScript is executed inside the database, SQL statements can be executed using an easy to use SQL driver. This driver is built-in into the JavaScript engine in the database. Because JavaScript is executed within the database, there is no need to establish a specific connection and the default connection object must be used. All SQL statements in the function are executed as CURRENT_USER.

As of Oracle 23ai, the JavaScript runtime comes with the following predefined symbols which remove the need for explicitly importing mle-js-oracledb:

  • oracledb: refers to mle-js-oracledb
  • session: shortcut for oracledb.defaultConnection()
  • soda: shortcut for session.getSodaDatabase()

Getting Started

Executing a SQL query and iterating over the resulting rows is as simple as

for (var row of session.execute("SELECT EMPNO, ENAME FROM SCOTT.EMP").rows) {
  const empno = row.EMPNO;
  const ename = row.ENAME;
  // ...
}

The execute function returns an IExecuteReturn object with the field rows which is an array. Since rows is an array, we can use the JavaScript for..of syntax.

By default, each row is represented as an Object. Note that if mle-js-oracledb gets imported with the deprecated require syntax, the default is Array, in order to stay compatible with Oracle 21c.

Array Output Format

A query result can be configured to return an Array representation (OUT_FORMAT_ARRAY) for each row.

for (var row of session.execute("SELECT EMPNO, ENAME FROM SCOTT.EMP", [], {
  outFormat : oracledb.OUT_FORMAT_ARRAY
}).rows) {
  const empno = row[0];
  const ename = row[1];
  // ...
}

The property names of such result row objects follow Oracle's standard name-casing rules. It will commonly be uppercase because most applications create tables using unquoted, case-insensitive names.

IN Binds

SQL and PL/SQL statements may contain bind parameters, indicated by colon-prefixed identifiers or numerals. These indicate where separately-specified values are substituted when the statement is executed. Bind variables can be used to substitute data, but not the text of the statement.

With IN binds, the bound data value, or current value of a JavaScript variable, is used during execution of the SQL statement.

var result = session.execute("SELECT :bind1, :bind2, :bind3", [42, "foo", true]).rows;

The position of the array values corresponds to the position of the SQL bind variables as they occur in the statement, regardless of their names.

Instead of binding by array, an object that names each bind value can be used. The attributes can be in any order, but their names must match the SQL bind parameter names. This is typically called "bind by name":

var result = session.execute("SELECT :foo, :bar", {
    foo : 42,
    bar : "foo"
  }
);

If needed, each IN bind parameter can be described by an object having explicit attributes for the bind direction (dir), the JavaScript type that should be used for binding (type) and the value itself (val):

var result = session.execute("SELECT :foo", {
    foo : {
      val : 42,
      dir : oracledb.BIND_IN,
      type : oracledb.NUMBER
    }
  }
);

OUT Binds

PL/SQL functions and procedures can return values through so-called OUT parameters. In order to retrieve such values, OUT binds can be specified when executing the statement.

var result = session.execute("BEGIN :a := 'foo'; :b := 42; END;", {
    a: { dir: oracledb.BIND_OUT, type: oracledb.STRING },
    b: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER }
  }
);

In this example, :a and :b are OUT binds. result.outBinds is an object containing a property for each OUT bind that was returned.

result.outBinds will appear as undefined if the statement doesn't return any bind values.

Mapping between JavaScript and Oracle types

By default, Oracle character, number, and date columns are selected directly into JavaScript string, number, and Date values.

The default mapping for NUMBER and DATE types can be changed using the fetchInfo property. Specifically, it allows for returning NUMBER and DATE types as string.

var result = session.execute("SELECT EMPNO, HIREDATE FROM SCOTT.EMP", [],  {
    fetchInfo : {
      "EMPNO" : { type : oracledb.STRING},    // return the number as a string
      "HIREDATE" : { type : oracledb.STRING } // return the date as a string
    }
  }
);

For NUMBER types, this property helps avoid situations where using JavaScript types can lead to a loss of precision.

Available Type Conversions

The following two tables show the available conversion paths between PL/SQL types and JavaScript types in both directions as well as the default conversion in case no specific type is specified. These (default) conversion rules apply to columns in SELECT statements as well as IN / INOUT / OUT binds and bind values imported from/to PL/SQL (see mle-js-bindings).

Mapping PL/SQL Type to JavaScript Types

default mappings in bold

PL/SQL Type JavaScript Types
NUMBER number
OracleNumber
BOOLEAN boolean
VARCHAR2 string
NVARCHAR2 string
CHAR string
NCHAR string
CLOB OracleClob
string
NCLOB OracleClob
string
BLOB OracleBlob
Uint8Array
RAW Uint8Array
DATE Date
OracleDate
TIMESTAMP Date
OracleTimestamp
TIMESTAMP WITH TIMEZONE Date
OracleTimestampTZ
TIMESTAMP WITH LOCAL TIMEZONE Date
OracleTimestampTZ
INTERVAL YEAR TO MONTH OracleIntervalYearToMonth
INTERVAL DAY TO SECOND OracleIntervalDayToSecond
NULL null
JSON object (JavaScript object or array) or corresponding JavaScript type for the JSON value
Named Type or Collection DBObject

 

Mapping JavaScript Type to PL/SQL Types

default mappings in bold

JavaScript Types PL/SQL Type
number NUMBER
JSON
boolean NUMBER
BOOLEAN
JSON
string VARCHAR2
CHAR
NCHAR
NCHAR2
CLOB
NCLOB
JSON
OracleClob CLOB
NCLOB
JSON
OracleBlob BLOB
JSON
Uint8Array RAW
BLOB
JSON
Date DATE
TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
JSON
OracleDate DATE
JSON
OracleTimestamp TIMESTAMP
JSON
OracleTimestampTZ TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
JSON
OracleIntervalYearToMonth INTERVAL YEAR TO MONTH
JSON
OracleIntervalDayToSecond INTERVAL DAY TO SECOND
JSON
null NULL
JSON
undefined NULL
JSON
object JSON
DBObject Named Type or Collection

 

Metadata

For SELECT statements, the IResultSet.metaData function returns an array of objects describing details of the columns of the select list.

var rs = session.execute("SELECT * FROM EMP");
for (var md of rs.metaData) {
   var columnName = md.name;
   var columnDBType = md.dbType; // ORACLE Database Type, e.g. oracledb.DB_TYPE_NUMBER
   var columnFetchType = md.fetchType // JavaScript Type which will be fetched, e.g. oracledb.STRING
   var isNullable = md.nullable;
   // ...
}

The name, dbType, fetchType and nullable properties are always available. Additionally, the following properties are available for some data types:

  • byteSize for Oracle character types,
  • precision for NUMBER and TIMESTAMP types, and
  • scale for the NUMBER type.

Errors

Functions communicate errors by throwing an exception object of type IError. Those exceptions can be handled using the try-catch statement.

try {
    for (let row of sql.execute("SELECT * FROM").rows) {
        // never reached
        ...
    }
    ...
} catch (err) {
    return err.errorNum + " " + err.message;
}

The IError object contains name, message and, optionally, error number and offset.

If errorNum exists, it is a standard Oracle Database error, while the offset explains which place in the SQL statement triggered the error. If not, it is an error specific to this driver (mle-js-oracledb error).

Database Error messages may look like this:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

mle-js-oracledb error messages may look like this:

NJS-004: invalid value for property outFormat

Executing non-SELECT Statements

Non-SELECT statements can be executed the same way as SELECT statements.

let result = session.execute("insert into emp values (7999, 'Hello', 'World', 7839, '18-DEC-85', 2500, 0, 30)");
let rowsInserted = result.rowsAffected;

For DML statements, rowsAffected contains the number of rows affected, for example the number of rows inserted. For non-DML statements, rowsAffected will appear as undefined. Also, rows will appear as undefined for any non-SELECT statement.

Managing Transactions

Note that, compared to node-oracledb, the (default) connection object in mle-js-oracledb cannot be closed. This means that mle-js-oracledb does not perform any implicit commit or rollback of transactions and explicit calls to commit or rollback need to be made instead. This is because the code in mle-js-oracledb executes in the scope of the transaction that called into MLE in the first place. Similarly, mle-js-oracledb does not support auto-commit, neither as a connection property nor as an option in execute.

Settings

Member Visibility

Theme