node-oracledb

9. SQL Execution

A SQL or PL/SQL statement may be executed using the Connection
execute() method. Either the callback style shown
below, or promises may be used.

After all database calls on the connection complete, the application
should use the connection.close() call to
release the connection.

Queries may optionally be streamed using the Connection
queryStream() method.

9.1 SELECT Statements

9.1.1 Fetching Rows

By default, query results are returned all at once in the rows
property of result parameter to the Connection
execute() callback. The number of rows returned is
restricted to maxRows:

    connection.execute(
      "SELECT department_id, department_name "
    + "FROM departments "
    + "WHERE department_id = :did",
      [180],
      { maxRows: 10 },  // a maximum of 10 rows will be returned.  Default limit is 100
      function(err, result)
      {
        if (err) { console.error(err.message); return; }
        console.log(result.rows);  // print all returned rows
      });

9.1.2 Result Set Handling

When the number of query rows is relatively big, or can't be
predicted, it is recommended to use a ResultSet
with callbacks, as described in this section, or via the ResultSet
stream wrapper, as described later. This
prevents query results being unexpectedly truncated by the
maxRows limit and removes the need to oversize
maxRows to avoid such truncation. Otherwise, for queries that
return a known small number of rows, non-result set queries may have
less overhead.

A result set is created when the execute() option property
resultSet is true. Result set rows can be
fetched using getRow() or getRows() on the
execute() callback function's result.resultSet parameter property.

For result sets the maxRows limit is ignored. All
rows can be fetched.

When all rows have been fetched, or the application does not want to
continue getting more rows, then the result set should be freed using
close().

REF CURSORS returned from a PL/SQL block via an oracledb.CURSOR OUT
bind are also available as a ResultSet. See
REF CURSOR Bind Parameters.

The format of each row will be an array or object, depending on the
value of outFormat.

See resultset1.js,
resultset2.js
and refcursor.js
for full examples.

To fetch one row at a time use getRow() :

connection.execute(
  "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
  [], // no bind variables
  { resultSet: true }, // return a result set.  Default is false
  function(err, result)
  {
    if (err) { . . . }
    fetchOneRowFromRS(connection, result.resultSet);
  });
});
function fetchOneRowFromRS(connection, resultSet)
{
  resultSet.getRow( // get one row
    function (err, row)
    {
      if (err) {
         . . .           // close the result set and release the connection
      } else if (!row) { // no rows, or no more rows
        . . .            // close the result set and release the connection
      } else {
        console.log(row);
        fetchOneRowFromRS(connection, resultSet);  // get next row
      }
    });
}

It is generally more efficient to fetch multiple rows at a time using getRows():

var numRows = 10;  // number of rows to return from each call to getRows()
connection.execute(
  "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
  [], // no bind variables
  { resultSet: true }, // return a result set.  Default is false
  function(err, result)
  {
    if (err) { . . . }
    fetchRowsFromRS(connection, result.resultSet, 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
      }
    });
}

9.1.3 Streaming Query Results

Streaming query results allows data to be piped to other streams, for
example when dealing with HTTP responses.

Use connection.queryStream() to create a stream from
a top level query and listen for events. You can also call
connection.execute() and use
toQueryStream() to return a stream from the
returned ResultSet or OUT bind REF CURSOR
ResultSet.

With streaming, each row is returned as a data event. Query
metadata is available via a metadata event. The end event
indicates the end of the query results.

The connection must remain open until the stream is completely read.

The query stream implementation is a wrapper over the
ResultSet Class. In particular, calls to
getRows() are made internally to fetch each successive
subset of data, each row of which will generate a data event. The
number of rows fetched from the database by each getRows() call is
set to the value of oracledb.maxRows. This value
does not alter the number of rows returned by the stream since
getRows() will be called each time more rows are needed. However
the value can be used to tune performance, as also can the value of
oracledb.prefetchRows.

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. If you need to be
able to stop a query before retrieving all data, use a
ResultSet with callbacks. (Note: An
experimental querystream._close() method exists to terminate a
stream early. It is under evaluation, may changed or be removed, and
should not be used in production.)

An example of streaming query results is:

var stream = connection.queryStream('SELECT employees_name FROM employees');
stream.on('error', function (error) {
  // handle any error...
});
stream.on('data', function (data) {
  // handle data row...
});
stream.on('end', function () {
  // release connection...
});
stream.on('metadata', function (metadata) {
  // access metadata of query
});
// listen to any other standard stream events...

See
selectstream.js
for a runnable example using connection.queryStream().

The REF CURSOR Bind Parameters section shows using
toQueryStream() to return a stream for a REF CURSOR.

9.1.4 Query Output Formats

Query rows may be returned as an array of column values, or as
Javascript objects, depending on the values of
outFormat.

The default format for each row is an array of column values.
For example:

var oracledb = require('oracledb');
. . .
connection.execute(
  "SELECT department_id, department_name " +
    "FROM departments " +
    "WHERE manager_id < :id",
  [110],  // bind value for :id
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.rows);
  });

If run with Oracle's sample HR schema, the output is:

[ [ 60, 'IT' ], [ 90, 'Executive' ], [ 100, 'Finance' ] ]

Using this format is recommended for efficiency.

Alternatively, rows may be fetched as JavaScript objects. To do so,
specify the outFormat option to be OBJECT:

oracledb.outFormat = oracledb.OBJECT;

The value can also be set as an execute() option:

connection.execute(
  "SELECT department_id, department_name " +
    "FROM departments " +
    "WHERE manager_id < :id",
  [110],  // bind value for :id
  { outFormat: oracledb.OBJECT },
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.rows);
  });

The output is:

[ { DEPARTMENT_ID: 60, DEPARTMENT_NAME: 'IT' },
  { DEPARTMENT_ID: 90, DEPARTMENT_NAME: 'Executive' },
  { DEPARTMENT_ID: 100, DEPARTMENT_NAME: 'Finance' } ]

In the preceding example, each row is a JavaScript object that
specifies column names and their respective values. Note the property
names follow Oracle's standard name-casing rules. They will commonly
be uppercase, since most applications create tables using unquoted,
case-insensitive names.

9.1.5 Query Column Metadata

The column names of a query are returned in the execute() callback's
result.metaData attribute:

connection.execute(
  "SELECT department_id, department_name " +
    "FROM departments " +
    "WHERE manager_id < :id",
  [110],  // bind value for :id
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.metaData);  // show the metadata
  });

When using a ResultSet, metadata is also
available in result.resultSet.metaData.

The metadata is an array of objects, one per column. By default each
object has a name attribute:

[ { name: 'DEPARTMENT_ID' }, { name: 'DEPARTMENT_NAME' } ]

The names are in uppercase. This is the default casing behavior for
Oracle client programs when a database table is created with unquoted,
case-insensitive column names.

Extended Metadata

More metadata is included when the Oracledb
extendedMetaData or execute() option
extendedMetaData is true. For
example:

connection.execute(
  "SELECT department_id, department_name " +
    "FROM departments " +
    "WHERE manager_id < :id",
  [110],  // bind value for :id
  { extendedMetaData: true },
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.metaData);  // show the extended metadata
  });

The output is:

[ { name: 'DEPARTMENT_ID',
    fetchType: 2002,
    dbType: 2,
    precision: 4,
    scale: 0,
    nullable: false },
  { name: 'DEPARTMENT_NAME',
    fetchType: 2001,
    dbType: 1,
    byteSize: 30,
    nullable: false } ]

Description of the properties is given in the
result.metaData description.

9.1.6 Result Type Mapping

Oracle character, number and date columns can be selected. Datatypes
that are currently unsupported give a "datatype is not supported"
error.

The default query result type mappings for Oracle Database types to JavaScript types are:

• Variable and fixed length character columns are mapped to JavaScript strings.

• All numeric columns are mapped to JavaScript numbers.

• Date and timestamp columns are mapped to JavaScript dates.
  Note that JavaScript Date has millisecond precision.
  Therefore, timestamps having greater
  precision lose their sub-millisecond fractional part
  when fetched. Internally, TIMESTAMP and DATE
  columns are fetched as TIMESTAMP WITH LOCAL TIME ZONE using
  OCIDateTime.
  When binding a JavaScript Date value in an INSERT statement, the date is also inserted as TIMESTAMP WITH
LOCAL TIME ZONE using OCIDateTime.

Fetching as String

The global fetchAsString property can be
used to force all number or date columns queried by an application to
be fetched as strings instead of in native format. Allowing data to
be fetched as strings helps avoid situations where using JavaScript
types can lead to numeric precision loss, or where date conversion is
unwanted.

For example, to force all dates and numbers used by queries in an
application to be fetched as strings:

var oracledb = require('oracledb');
oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ];

Only number and date columns can be mapped to strings with fetchAsString.

The maximum length of a string created can be 200 bytes.

Individual queries can use the execute() option
fetchInfo to map individual number or date columns
to strings without affecting other columns or other queries. Any
global fetchAsString setting can be overridden to allow specific
columns to have data returned in native format:

var oracledb = require('oracledb');
oracledb.fetchAsString = [ oracledb.NUMBER ];  // any number queried will be returned as a string
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function(err, connection)
  {
    if (err) { console.error(err.message); return; }
    connection.execute(
      "SELECT last_name, hire_date, salary, commission_pct FROM employees WHERE employee_id = :id",
      [178],
      {
        fetchInfo :
        {
          "HIRE_DATE":      { type : oracledb.STRING },  // return the date as a string
          "COMMISSION_PCT": { type : oracledb.DEFAULT }  // override oracledb.fetchAsString and fetch as native type
        }
      },
      function(err, result)
      {
        if (err) { console.error(err.message); return; }
        console.log(result.rows);
      });
  });

The output is:

[ [ 'Grant', '24-MAY-07', '7000', 0.15 ] ]

The date and salary columns are returned as strings, but the
commission is a number. The date is mapped using the current session
date format, which was DD-MON-YY in this example. The default date
format can be set, for example, with the environment variable
NLS_DATE_FORMAT. Note this variable will only be read if NLS_LANG
is also set.

Without the mapping capabilities provided by fetchAsString and
fetchInfo the hire date would have been a JavaScript date in the
local time zone, and both numeric columns would have been
represented as numbers:

[ [ 'Grant', Thu May 24 2007 00:00:00 GMT+1000 (AEST), 7000, 0.15 ] ]

To map columns returned from REF CURSORS, use fetchAsString. The
fetchInfo settings do not apply.

Mapping Custom Types

Datatypes such as an Oracle Locator SDO_GEOMETRY, or your own custom
types, cannot be fetched directly in node-oracledb. Instead, utilize
techniques such as using an intermediary PL/SQL procedure to map the
type components to scalar values, or use a pipelined table.

For example, consider a CUSTOMERS table having a CUST_GEO_LOCATION
column of type SDO_GEOMETRY, as created in this example
schema:

CREATE TABLE customers (
  customer_id NUMBER,
  last_name VARCHAR2(30),
  cust_geo_location SDO_GEOMETRY);
INSERT INTO customers VALUES
  (1001, 'Nichols', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL));
COMMIT;

Instead of attempting to get CUST_GEO_LOCATION by directly calling a
PL/SQL procedure that returns an SDO_GEOMETRY parameter, you could
instead get the scalar coordinates by using an intermediary PL/SQL
block that decomposes the geometry:

    . . .
    var sql =
      "BEGIN " +
      "  SELECT t.x, t.y" +
      "  INTO :x, :y" +
      "  FROM customers, TABLE(sdo_util.getvertices(customers.cust_geo_location)) t" +
      "  WHERE customer_id = :id;" +
      "END; ";
    var bindvars = {
      id: 1001,
      x: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },
      y: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT }
    }
    connection.execute(
      sql,
      bindvars,
      function (err, result) {
        if (err) { console.error(err.message); return; }
        console.log(result.outBinds);
      });

The output is:

{ x: -71.48922999999999, y: 42.72347 }

Note the JavaScript precision difference. In this particular example,
you may want to bind using type: oracledb.STRING. Output would be:

{ x: '-71.48923', y: '42.72347' }

9.1.7 Row Prefetching

Prefetching is a query tuning feature allowing resource usage to be
optimized. It allows multiple rows to be returned in each network
trip from Oracle Database to node-oracledb when a
ResultSet is used for query or REF CURSOR data.
The prefetch size does not affect when, or how many, rows are returned
by node-oracledb to the application. The buffering of rows is handled
by Oracle's underlying client libraries.

By default prefetchRows is 100 for
ResultSet fetches. The application can choose a
different default prefetch size or change it for each query, as
determined by user benchmarking.

The default prefetch size was heuristically chosen to give decent
performance for developers who do not read documentation. Skilled
developers should benchmark their applications and adjust the prefetch
value of each query for optimum performance, memory use, and network
utilization.

For queries returning small sets of rows, reduce the default prefetch
to avoid unnecessary memory allocation and initialization. For
queries that return only a single row the minimum recommended prefetch
value is 2. This value lets node-oracledb fetch one row and check for
end-of-fetch at the same time.

The value of prefetchRows size is ignored when not using a
ResultSet.

Prefetching from REF CURSORS requires Oracle Database 11gR2 or
greater.

Prefetching can be disabled by setting prefetchRows to 0.

The prefetch size can be changed for the whole application:

var oracledb = require('oracledb');
oracledb.prefetchRows = 2;

Alternatively the prefetch size can be changed for individual queries
in the execute() options parameter:

connection.execute(
  "SELECT last_name FROM employees",
  [],
  {resultSet: true, prefetchRows: 2},
  function(err, result)
  {
     . . .
  });