node-oracledb

Oracledb Class

The Oracledb object is the factory class for Pool and Connection objects.

The Oracledb object is instantiated by loading node-oracledb:

var oracledb = require("oracledb");

Internally, the add-on creates the Oracledb object as a singleton.
Reloading it in the same Node.js process creates a new pointer to the
same object.

3.1 Oracledb Constants

These constants are defined in the oracledb module. Usage is
described later in this document.

The numeric values for the constants are shown to aid debugging. They
may change in future, so use the constant names in applications.

3.1.1 Query outFormat Constants

Constants for the query result outFormat option:

Oracledb.ARRAY                  // (4001) Fetch each row as array of column values
Oracledb.OBJECT                 // (4002) Fetch each row as an object

3.1.2 Node-oracledb Type Constants

Constants for execute() bind parameter and
Lob type properties, for
fetchAsString and
fetchInfo, and for
extended metadata.

Not all constants can be used in all places.

Oracledb.BLOB                   // (2007) Bind a BLOB to a Node.js Stream
Oracledb.BUFFER                 // (2005) Bind a RAW to a Node.js Buffer
Oracledb.CLOB                   // (2006) Bind a CLOB to a Node.js Stream
Oracledb.CURSOR                 // (2004) Bind a REF CURSOR to a node-oracledb ResultSet class
Oracledb.DATE                   // (2003) Bind as JavaScript date type.  Can also be used for fetchAsString and fetchInfo
Oracledb.DEFAULT                // (0) Used with fetchInfo to reset the fetch type to the database type
Oracledb.NUMBER                 // (2002) Bind as JavaScript number type.  Can also be used for fetchAsString and fetchInfo
Oracledb.STRING                 // (2001) Bind as JavaScript string type

3.1.3 Oracle Database Type Constants

These types are shown in extended metadata
for queries and REF CURSORS. They indicate the Oracle database type.

Oracledb.DB_TYPE_BINARY_DOUBLE  // (101) BINARY_DOUBLE
Oracledb.DB_TYPE_BINARY_FLOAT   // (100) BINARY_FLOAT
Oracledb.DB_TYPE_BLOB           // (113) BLOB
Oracledb.DB_TYPE_CHAR           // (96) CHAR
Oracledb.DB_TYPE_CLOB           // (112) CLOB
Oracledb.DB_TYPE_DATE           // (12) DATE
Oracledb.DB_TYPE_NUMBER         // (2) NUMBER or FLOAT
Oracledb.DB_TYPE_RAW            // (23) RAW
Oracledb.DB_TYPE_ROWID          // (104) ROWID
Oracledb.DB_TYPE_TIMESTAMP      // (187) TIMESTAMP
Oracledb.DB_TYPE_TIMESTAMP_LTZ  // (232) TIMESTAMP WITH LOCAL TIME ZONE
Oracledb.DB_TYPE_TIMESTAMP_TZ   // (188) TIMESTAMP WITH TIME ZONE
Oracledb.DB_TYPE_VARCHAR        // (1) VARCHAR2

3.1.4 Execute Bind Direction Constants

Constants for execute() bind parameter dir
properties.

These specify whether data values bound to SQL or PL/SQL bind
parameters are passed into, or out from, the database:

Oracledb.BIND_IN                // (3001) Direction for IN binds
Oracledb.BIND_INOUT             // (3002) Direction for IN OUT binds
Oracledb.BIND_OUT               // (3003) Direction for OUT binds

3.2 Oracledb Properties

The properties of the Oracledb object are used for setting up
configuration parameters for deployment.

If required, these properties can be overridden for the Pool or
Connection objects.

These properties may be read or modified. If a property is modified,
only subsequent invocations of the createPool() or getConnection()
methods will be affected. Objects that exist before a property is
modified are not altered.

Invalid values, or combinations of values, for pool configuration
properties can result in the error ORA-24413: Invalid number of
sessions specified.

Each of the configuration properties is described below.

3.2.1 autoCommit

Boolean autoCommit

If this property is true, then the transaction in the current
connection is automatically committed at the end of statement
execution.

The default value is false.

This property may be overridden in an execute() call.

Note prior to node-oracledb 0.5 this property was called
isAutoCommit.

Example

var oracledb = require('oracledb');
oracledb.autoCommit = false;

3.2.2 connectionClass

String connectionClass

The user-chosen Connection class value defines a logical name for connections.
Most single purpose applications should set connectionClass when
using a connection pool or DRCP.

When a pooled session has a connection class, Oracle ensures that the
session is not shared outside of that connection class.

The connection class value is similarly used by
Database Resident Connection Pooling (DRCP) to allow or
disallow sharing of sessions.

For example, where two different kinds of users share one pool, you
might set connectionClass to 'HRPOOL' for connections that access a
Human Resources system, and it might be set to 'OEPOOL' for users of an
Order Entry system. Users will only be given sessions of the
appropriate class, allowing maximal reuse of resources in each case,
and preventing any session information leaking between the two systems.

Example

var oracledb = require('oracledb');
oracledb.connectionClass = 'HRPOOL';

3.2.3 extendedMetaData

Boolean extendedMetaData

Determines whether additional metadata is available for queries and
for REF CURSORs returned from PL/SQL blocks.

The default value for extendedMetaData is false. With this value,
the result.metaData
result.resultSet.metaData objects only include column
names.

If extendedMetaData is true then metaData will contain
additional attributes. These are listed in
Result Object Properties.

This property may be overridden in an execute() call.

3.2.4 externalAuth

Boolean externalAuth

If this property is true then connections are established using
external authentication. See External Authentication for
more information.

The default value is false.

The user and password properties for connecting or creating a pool
should not be set when externalAuth is true.

This property can be overridden in the Oracledb
getConnection() or createPool()
calls.

Note prior to node-oracledb 0.5 this property was called
isExternalAuth.

Example

var oracledb = require('oracledb');
oracledb.externalAuth = false;

3.2.5 fetchAsString

Array fetchAsString

An array of node-oracledb types. When any column having the specified
type is queried with execute(), the column data is
returned as a string instead of the native representation. For column
types not specified in fetchAsString, native types will be returned.

By default all columns are returned as native types.

This property helps avoid situations where using JavaScript types can
lead to numeric precision loss, or where date conversion is unwanted.

The valid types that can be mapped to strings are
DATE and
NUMBER. Columns of type ROWID and
TIMESTAMP WITH TIME ZONE that cannot natively be fetched can also be
mapped and fetched as strings.

The maximum length of a string created by this mapping is 200 bytes.

Individual query columns in an execute() call can
override the fetchAsString global setting by using
fetchInfo.

The conversion to string is handled by Oracle client libraries and is
often referred to as defining the fetch type.

Example

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

3.2.6 lobPrefetchSize

Number lobPrefetchSize

This attribute is temporarily disabled. Setting it has no effect.

Node-oracledb internally uses Oracle LOB Locators to manipulate long
object (LOB) data. LOB Prefetching allows LOB data to be returned
early to node-oracledb when these locators are first returned.
This is similar to the way row prefetching allows
for efficient use of resources and round-trips between node-oracledb
and the database.

Prefetching of LOBs is mostly useful for small LOBs.

The default size is 16384.

Example

var oracledb = require('oracledb');
oracledb.lobPrefetchSize = 16384;

3.2.7 maxRows

Number maxRows

The maximum number of rows that are fetched by the execute() call of the Connection
object when not using a ResultSet. Rows beyond
this limit are not fetched from the database.

The default value is 100.

This property may be overridden in an execute() call.

This property is also used by queryStream() as an
internal buffer size tuning parameter.

To improve database efficiency, SQL queries should use a row
limiting clause like OFFSET /
FETCH
or equivalent. The maxRows property can be used to stop badly coded
queries from returning unexpectedly large numbers of rows.

Adjust maxRows as required by each application or query. Values
that are larger than required can result in sub-optimal memory usage.

maxRows is ignored when fetching rows with a
ResultSet.

When the number of query rows is relatively big, or can't be
predicted, it is recommended to use a ResultSet.
This prevents query results being unexpectedly truncated by the
maxRows limit and removes the need to oversize maxRows to avoid
such truncation.

Example

var oracledb = require('oracledb');
oracledb.maxRows = 100;

3.2.8 oracleClientVersion

readonly Number oracleClientVersion

This readonly property gives a numeric representation of the Oracle client library version.
For version a.b.c.d.e, this property gives the number: (100000000 * a) + (1000000 * b) + (10000 * c) + (100 * d) + e

Example

var oracledb = require('oracledb');
console.log("Oracle client library version number is " + oracledb.oracleClientVersion);

3.2.9 outFormat

Number outFormat

The format of rows fetched when using the execute()
call. This can be either of the Oracledb
constants ARRAY or OBJECT. The default value
is ARRAY which is more efficient.

If specified as ARRAY, each row is fetched as an array of column
values.

If specified as OBJECT, each row is fetched as a JavaScript object.
The object has a property for each column name, with the property
value set to the respective column value. The property name follows
Oracle's standard name-casing rules. It will commonly be uppercase,
since most applications create tables using unquoted, case-insensitive
names.

This property may be overridden in an execute() call.

Example

var oracledb = require('oracledb');
oracledb.outFormat = oracledb.ARRAY;

3.2.10 poolIncrement

Number poolIncrement

The number of connections that are opened whenever a connection
request exceeds the number of currently open connections.

The default value is 1.

This property may be overridden when creating a connection pool.

Example

var oracledb = require('oracledb');
oracledb.poolIncrement = 1;

3.2.11 poolMax

Number poolMax

The maximum number of connections to which a connection pool can grow.

The default value is 4.

This property may be overridden when creating a connection pool.

If you increase this value, you may want to increase the number of
threads available to node-oracledb. See
Connections and Number of Threads.

Example

var oracledb = require('oracledb');
oracledb.poolMax = 4;

3.2.12 poolMin

Number poolMin

The minimum number of connections a connection pool maintains, even
when there is no activity to the target database.

The default value is 0.

This property may be overridden when creating a connection pool.

Example

var oracledb = require('oracledb');
oracledb.poolMin = 0;

3.2.13 poolTimeout

Number poolTimeout

The number of seconds after which idle connections (unused in the
pool) are terminated. Idle connections are terminated only when the
pool is accessed. If the poolTimeout is set to 0, then idle
connections are never terminated.

The default value is 60.

This property may be overridden when creating a connection pool.

Example

var oracledb = require('oracledb');
oracledb.poolTimeout = 60;

3.2.14 prefetchRows

Number prefetchRows

The number of additional rows the underlying Oracle client library
fetches whenever node-oracledb requests query data from the database.

Prefetching is a tuning option to maximize data transfer efficiency and
minimize round-trips to the database. The prefetch size does not
affect when, or how many, rows are returned by node-oracledb to the
application. The cache management is transparently handled by the
Oracle client libraries.

prefetchRows is ignored unless a ResultSet is used.

The default value is 100.

This property may be overridden in an execute() call.

See Row Prefetching for examples.

Example

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

3.2.15 Promise

Promise Promise

Node-oracledb supports Promises on all methods. The standard Promise
library is used in Node 0.12 and greater. Promise support is not
enabled by default in Node 0.10.

See Promises in node-oracledb for a discussion of
using Promises.

This property can be set to override or disable the Promise
implementation.

Example

var mylib = require('myfavpromiseimplementation');
oracledb.Promise = mylib;

Promises can be completely disabled by setting

oracledb.Promise = null;

3.2.16 queueRequests

Boolean queueRequests

If this property is true and the number of connections "checked out"
from the pool has reached the number specified by
poolMax, then new requests for connections are
queued until in-use connections are released.

If this property is false and a request for a connection is made
from a pool where the number of "checked out" connections has reached
poolMax, then an ORA-24418 error indicating that further sessions
cannot be opened will be returned.

The default value is true.

This property may be overridden when creating a connection pool.

Example

var oracledb = require('oracledb');
oracledb.queueRequests = false;

3.2.17 queueTimeout

Number queueTimeout

The number of milliseconds after which connection requests waiting in
the connection request queue are terminated. If queueTimeout is
0, then queued connection requests are never terminated.

The default value is 60000.

This property may be overridden when creating a connection pool.

Example

var oracledb = require('oracledb');
oracledb.queueTimeout = 3000; // 3 seconds

3.2.18 stmtCacheSize

Number stmtCacheSize

The number of statements that are cached in the statement cache of
each connection.

The default value is 30.

This property may be overridden for specific Pool or Connection
objects.

In general, set the statement cache to the size of the working set of
statements being executed by the application. Statement caching can
be disabled by setting the size to 0.

See Statement Caching for examples.

Example

var oracledb = require('oracledb');
oracledb.stmtCacheSize = 30;

3.2.19 version

readonly Number version

This readonly property gives a numeric representation of the node-oracledb version.
For version x.y.z, this property gives the number: (10000 * x) + (100 * y) + z

Example

var oracledb = require('oracledb');
console.log("Driver version number is " + oracledb.version);

3.3 Oracledb Methods

3.3.1 createPool()

Prototype

Callback:

createPool(Object poolAttrs, function(Error error, Pool pool){});

Promise:

promise = createPool(Object poolAttrs);

Description

This method creates a pool of connections with the specified username,
password and connection string.

Internally, createPool() creates an OCI Session
Pool
for each Pool object.

The default properties may be overridden by specifying new properties
in the poolAttrs parameter.

It is possible to add pools to the pool cache when calling createPool().
See Connection Pool Cache for more details.

A pool should be terminated with the pool.close()
call, but only after all connections have been released.

Parameters

Object poolAttrs

The poolAttrs parameter provides connection credentials and
pool-specific configuration properties, such as the maximum or minimum
number of connections for the pool, or stmtCacheSize for the connections.
The properties provided in the poolAttrs parameter override the default
pooling properties in effect in the Oracledb object.

Note that the poolAttrs parameter may have configuration
properties that are not used by the createPool() method. These are
ignored.

The properties of poolAttrs are described below.

String user

The database user name. Can be a simple user name or a proxy of the form alison[fred]. See the
Client Access Through Proxy
section in the OCI manual for more details about proxy
authentication.

String password

The password of the database user. A password is also necessary if a
proxy user is specified.

String connectString

The Oracle database instance to connect to. The string can be an Easy
Connect string, or a Net Service Name from a tnsnames.ora file, or the
name of a local Oracle database instance. See
Connection Strings for examples.

Boolean externalAuth

Indicate whether to connections should be established using
External Authentication.

This optional property overrides the Oracledb
externalAuth property.

The user and password properties should not be set when
externalAuth is true.

Note prior to node-oracledb 0.5 this property was called
isExternalAuth.

Number stmtCacheSize

The number of statements to be cached in the
statement cache of each connection.

This optional property overrides the Oracledb
stmtCacheSize property.

String poolAlias

The poolAlias is an optional property that is used to explicitly add pools to the
connection pool cache. If a pool alias is provided, then the new pool will be added
to the connection pool cache and the poolAlias value can then be used with methods
that utilize the connection pool cache, such as oracledb.getPool() and
oracledb.getConnection().

See Connection Pool Cache for details and examples.

Number poolMax

The maximum number of connections to which a connection pool can grow.

This optional property overrides the Oracledb
poolMax property.

Number poolMin

The minimum number of connections a connection pool maintains, even
when there is no activity to the target database.

This optional property overrides the Oracledb
poolMin property.

Number poolIncrement

The number of connections that are opened whenever a connection
request exceeds the number of currently open connections.

This optional property overrides the Oracledb
poolIncrement property.

Number poolTimeout

The number of seconds after which idle connections (unused in the
pool) may be terminated. Idle connections are terminated only when
the pool is accessed. If poolTimeout is set to 0, then idle
connections are never terminated.

This optional property overrides the Oracledb
poolTimeout property.

Boolean queueRequests

Indicate whether pool.getConnection() calls
should be queued when all available connections are in currently use.

This optional property overrides the Oracledb
queueRequests property.

Number queueTimeout

The number of milliseconds after which connection requests waiting in the
connection request queue are terminated. If queueTimeout is
set to 0, then queued connection requests are never terminated.

This optional property overrides the Oracledb
queueTimeout property.

function(Error error, Pool pool)

The parameters of the callback function are:

3.3.2 getConnection()

Prototype

Callback:

getConnection([String poolAlias | Object connAttrs], function(Error error, Connection conn){});

Promise:

promise = getConnection([String poolAlias | Object connAttrs]);

Description

Obtains a connection from a pool in the connection pool cache or creates a new,
non-pooled connection.

For situations where connections are used infrequently, creating a new connection
may be more efficient than creating and managing a connection pool. However, in
most cases, Oracle recommends getting connections from a connection pool.

The following table shows the various signatures that can be used when invoking
getConnection and describes how the function will behave as a result.

See Connection Handling for more information on
connections.

Parameters

String poolAlias

The poolAlias parameter is used to specify which pool in the connection pool
cache to use to obtain the connection.

Object connAttrs

The connAttrs parameter provides connection credentials and
connection-specific configuration properties, such as stmtCacheSize.

Note that the connAttrs object may have configuration
properties that are not used by the getConnection() method. These
are ignored.

The properties of the connAttrs object are described below.

String user

The database user name. Can be a simple user name or a proxy of the form alison[fred]. See the
Client Access Through Proxy
section in the OCI manual for more details about proxy
authentication.

String password

The password of the database user. A password is also necessary if a
proxy user is specified.

String connectString

The Oracle database instance to connect to. The string can be an Easy Connect string, or a
Net Service Name from a tnsnames.ora file, or the name of a local
Oracle database instance. See
Connection Strings for examples.

Boolean externalAuth

If this optional property is true then the connection will be
established using External Authentication.

This optional property overrides the Oracledb
externalAuth property.

The user and password properties should not be set when
externalAuth is true.

Note prior to node-oracledb 0.5 this property was called
isExternalAuth.

Number stmtCacheSize

The number of statements to be cached in the
statement cache of each connection. This optional
property may be used to override the
stmtCacheSize property of the Oracledb
object.

function(Error error, Connection conn)

The parameters of the callback function are:

3.3.3 getPool()

Prototype

getPool([String poolAlias]);

Description

Retrieves a pool from the connection pool cache. Note that this is a synchronous
method.

Parameters

String poolAlias

The pool alias of the pool to retrieve from the connection pool cache. The default
value is 'default' which will retrieve the default pool.