MLE SQL Driver(mle-js-oracledb-common)
    Preparing search index...

    MLE SQL Driver(mle-js-oracledb-common)

    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()

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

    var rows = session.execute("SELECT EMPNO, ENAME FROM SCOTT.EMP").rows;
    if (rows) {
      for (var row of 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.

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

    var rows = session.execute("SELECT EMPNO, ENAME FROM SCOTT.EMP", [], {
      outFormat : oracledb.OUT_FORMAT_ARRAY}
    ).rows;
    if (rows) {
      for (var row of 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.

    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 rows = 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
        }
      }
    );

    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.

    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.

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

    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

     

    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

     

    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");
    if (rs.metaData) {
      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.

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

    try {
      var rows = session.execute("SELECT * FROM").rows
      if (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

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

    var result = session.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.

    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

    On This Page

    Executing SQL in JavaScript