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.

Getting Started

In order to use the driver, it needs to be imported using the require function.

let oracledb = require('mle-js-oracledb');

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

  let conn = oracledb.defaultConnection();
  for (var row of conn.execute("SELECT EMPNO, ENAME FROM EMP").rows) {
    const empno = row[0];
    const ename = row[1];
    // ...
  }

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

Object Output Format

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

for (var row of conn.execute("SELECT EMPNO, ENAME FROM EMP", [], {
  outFormat : oracledb.OBJECT
}).rows) {
  var empno = row.EMPNO;
  var ename = row.ENAME;
  // ...
}

The property names of such result row objects follow Oracle's standard name-casing rules. It will commonly be uppercase, since 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 = conn.execute("SELECT :bind1, :bind2, :bind3 FROM DUAL", [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 = conn.execute("SELECT :foo, :bar, FROM DUAL", {
    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 which should be used for binding (type) and the value itself (val):

var result = conn.execute("SELECT :foo FROM DUAL", {
    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 = conn.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 strings, numbers, and Date.

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 = conn.execute("SELECT EMPNO, HIREDATE FROM 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

 

Mapping JavaScript Type to PL/SQL Types

default mappings in bold

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

 

Metadata

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

var rs = conn.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 a message property (IError.message) and an error code (IError.errorNum).

If errorNum exists, this is a standard Oracle Database 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.

var result = conn.execute("insert into emp values (7999, 'Hello', 'World', 7839, '18-DEC-85', 2500, 0, 30)");
var 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