Executing SQL Callouts from JavaScript

If JavaScript is executed inside the database, SQL statements can be executed from JavaScript using an easy to use SQL driver. Such SQL statements are also referred to as callouts.

In this document, we describe a SQL driver that is built into the Oracle Database Multilingual.

Getting Started

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

var sql = require('@oracle/sql');

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

  for (var row of sql.execute("SELECT EMPNO, ENAME FROM EMP").rows) {
    var empno = row[0];
    var ename = row[1];
    ...
  }

Because JavaScript is executed within the database, there is no need to establish a connection.

The execute function returns a Result with the field Result.rows of type ResultSet. A ResultSet represents all rows returned by the database for a query. ResultSet implements the JavaScript iterator protocol. This allows it to be used with the JavaScript for..of syntax.

By default, each row is represented as a regular JavaScript Array of attribute values. Our example query returns rows with two attributes that are represented as arrays with two elements:

[7369, "SMITH"]
[7499, "ALLEN"]
...

A ResultSet always needs to be explicitly closed (see ResultSet.close}) in order to release resources. A ResultSet can also be closed using Result.close. However, if used in the for..of construct (s.a.), the ResultSet is closed implicitly, i.e. there is no need to call ResultSet.close.

Please note that all SQL statements are executed as CURRENT_USER.

Object Output Format

As we have seen above, each row in a ResultSet is represented as an Array by default. However, a ResultSet can also be configured to return an Object representation (OutFormat.OBJECT) for each row.

function simpleSqlWithObjectFormat() {
  var res = "";

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

A row is then represented as a regular JavaScript Object with a separate property for each attribute.

{ EMPNO: 7369, ENAME: 'SMITH' }
{ EMPNO: 7499, ENAME: 'ALLEN' }

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

Executing non-SELECT Statements

Besides SELECT queries, other forms of SQL statements (e.g. INSERT, UPDATE, CREATE TABLE) can be executed with the driver as well. The execute method executes all valid forms of SQL statements:

var result = sql.execute("INSERT INTO emp VALUES (7999, 'Hello', 'World', 7839, '18-DEC-85', 2500, 0, 30)");

var rowsInserted = result.rowsAffected;

For DML statements, the rowsAffected property of the Result contains the number of rows affected. In our INSERT example, it reflects the number of rows inserted. For non-DML statements, rowsAffected will appear as undefined. Also, the rows property of the ResultSet will appear as undefined for any non-SELECT statement.

Please note that DML statements do not work in user-defined functions that are called by a SQL query. They can only be used in stored procedures that are invoked by a top-level call statement.

Mapping between JavaScript and Oracle types

Oracle character and number are converted directly into JavaScript strings and numbers (see conversion documentation.

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 = sql.execute("SELECT EMPNO, HIREDATE FROM EMP", [],  {
    fetchInfo : {
      "EMPNO" : { type : sql.JSTypes.STRING }, // return the number as a string
      "HIREDATE" : { type : sql.JSTypes.STRING } // return the date as a string
    }
  }
);
...
result.close();

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

Metadata

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

  var rs = sql.execute("SELECT * FROM EMP").rows;
  for (var md of rs.metadata()) {
     var columnName = md.name;
     var columnType = md.type;
     var isNullable = md.nullable;
     ...
   }
  rs.close();

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

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

IN Binds

SQL and PL/SQL statements may contain bind variables, 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.

When a SQL statement with IN binds is executed, bind parameters are substituted with JavaScript values that are passed in as separate argument to execute. Bind parameters are passed in an array by default.

var result = sql.execute("INSERT INTO emp VALUES (7999, 'Joe Doe', :job, 7839, '18-DEC-85', :sal, 0, 30)", ['Artist', 42000]);
...
result.rowsAffected;

The position of the value in the array determines the SQL bind variable it substitutes: 42000 is at the second position and will therefore substitute the second bind variable :sal.

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

var result = sql.execute("SELECT :foo, :bar FROM DUAL", {
  foo : 42,
  bar : "foo"
});
...
result.close();

If needed, each IN bind parameter can be described by an object having explicit attributes for the bind direction (dir), the SQL type of the value in the database (dbType) and the value itself (val):

var result = sql.execute("SELECT :foo FROM DUAL", {
  foo : {
    val : 42,
    dir : sql.BindDirection.BIND_IN,
    dbType : sql.DBTypes.BINARY_DOUBLE
  }
});
...
result.close();

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 = sql.execute("BEGIN testproc(:i, :io, :o); END;", {
    i : { val : 'Hello' },
    io : {
      dir : sql.BindDirection.BIND_INOUT,
      val : 'World'
    },
    o : {
      dir : sql.BindDirection.BIND_OUT,
      dbType : sql.DBTypes.CHAR,
      maxSize : 10
    }
  }
);

For this example to work, the corresponding testproc procedure needs to be created:

CREATE OR REPLACE PROCEDURE testproc (p_in IN VARCHAR2, p_inout IN OUT VARCHAR2, p_out OUT NUMBER)
  AS
BEGIN
  p_inout := p_in || p_inout;
  p_out := 101;
END;
/

In the above example, :i is an IN bind. :io is an IN/OUT bind and :o is an OUT bind. `rs.outBinds`` is an object containing a property for each OUT bind that was returned.

Please note that there is no need to close the result because the testproc procedure doesn't return a value other than through OUT Binds.

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

Errors

Functions of the SQL driver communicate errors by throwing an exception object of type OracleError. 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.code + " " + err.message;
}

The OracleError object contains a message property (OracleError.message) and an error code (OracleError.code).

If the OracleError.code property is SQL_ERROR, execution of the query on the Oracle Database failed. In this case, the OracleError.message property contains a standard Oracle error message. Such errors may consist of multiple lines each with a prefix like ORA or PLS.

Single line SQL_ERRORs may look like this: ``` ORA-00903: invalid table name

Multi line SQL_ERRORs may look liks this: ```
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

An OracleError with an error code of INVALID_ARGUMENT or INVALID_OPERATION, on the other hand, indicates an error specific to this driver.

Transactions

Typically, DDL or DML statements that modify the database are executed within stored procedures or functions that are executed using the top-level CALL statement.

CALL mypackage.myprocedure();

Within a procedure or a function, the first DDL or DML statement implicitly starts a new transaction. In order to commit or rollback a transaction, the SQL statements COMMIT or ROLLBACK can be used, respectively. For example,

sql.execute("INSERT...");
sql.execute("UPDATE...");
sql.execute("COMMIT");

The Oracle database does not allow for executing a DML or DDL statement when a function is called as a user-defined function, i.e. from a SQL statement. In order to allow for executing such statements, the JavaScript function has to execute the statements in an autonomous transaction that is independent of the transaction of the invoking SQL statement. This can be done using the sql.transactionally function provided by the driver.

sql.transactionally(() => {
    sql.execute("INSERT...");
    sql.execute("UPDATE...");
});

This guarantees that both the INSERT and UPDATE statements are executed in an autonomous transaction. If the first call to execute throws an exception, the autonomous transaction is aborted and the exception is rethrown. If the function terminates without throwing an error, the autonomous transaction is committed and the value returned by the higher-order function is returned.

TypeScript

In order to import the sql driver into TypeScript, the import statement instead of the require call needs to be used.

import * as sql from "@oracle/sql";

This, however, requires a local installation of the corresponding TypeScript module. Use

$ npm install /home/oracle/@types/oracle__sql

to install the module locally.