node-oracledb

13. Bind Parameters for Prepared Statements

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 parameters are also called bind variables.

Using bind parameters is recommended in preference to constructing SQL or PL/SQL statements by string concatenation. This is for performance and security.

Inserted data that is bound is passed to the database separately from the statement text. It can never be executed. This means there is no need to escape bound data inserted into the database.

If a statement is executed more than once with different values for the bind parameters, Oracle can re-use context from the initial execution, thus improving performance. However, if similar statements contain hard coded values instead of bind parameters, Oracle sees the statement text is different and would be less efficient.

IN binds are values passed into the database. OUT binds are used to retrieve data. IN OUT binds are passed in, and may return a different value after the statement executes.

OUT bind parameters for RETURNING INTO clauses will always return an array of values. See DML RETURNING Bind Parameters.

13.1 IN Bind Parameters

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

In this example, the SQL bind parameters :country_id and :country_name can be bound to values in node-oracledb using an array. This is often called "bind by position":

connection.execute(
  "INSERT INTO countries VALUES (:country_id, :country_name)",
  [90, "Tonga"],
  function(err, result)
  {
    if (err)
      console.error(err.message);
    else
      console.log("Rows inserted " + result.rowsAffected);
  });

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. This is still true even if the bind variables are named like :0, :1 etc. The following snippet will fail because the country name needs to be the second entry of the array so it becomes the second value in the INSERT statement

connection.execute(
  "INSERT INTO countries VALUES (:1, :0)",
  ["Tonga", 90],  // fail
  . . .

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":

connection.execute(
  "INSERT INTO countries VALUES (:country_id, :country_name)",
  {country_id: 90, country_name: "Tonga"},
  function(err, result)
  {
    if (err)
      console.error(err.message);
    else
      console.log("Rows inserted " + result.rowsAffected);
  });

The default direction for binding is BIND_IN. The datatype used for IN binds is inferred from the bind value.

If desired, each IN bind parameter can be described by an object having explicit attributes for the bind direction (dir), the datatype (type) and the value (val):

var oracledb = require('oracledb');
. . .
connection.execute(
  "INSERT INTO countries VALUES (:country_id, :country_name)",
  {
    country_id: { val: 90, dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    country_name: { val: "Tonga", dir: oracledb.BIND_IN, type:oracledb.STRING }
  },
  function(err, result)
  {
    if (err)
      console.error(err.message);
    else
      console.log("Rows inserted " + result.rowsAffected);
  });

For IN binds the direction must be BIND_IN. The type can be STRING, NUMBER, DATE matching the data. The type BUFFER can bind a Node.js Buffer to an Oracle Database RAW type. The type CURSOR cannot be used with IN binds.

13.2 OUT and IN OUT Bind Parameters

For each OUT and IN OUT bind parameter, a bind value object containing val, dir, type and maxSize properties is used. For PL/SQL Associative Array binds a maxArraySize property is required.

The dir attribute should be BIND_OUT or BIND_INOUT.

For BIND_INOUT parameters, the type attribute should be STRING, NUMBER, DATE or BUFFER.

For BIND_OUT parameters the type attribute should be STRING, NUMBER, DATE, CURSOR, BLOB, CLOB or BUFFER.

The type BUFFER is used to bind an Oracle Database RAW to a Node.js Buffer.

If type is not specified then STRING is assumed.

A maxSize should be set for STRING OUT or IN OUT binds. This is the maximum number of bytes the bind parameter will return. If the output value does not fit in maxSize bytes, then an error such ORA-06502: PL/SQL: numeric or value error: character string buffer too small or NJS-016: buffer is too small for OUT binds occurs.

A default value of 200 bytes is used when maxSize is not provided for OUT binds of type STRING or BUFFER.

The results parameter of the execute() callback contains an outBinds property that has the returned OUT and IN OUT binds as either array elements or property values. This depends on whether an array or object was initially passed as the bindParams parameter to the execute() call. That is, if bind-by-name is done by passing an object with keys matching the bind variable names, then the OUT bind is also returned as an object with the same keys. Similarly, if bind-by-position is done by passing an array of bind values, then the OUT and IN OUT binds are in an array with the bind positions in the
same order.

Here is an example program showing the use of binds:

var oracledb = require('oracledb');
. . .
var bindVars = {
  i:  'Chris', // default direction is BIND_IN. Datatype is inferred from the data
  io: { val: 'Jones', dir: oracledb.BIND_INOUT },
  o:  { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
}
connection.execute(
  "BEGIN testproc(:i, :io, :o); END;",
  bindVars,
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

Given the creation of TESTPROC using:

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;
/
show errors

The Node.js output would be:

{ io: 'ChrisJones', o: 101 }

An alternative to the named bind syntax is positional syntax:

var bindVars = [
  'Chris',
  { val: 'Jones', dir: oracledb.BIND_INOUT },
  { type: oracledb.NUMBER, dir: oracledb.BIND_OUT }
];

Mixing positional and named syntax is not supported. The following will throw an error:

var bindVars = [
  'Chris',
  { val: 'Jones', dir: oracledb.BIND_INOUT },
  { o: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT } }  // invalid
];

13.3 DML RETURNING Bind Parameters

Bind parameters from "DML RETURNING" statements (such as INSERT ... RETURNING ... INTO ...) can use STRING, NUMBER or DATE for the OUT type.

For STRING types, an error occurs if maxSize is not large enough to hold a returned value.

Note each DML RETURNING bind parameter is returned as an array containing zero or more elements. Application code that is designed to expect only one value could be made more robust if it confirms the returned array length is not greater than one. This will help identify invalid data or an incorrect WHERE clause that causes more results to be returned.

Oracle Database DATE, TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE types can be bound as DATE for DML RETURNING. These types and ROWID can also be bound as STRING.

An example of DML RETURNING binds is:

var oracledb = require('oracledb');
. . .
connection.execute(
   "UPDATE mytab SET name = :name "
 + "WHERE id = :id "
 + "RETURNING id, name INTO :rid, :rname",
  {
    id:    1001,
    name:  "Krishna",
    rid:   { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
    rname: { type: oracledb.STRING, dir: oracledb.BIND_OUT }
  },
  function(err, result)
  {
    if (err) { console.error(err); return; }
    console.log(result.outBinds);
  });

If the WHERE clause matches one record, the output would be like:

{ rid: [ 1001 ], rname: [ 'Krishna' ] }

When a couple of rows match, the output could be:

{ rid: [ 1001, 1001 ], rname: [ 'Krishna', 'Krishna' ] }

If the WHERE clause matches no rows, the output would be:

{ rid: [], rname: [] }

13.4 REF CURSOR Bind Parameters

Oracle REF CURSORS can be fetched in node-oracledb by binding a CURSOR to a PL/SQL call. The resulting bind variable becomes a ResultSet, allowing rows to be fetched using getRow() or getRows(). The ResultSet can also be converted to a Readable Stream by using toQueryStream().

If using getRow() or getRows() the result set must be freed using close() when all rows have been fetched, or when the application does not want to continue getting more rows. If the REF CURSOR is set to NULL or is not set in the PL/SQL procedure then the returned ResultSet is invalid and methods like getRows() will return an error when invoked.

When using Oracle Database 11gR2 or greater, then prefetchRows can be used to tune the performance of fetching REF CURSORS.

Given a PL/SQL procedure defined as:

CREATE OR REPLACE PROCEDURE get_emp_rs (
  p_sal IN NUMBER,
  p_recordset OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_recordset FOR
    SELECT first_name, salary, hire_date
    FROM   employees
    WHERE  salary > p_sal;
END;
/

This PL/SQL procedure can be called in node-oracledb using:

var oracledb = require('oracledb');
var numRows = 10;  // number of rows to return from each call to getRows()
var plsql = "BEGIN get_emp_rs(:sal, :cursor); END;";
var bindvars = {
  sal:  6000,
  cursor:  { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
}
connection.execute(
  plsql,
  bindvars,
  function(err, result)
  {
    if (err) { . . . }
    fetchRowsFromRS(connection, result.outBinds.cursor, numRows);
  });
function fetchRowsFromRS(connection, resultSet, numRows)
{
  resultSet.getRows( // get numRows rows
    numRows,
    function (err, rows)
    {
      if (err) {
         . . .                        // close the result set and release the connection
      } else if (rows.length > 0) {   // got some rows
        console.log(rows);            // process rows
        if (rows.length === numRows)  // might be more rows
          fetchRowsFromRS(connection, resultSet, numRows);
        else                          // got fewer rows than requested so must be at end
          . . .                       // close the result set and release the connection
      } else {                        // else no rows
          . . .                       // close the result set and release the connection
      }
    });
}

See refcursor.js
for a complete example.

To convert the REF CURSOR ResultSet to a stream, use
toQueryStream(). With the PL/SQL and bind values
from the previous examples, the code would become:

connection.execute(
  plsql,
  bindvars,
  function(err, result)
  {
    if (err) { . . . }
    fetchRCFromStream(connection, result.outBinds.cursor);
  });
function fetchRCFromStream(connection, cursor)
{
  var stream = cursor.toQueryStream();
  stream.on('error', function (error) {
    // console.log("stream 'error' event");
    console.error(error);
    return;
  });
  stream.on('metadata', function (metadata) {
    // console.log("stream 'metadata' event");
    console.log(metadata);
  });
  stream.on('data', function (data) {
    // console.log("stream 'data' event");
    console.log(data);
  });
  stream.on('end', function () {
    // console.log("stream 'end' event");
    connection.release(
      function(err) {
        if (err) {
          console.error(err.message);
        }
      });
  });
}

The connection must remain open until the stream is completely read.
Query results must be fetched to completion to avoid resource leaks.
The ResultSet close() call for streaming query results will be
executed internally when all data has been fetched.

13.5 LOB Bind Parameters

LOBs can be bound with dir set to BIND_OUT. Binding LOBs with
BIND_IN or BIND_INOUT is currently not supported.

To use the node-oracledb Lob API, CLOB variables should be bound with
type CLOB. BLOB variables should be bound
with type BLOB.

PL/SQL OUT CLOB parameters can also be bound as STRING but this is
not recommended because the returned length is limited to the maximum
limit of maxSize.

The following code snippet binds a PL/SQL OUT CLOB parameter and
returns a Lob to node-oracledb:

/*
  CREATE TABLE mylobs (id number, c CLOB);
  INSERT INTO mylobs . . .
  CREATE OR REPLACE PROCEDURE myproc (p_id IN NUMBER, p_c OUT CLOB) AS
  BEGIN
    SELECT c INTO p_c FROM mylobs WHERE id = p_id;
  END;
*/
var oracledb = require('oracledb');
. . .
connection.execute(
  "BEGIN myproc(:id, :cbv); END;",
  { id: 1, cbv: { type: oracledb.CLOB, dir: oracledb.BIND_OUT} },
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    var lob = result.outBinds.cbv;
    // Use Node.js Streams API to fetch the CLOB data from lob
    . . .

See Working with CLOB and BLOB Data for more information
on working with Lob streams.

13.6 PL/SQL Collection Associative Array (Index-by) Bind Parameters

Arrays of strings and numbers can be bound to PL/SQL IN, IN OUT, and
OUT parameters of PL/SQL INDEX BY associative array type. This type
was formerly called PL/SQL tables or index-by tables. This method of
binding can be a very efficient way of transferring small data sets.
Note PL/SQL's VARRAY and nested table collection types cannot be
bound.

Given this table and PL/SQL package:

DROP TABLE mytab;
CREATE TABLE mytab (id NUMBER, numcol NUMBER);
CREATE OR REPLACE PACKAGE mypkg IS
  TYPE numtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  PROCEDURE myinproc(p_id IN NUMBER, vals IN numtype);
  PROCEDURE myoutproc(p_id IN NUMBER, vals OUT numtype);
END;
/
CREATE OR REPLACE PACKAGE BODY mypkg IS
  PROCEDURE myinproc(p_id IN NUMBER, vals IN numtype) IS
  BEGIN
    FORALL i IN INDICES OF vals
      INSERT INTO mytab (id, numcol) VALUES (p_id, vals(i));
  END;
  PROCEDURE myoutproc(p_id IN NUMBER, vals OUT numtype) IS
  BEGIN
    SELECT numcol BULK COLLECT INTO vals FROM mytab WHERE id = p_id ORDER BY 1;
  END;
END;
/

To bind an array in node-oracledb using "bind by name" syntax for insertion into mytab use:

connection.execute(
  "BEGIN mypkg.myinproc(:id, :vals); END;",
  {
    id: 1234,
    vals: { type: oracledb.NUMBER,
             dir: oracledb.BIND_IN,
             val: [1, 2, 23, 4, 10]
          }
  }, . . .

Alternatively, "bind by position" syntax can be used:

```javascript
connection.execute(
"BEGIN mypkg.myinproc(:id, :vals); END;",
[
1234,
{ type: oracledb.NUMBER,
dir: oracledb.BIND_IN,
val: [1, 2, 23, 4, 10]
}
],

function (err) { . . . });

After executing either of these `mytab` will contain:

ID         NUMCOL

  1234          1
  1234          2
  1234         23
  1234          4
  1234         10

The [`type`](#executebindParams) must be set for PL/SQL array binds.
It can be set to [`STRING`](#oracledbconstantsnodbtype) or [`NUMBER`](#oracledbconstantsnodbtype).
For OUT and IN OUT binds, the [`maxArraySize`](#executebindParams)
bind property must be set.  Its value is the maximum number of
elements that can be returned in an array.  An error will occur if the
PL/SQL block attempts to insert data beyond this limit.  If the PL/SQL
code returns fewer items, the JavaScript array will have the actual
number of data elements and will not contain null entries.  Setting
`maxArraySize` larger than needed will cause unnecessary memory
allocation.
For IN OUT binds, `maxArraySize` can be greater than the number of
elements in the input array.  This allows more values to be returned
than are passed in.
For IN binds, `maxArraySize` is ignored, as also is `maxSize`.
For `STRING` IN OUT or OUT binds, the string length
[`maxSize`](#executebindParams) property may be set.  If it is not set
the memory allocated per string will default to 200 bytes.  If the
value is not large enough to hold the longest string data item in the
collection a runtime error occurs.  To avoid unnecessary memory
allocation, do not let the size be larger than needed.
The next example fetches an array of values from a table.  First,
insert these values:
```sql
INSERT INTO mytab (id, numcol) VALUES (99, 10);
INSERT INTO mytab (id, numcol) VALUES (99, 25);
INSERT INTO mytab (id, numcol) VALUES (99, 50);
COMMIT;
<div class="md-section-divider"></div>

With these values, the following node-oracledb code will print
[ 10, 25, 50 ].

connection.execute(
  "BEGIN mypkg.myoutproc(:id, :vals); END;",
  {
    id: 99,
    vals: { type: oracledb.NUMBER,
            dir:  oracledb.BIND_OUT,
            maxArraySize: 10          // allocate memory to hold 10 numbers
        }
  },
  function (err, result) {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds.vals);
  });
<div class="md-section-divider"></div>

If maxArraySize was reduced to 2, the script would fail with:

ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array

See Oracledb Constants and
execute(): Bind Parameters for more information
about binding.

See
plsqlarray.js
for a runnable example.