1. API: Oracledb Class

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

The Oracledb object is instantiated by loading node-oracledb:

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

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

1.1.1. Query outFormat Constants

Constants for the query result outFormat option:

Table 1.1 Query outFormat Constants

Constant Name

Value

Description

oracledb.OUT_FORMAT_ARRAY

4001

Fetch each row as array of column values.

Added in version 4.0.

oracledb.OUT_FORMAT_OBJECT

4002

Fetch each row as an object.

Added in version 4.0.

The previous constants oracledb.ARRAY and oracledb.OBJECT are deprecated but still usable.

1.1.2. Oracle Database Type Objects

These database type objects indicate the Oracle Database type in metaData, DbObject types, and in the lob type property. Some database type objects can also be used for:

Note that the Oracle Database Type constants were changed to database type objects in node-oracledb 6.0. When comparing fetch types, ensure that you are using the database type object name instead of the database type number. For example, use result.metadata[0].fetchType == oracledb.DB_TYPE_VARCHAR instead of result.metadata[0].fetchType == 2001.

Table 1.2 Oracle Database Type Objects

DbType Object

Value

Database Data Type

oracledb.DB_TYPE_BFILE

2020

BFILE

oracledb.DB_TYPE_BINARY_DOUBLE

2008

BINARY_DOUBLE

oracledb.DB_TYPE_BINARY_FLOAT

2007

BINARY_FLOAT

oracledb.DB_TYPE_BINARY_INTEGER

2009

BINARY_INTEGER, PLS_INTEGER, SMALLINT, etc.

oracledb.DB_TYPE_BLOB

2019

BLOB

oracledb.DB_TYPE_BOOLEAN

2022

PL/SQL BOOLEAN

oracledb.DB_TYPE_CHAR

2003

CHAR

oracledb.DB_TYPE_CLOB

2017

CLOB

oracledb.DB_TYPE_CURSOR

2021

SYS_REFCURSOR, Nested Cursors

oracledb.DB_TYPE_DATE

2011

DATE

oracledb.DB_TYPE_INTERVAL_DS

2015

INTERVAL DAY TO SECOND

oracledb.DB_TYPE_INTERVAL_YM

2016

INTERVAL YEAR TO MONTH

oracledb.DB_TYPE_JSON

2027

JSON

Added in version 5.1.

oracledb.DB_TYPE_LONG

2024

LONG

oracledb.DB_TYPE_LONG_NVARCHAR

2031

LONG

oracledb.DB_TYPE_LONG_RAW

2025

LONG RAW

oracledb.DB_TYPE_NCHAR

2004

NCHAR

oracledb.DB_TYPE_NCLOB

2018

NCLOB

oracledb.DB_TYPE_NUMBER

2010

NUMBER or FLOAT

oracledb.DB_TYPE_NVARCHAR

2002

NVARCHAR

oracledb.DB_TYPE_OBJECT

2023

OBJECT

oracledb.DB_TYPE_RAW

2006

RAW

oracledb.DB_TYPE_ROWID

2005

ROWID

oracledb.DB_TYPE_TIMESTAMP

2012

TIMESTAMP

oracledb.DB_TYPE_TIMESTAMP_LTZ

2014

TIMESTAMP WITH LOCAL TIME ZONE

oracledb.DB_TYPE_TIMESTAMP_TZ

2013

TIMESTAMP WITH TIME ZONE

oracledb.DB_TYPE_VARCHAR

2001

VARCHAR2

oracledb.DB_TYPE_XMLTYPE

2032

XMLTYPE

oracledb.DB_TYPE_VECTOR

2033

VECTOR

Added in version 6.5.

Changed in version 4.0: The values of the constants were changed. This change is not applicable to the constants introduced after node-oracledb 4.0.

1.1.3. Node-oracledb Type Constants

From node-oracledb 4.0, these constant values changed and became aliases for common Oracle Database Type Constants.

Table 1.3 Node-oracledb Type Constants

Constant Name

Value

DbType Object Equivalent

Notes

oracledb.BLOB

2019

oracledb.DB_TYPE_BLOB

oracledb.BUFFER

2006

oracledb.DB_TYPE_RAW

oracledb.CLOB

2017

oracledb.DB_TYPE_CLOB

oracledb.CURSOR

2021

oracledb.DB_TYPE_CURSOR

oracledb.DATE

2014

oracledb.DB_TYPE_TIMESTAMP_LTZ

oracledb.DEFAULT

0

NA

Used with fetchInfo to reset the fetch type to the database type.

oracledb.NUMBER

2010

oracledb.DB_TYPE_NUMBER

oracledb.NCLOB

2018

oracledb.DB_TYPE_NCLOB

Added in version 4.2.

oracledb.STRING

2001

oracledb.DB_TYPE_VARCHAR

1.1.4. Execute Bind Direction Constants

Constants for the dir property of execute() bindParams, connection.queryStream() and executeMany() bindDefs.

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

Table 1.4 Execute Bind Direction Constants

Constant Name

Value

Description

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.

1.1.5. Privileged Connection Constants

Constants for getConnection() privilege properties.

These specify what privilege should be used by the connection that is being established.

Table 1.5 Privileged Connection Constants

Constant Name

Value

Description

oracledb.SYSASM

32768

SYSASM privileges

oracledb.SYSBACKUP

131072

SYSBACKUP privileges

oracledb.SYSDBA

2

SYSDBA privileges

oracledb.SYSDG

262144

SYSDG privileges

oracledb.SYSKM

524288

SYSKM privileges

oracledb.SYSOPER

4

SYSOPER privileges

oracledb.SYSPRELIM

8

Preliminary privilege required when starting up a database with connection.startup().

Added in version 5.0.

oracledb.SYSRAC

1048576

SYSRAC privileges

1.1.6. SQL Statement Type Constants

Constants for connection.getStatementInfo() properties.

Table 1.6 SQL Statement Type Constants

Constant Name

Value

Description

oracledb.STMT_TYPE_ALTER

7

ALTER

oracledb.STMT_TYPE_BEGIN

8

BEGIN

oracledb.STMT_TYPE_CALL

10

CALL

oracledb.STMT_TYPE_COMMIT

21

COMMIT

oracledb.STMT_TYPE_CREATE

5

CREATE

oracledb.STMT_TYPE_DECLARE

9

DECLARE

oracledb.STMT_TYPE_DELETE

3

DELETE

oracledb.STMT_TYPE_DROP

6

DROP

oracledb.STMT_TYPE_EXPLAIN_PLAN

15

EXPLAIN_PLAN

oracledb.STMT_TYPE_INSERT

4

INSERT

oracledb.STMT_TYPE_MERGE

16

MERGE

oracledb.STMT_TYPE_ROLLBACK

17

ROLLBACK

oracledb.STMT_TYPE_SELECT

1

SELECT

oracledb.STMT_TYPE_UNKNOWN

0

UNKNOWN

oracledb.STMT_TYPE_UPDATE

2

UPDATE

1.1.7. Subscription Constants

Constants for the Continuous Query Notification (CQN) message.type.

Table 1.7 Subscription Constants for the CQN message.type Property

Constant Name

Value

Description

oracledb.SUBSCR_EVENT_TYPE_AQ

100

Advanced Queuing notifications are being used.

oracledb.SUBSCR_EVENT_TYPE_DEREG

5

A subscription has been closed or the timeout value has been reached.

oracledb.SUBSCR_EVENT_TYPE_OBJ_CHANGE

6

Object-level notifications are being used (Database Change Notification).

oracledb.SUBSCR_EVENT_TYPE_QUERY_CHANGE

7

Query-level notifications are being used (Continuous Query Notification).

oracledb.SUBSCR_EVENT_TYPE_SHUTDOWN

2

The database is being shut down.

oracledb.SUBSCR_EVENT_TYPE_SHUTDOWN_ANY

3

An instance of Oracle Real Application Clusters (RAC) is being shut down.

oracledb.SUBSCR_EVENT_TYPE_STARTUP

1

The database is being started up.

Constant for the CQN groupingClass.

Table 1.8 Subscription Constant for the CQN groupingClass Property

Constant Name

Value

Description

oracledb.SUBSCR_GROUPING_CLASS_TIME

1

Group notifications by time into a single notification

Constants for the CQN groupingType.

Table 1.9 Subscription Constants for the CQN groupingType Property

Constant Name

Value

Description

oracledb.SUBSCR_GROUPING_TYPE_LAST

2

The last notification in the group is sent.

oracledb.SUBSCR_GROUPING_TYPE_SUMMARY

1

A summary of the grouped notifications is sent.

Constants for the CQN qos Quality of Service.

Table 1.10 Subscription Constants for the CQN qos Property

Constant Name

Value

Description

oracledb.SUBSCR_QOS_BEST_EFFORT

16

When best effort filtering for query result set changes is acceptable. False positive notifications may be received. This behavior may be suitable for caching applications.

oracledb.SUBSCR_QOS_DEREG_NFY

2

The subscription will be automatically unregistered as soon as the first notification is received.

oracledb.SUBSCR_QOS_QUERY

8

CQN will be used instead of Database Change Notification. This means that notifications are only sent if the result set of the registered query changes. By default no false positive notifications are generated. Use oracledb.SUBSCR_QOS_BEST_EFFORT if this is not needed.

oracledb.SUBSCR_QOS_RELIABLE

1

Notifications are not lost in the event of database failure.

oracledb.SUBSCR_QOS_ROWIDS

4

Notifications include the ROWIDs of the rows that were affected.

Constants for the CQN namespace.

Table 1.11 Subscription Constants for the CQN namespace Property

Constant Name

Value

Description

oracledb.SUBSCR_NAMESPACE_AQ

1

For Advanced Queuing notifications.

oracledb.SUBSCR_NAMESPACE_DBCHANGE

2

For Continuous Query Notifications.

1.1.8. Advanced Queuing Constants

Refer to Advanced Queuing documentation for more details about attributes.

Constants for AqDeqOptions Class mode.

Table 1.12 Constants for the AqDeqOptions Class mode Property

Constant Name

Value

Description

oracledb.AQ_DEQ_MODE_BROWSE

1

Read a message without acquiring a lock.

oracledb.AQ_DEQ_MODE_LOCKED

2

Read and obtain write lock on message.

oracledb.AQ_DEQ_MODE_REMOVE

3

Read the message and delete it.

oracledb.AQ_DEQ_MODE_REMOVE_NO_DATA

4

Delete message without returning payload.

Constants for AqDeqOptions Class navigation.

Table 1.13 Constants for the AqDeqOptions Class navigation Property

Constant Name

Value

Description

oracledb.AQ_DEQ_NAV_FIRST_MSG

1

Get the message at the head of queue.

oracledb.AQ_DEQ_NAV_NEXT_TRANSACTION

2

Get the first message of next transaction group.

oracledb.AQ_DEQ_NAV_NEXT_MSG

3

Get the next message in the queue.

Constants for AqDeqOptions Class wait.

Table 1.14 Constants for the AqDeqOptions Class wait Property

Constant Name

Value

Description

oracledb.AQ_DEQ_NO_WAIT

0

Do not wait if no message is available.

oracledb.AQ_DEQ_WAIT_FOREVER

4294967295

Wait forever if no message is available.

Constants for AqEnqOptions Class deliveryMode.

Table 1.15 Constants for the AqDeqOptions Class deliveryMode Property

Constant Name

Value

Description

oracledb.AQ_MSG_DELIV_MODE_PERSISTENT

1

Messages are persistent.

oracledb.AQ_MSG_DELIV_MODE_BUFFERED

2

Messages are buffered.

oracledb.AQ_MSG_DELIV_MODE_PERSISTENT_OR_BUFFERED

3

Messages are either persistent or buffered.

Constants for AqMessage Class state.

Table 1.16 Constants for the AqMessage Class state Property

Constant Name

Value

Description

oracledb.AQ_MSG_STATE_READY

0

Consumers can dequeue messages that are in the READY state.

oracledb.AQ_MSG_STATE_WAITING

1

Message is hidden for a given retry delay interval.

oracledb.AQ_MSG_STATE_PROCESSED

2

All intended consumers have successfully dequeued the message.

oracledb.AQ_MSG_STATE_EXPIRED

3

One or more consumers did not dequeue the message before the expiration time.

Constants for AqEnqOptions Class and AqDeqOptions Class visibility.

Table 1.17 Constants for the AqEnqOptions Class and AqDeqOptions Class visibility Property

Constant Name

Value

Description

oracledb.AQ_VISIBILITY_IMMEDIATE

1

The message is not part of the current transaction. It constitutes a transaction on its own.

oracledb.AQ_VISIBILITY_ON_COMMIT

2

The message is part of the current transaction.

1.1.9. Continuous Query Notification (CQN) Constants

Constants for Continuous Query Notification (CQN) are integer mask values for the following properties of the options parameter in the connection.subscribe() method:

Table 1.18 Constants for the connection.subscribe() option operations and notification message operation Properties.

Constant Name

Value

Description

oracledb.CQN_OPCODE_ALL_OPS

0

Default. Used to request notification of all operations.

oracledb.CQN_OPCODE_ALL_ROWS

1

Indicates that row information is not available. This occurs if qos quality of service flags do not specify the desire for ROWIDs, or if grouping has taken place and summary notifications are being sent.

oracledb.CQN_OPCODE_ALTER

16

Set if the table was altered in the notifying transaction.

oracledb.CQN_OPCODE_DELETE

8

Set if the notifying transaction included deletes on the table.

oracledb.CQN_OPCODE_DROP

32

Set if the table was dropped in the notifying transaction.

oracledb.CQN_OPCODE_INSERT

2

Set if the notifying transaction included inserts on the table.

oracledb.CQN_OPCODE_UPDATE

4

Set if the notifying transaction included updates on the table.

1.1.10. Pool Status Constants

Constants for the connection pool.status read-only attribute.

Table 1.19 Constants for the connection pool.status Attribute

Constant Name

Value

Description

oracledb.POOL_STATUS_CLOSED

6002

The connection pool has been closed.

oracledb.POOL_STATUS_DRAINING

6001

The connection pool is being drained of in-use connections and will be force closed soon.

oracledb.POOL_STATUS_OPEN

6000

The connection pool is open.

oracledb.POOL_STATUS_RECONFIGURING

6003

A pool.reconfigure() call is processing.

1.1.11. Simple Oracle Document Access (SODA) Constants

Table 1.20 SODA Constant

Constant Name

Value

Description

oracledb.SODA_COLL_MAP_MODE

5001

Indicate sodaDatabase.createCollection() should use an externally created table to store the collection.

1.1.12. Database Shutdown Constants

Constants for shutting down the Oracle Database with oracledb.shutdown() and connection.shutdown().

Added in version 5.0.

Table 1.21 Database Shutdown Constants

Constant Name

Value

Description

oracledb.SHUTDOWN_MODE_ABORT

4

All uncommitted transactions are terminated and not rolled back. This is the fastest way to shut down the database, but the next database start up may require instance recovery.

oracledb.SHUTDOWN_MODE_DEFAULT

0

Further connections to the database are prohibited. Wait for users to disconnect from the database.

oracledb.SHUTDOWN_MODE_FINAL

5

Used with a second connection.shutdown() to conclude the database shut down steps.

oracledb.SHUTDOWN_MODE_IMMEDIATE

3

All uncommitted transactions are terminated and rolled back and all connections to the database are closed immediately.

oracledb.SHUTDOWN_MODE_TRANSACTIONAL

1

Further connections to the database are prohibited and no new transactions are allowed to be started. Wait for active transactions to complete.

oracledb.SHUTDOWN_MODE_TRANSACTIONAL_LOCAL

2

Behaves the same way as SHUTDOWN_MODE_TRANSACTIONAL, but only waits for local transactions to complete.

1.1.13. Two-Phase Commit Constants

Added in version 5.3.

Constants for two-phase commit (TPC) functions connection.tpcBegin() and connection.tpcEnd().

Table 1.22 Two-Phase Commit Constants

Constant Name

Value

Description

oracledb.TPC_BEGIN_JOIN

2

Join an existing two-phase commit (TPC) transaction.

oracledb.TPC_BEGIN_NEW

1

Create a new TPC transaction.

oracledb.TPC_BEGIN_RESUME

4

Resume an existing TPC transaction.

oracledb.TPC_BEGIN_PROMOTE

8

Promote a local transaction to a TPC transaction.

oracledb.TPC_END_NORMAL

0

End the TPC transaction participation normally.

oracledb.TPC_END_SUSPEND

1048576

Suspend the TPC transaction.

1.1.14. Vector Type Constants

Added in version 6.5.

Constants for the vectorFormat attribute.

Table 1.23 Vector Type Constants

Constant Name

Value

Description

oracledb.VECTOR_FORMAT_FLOAT32

2

The storage format of each dimension value in the VECTOR column is a 32-bit floating-point number.

oracledb.VECTOR_FORMAT_FLOAT64

3

The storage format of each dimension value in the VECTOR column is a 64-bit floating-point number.

oracledb.VECTOR_FORMAT_INT8

4

The storage format of each dimension value in the VECTOR column is an 8-bit signed integer.

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

oracledb.autoCommit

This property is a boolean value. 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.

When using an external transaction manager with two-phase commits, autoCommit should be false.

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

Example

const oracledb = require('oracledb');
oracledb.autoCommit = false;
oracledb.connectionClass

The user-chosen Connection class value is a string which 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, the session is not shared with users with a different 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.

If connectionClass is set for a non-pooled connection, the driver name is not recorded in V$ views. See End-to-end Tracing, Mid-tier Authentication, and Auditing.

Example

const oracledb = require('oracledb');
oracledb.connectionClass = 'HRPOOL';
oracledb.dbObjectAsPojo

This property is a boolean which specifies whether Oracle Database named objects or collections that are queried should be returned to the application as “plain old JavaScript objects” or kept as database-backed objects. This option also applies to output BIND_OUT bind variables.

Note that LOBs in objects will be represented as Lob instances and will not be String or Buffer, regardless of any fetchAsString, fetchAsBuffer, or fetchInfo setting.

The default value for dbObjectAsPojo is false.

Setting dbObjectAsPojo to true can avoid overhead if object attributes are repeatedly accessed. It also allows applications to close connections before any attributes are accessed unless LOBs are involved. Regardless of the value, the interface to access objects is the same.

Example

const oracledb = require('oracledb');
oracledb.dbObjectAsPojo = false;
oracledb.edition

Added in version 2.2.

This property is a string that sets the name used for Edition-Based Redefinition by connections.

See Edition-Based Redefinition for more information.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Example

const oracledb = require('oracledb');
oracledb.edition = 'ed_2';
oracledb.errorOnConcurrentExecute

Added in version 5.2.

This property is a boolean that can be set to throw an error if concurrent operations are attempted on a single connection.

The default value for errorOnConcurrentExecute is false.

Each Oracle connection can only interact with the database for one operation at a time. Attempting to do more than one operation concurrently may be a sign of an incorrectly coded application, for example an await may be missing. Examples of operations that cannot be executed in parallel on a single connection include connection.execute(), connection.executeMany(), connection.queryStream(), connection.getDbObjectClass(), connection.commit(), connection.close(), SODA calls, and streaming from Lobs.

The value of this property does not affect using multiple connections. These may all be in use concurrently, and each can be doing one operation.

Leaving errorOnConcurrentExecute set to false is recommended for production applications. This will avoid unexpected errors. Some frameworks may execute concurrent statements on a connection by design. Also some application modules may have the expectation that node-oracledb will handle any necessary connection usage serialization.

For more discussion, see Parallelism on Each Connection <parallelism>.

Example

const oracledb = require('oracledb');
oracledb.errorOnConcurrentExecute = false;
oracledb.events

Added in version 2.2.

This property is a boolean that determines whether Oracle Client events mode should be enabled.

The default value for events is false.

This property can be overridden in the oracledb.createPool() call and when getting a standalone connection from oracledb.getConnection().

Events mode is required for Continuous Query Notification, Fast Application Notification (FAN) and Runtime Load Balancing (RLB).

In node-oracledb 4.0.0 and 4.0.1, the default value for events was true.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Example

const oracledb = require('oracledb');
oracledb.events = false;
oracledb.extendedMetaData

Desupported in version 6.0.

Extended metadata is now always returned

Added in version 1.10.

This property is a boolean that 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 and 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.

oracledb.externalAuth

This property is a boolean value. If this property is true in node-oracledb Thick mode, then connections are established using external authentication. See External Authentication for more information.

In node-oracledb Thin mode, when token-based authentication is required, this property must be set to true. In all the other cases where this property is set to true, an error is thrown.

The default value is false.

The user (or username) and password properties should not be set when externalAuth is true.

This property can be overridden in the oracledb.createPool() call and when getting a standalone connection from oracledb.getConnection().

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

Example

const oracledb = require('oracledb');
oracledb.externalAuth = false;
oracledb.fetchArraySize

Added in version 2.0.

This property is a number that sets the size of an internal buffer used for fetching query rows from Oracle Database. Changing it may affect query performance but does not affect how many rows are returned to the application.

The default value is 100.

The property is used during the default direct fetches, during ResultSet resultset.getRow() calls, and for connection.queryStream(). It is used for resultset.getRows() when no argument (or the value 0) is passed to getRows().

Increasing this value reduces the number of round-trips to the database but increases memory usage for each data fetch. For queries that return a large number of rows, higher values of fetchArraySize may give better performance. For queries that only return a few rows, reduce the value of fetchArraySize to minimize the amount of memory management during data fetches. JavaScript memory fragmentation may occur in some cases, see Fetching Rows with Direct Fetches.

For direct fetches (those using execute() option resultSet: false), the internal buffer size will be based on the lesser of oracledb.maxRows and fetchArraySize.

This property can be overridden by the execute() option fetchArraySize.

See Tuning Fetch Performance for more information.

Example

const oracledb = require('oracledb');
oracledb.fetchArraySize = 100;
oracledb.fetchAsBuffer

Added in version 1.13.

This property is an array of type constants that allows query columns to be returned as Buffers.

Currently the only valid constant is oracledb.BLOB or its equivalent oracledb.DB_TYPE_BLOB.

When set, and a BLOB column is queried with execute() or queryStream(), then the column data is returned as a Buffer instead of the default Lob instance. Individual query columns in execute() or queryStream() calls can override the fetchAsBuffer global setting by using fetchInfo.

Example

const oracledb = require('oracledb');
oracledb.fetchAsBuffer = [ oracledb.BLOB ];
oracledb.fetchAsString

This property is an array that allows query columns to be returned as Strings instead of the default type.

In node-oracledb, all columns are returned as the closest JavaScript type, or as Lob instances in the case of CLOB and NCLOB types. (See Query Result Type Mapping). The fetchAsString property can override this default type mapping.

The fetchAsString property should be an array of type constants. The valid constants are oracledb.DATE, oracledb.NUMBER, oracledb.BUFFER, oracledb.CLOB, and oracledb.NCLOB. The equivalent DB_TYPE_* constants can also be used.

When any column having one of the types is queried with execute() or queryStream(), the column data is returned as a string instead of the default representation. Individual query columns in execute() or queryStream() calls can override the fetchAsString global setting by using fetchInfo.

Note:

  • Specifying oracledb.NUMBER will affect numeric columns. The fetchAsString property helps avoid situations where using JavaScript types can lead to numeric precision loss.

  • Specifying oracledb.CLOB will affect both CLOB and NCLOB columns. Similarly, specifying oracledb.NCLOB will also affect both CLOB and NCLOB columns. Using fetchAsString automatically fetches LOB data directly in query output without requiring streaming.

  • Specifying oracledb.DATE will affect date and timestamp columns. Using fetchAsString can be helpful to avoid date conversions.

When oracledb.BUFFER is used for RAW data, Oracle returns the data as a hex-encoded string. For dates and numbers returned as a string, the maximum length of a string created by this mapping is 200 bytes. Strings created for CLOB and NCLOB columns will generally be limited by Node.js and V8 memory restrictions.

Example

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

Added in version 6.0.

This property is a function that allows applications to examine and modify queried column data before it is returned to the user. This function is called once for each column that is being fetched with a single object argument containing the following attributes:

  • annotations: The object representing the annotations.

  • byteSize: The maximum size in bytes. This is only set if dbType is oracledb.DB_TYPE_VARCHAR, oracledb.DB_TYPE_CHAR, or oracledb.DB_TYPE_RAW.

  • dbType: The database type, that is, one of the Oracle Database Type Objects.

  • dbTypeName: The name of the database type, such as “NUMBER” or “VARCHAR2”.

  • dbTypeClass: The class associated with the database type. This is only set if dbType is oracledb.DB_TYPE_OBJECT.

  • domainName: The name of the SQL domain.

  • domainSchema: The schema name of the SQL domain.

  • isJson: Indicates if the column is known to contain JSON data.

  • name: The name of the column.

  • nullable: Indicates whether NULL values are permitted for this column.

  • precision: Set only when the dbType is oracledb.DB_TYPE_NUMBER.

  • scale: Set only when the dbType is oracledb.DB_TYPE_NUMBER.

By default, this property is “undefined”, that is, it is not set.

The function is expected to return either nothing or an object containing:

  • the type attribute

  • or the converter attribute

  • or both the type and converter attributes

The converter function is a function which can be used with fetch type handlers to change the returned data. This function accepts the value that will be returned by connection.execute() for a particular row and column and returns the value that will actually be returned by connection.execute().

This property can be overridden by the fetchTypeHandler option in execute().

See Using Fetch Type Handlers.

Changed in version 6.3: The annotations, domainName, domainSchema, and isJson information attributes were added.

Example

const oracledb = require('oracledb');
oracledb.fetchTypeHandler = function(metaData) {
// Return number column data as strings
    if (metaData.dbType == oracledb.DB_TYPE_NUMBER) {
        return {type: oracledb.STRING};
    }
}
oracledb.lobPrefetchSize

This property is a number and is temporarily disabled. Setting it has no effect. For best performance, fetch Lobs as Strings or Buffers.

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

const oracledb = require('oracledb');
oracledb.lobPrefetchSize = 16384;
oracledb.maxRows

This property is the maximum number of rows that are fetched by a query with connection.execute() when not using a ResultSet. Rows beyond this limit are not fetched from the database. A value of 0 means there is no limit.

For nested cursors, the limit is also applied to each cursor.

The default value is 0, meaning unlimited.

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

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.

For queries that return a fixed, small number of rows, then set maxRows to that value. For example, for queries that return one row, set maxRows to 1.

When the number of query rows is relatively big, or can not be predicted, it is recommended to use a ResultSet or queryStream(). This allows applications to process rows in smaller chunks or individually, preventing the Node.js memory limit being exceeded or query results being unexpectedly truncated by a maxRows limit.

In version 1, the default value was 100.

Example

const oracledb = require('oracledb');
oracledb.maxRows = 0;
oracledb.oracleClientVersion

Added in version 1.3.

This read-only property gives a numeric representation of the Oracle Client library version which is useful in comparisons. For version a.b.c.d.e, this property gives the number: (100000000 * a) + (1000000 * b) + (10000 * c) + (100 * d) + e

From node-oracledb 3.1.0, using oracledb.oracleClientVersion will throw a DPI-1047 error if node-oracledb cannot load Oracle Client libraries. Previous versions threw this error from require('oracledb').

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Example

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

Added in version 2.2.

This read-only property gives a string representation of the Oracle Client library version which is useful for display.

From node-oracledb 3.1.0, using oracledb.oracleClientVersionString will throw a DPI-1047 error if node-oracledb cannot load Oracle Client libraries. Previous versions threw this error from require('oracledb').

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Example

const oracledb = require('oracledb');
console.log("Oracle client library version is " + oracledb.oracleClientVersionString);
oracledb.outFormat

This property is a number that identifies the format of query rows fetched when using connection.execute() or connection.queryStream(). It affects both ResultSet and non-ResultSet queries. It can be used for top level queries and REF CURSOR output.

This can be either of the Oracledb constants oracledb.OUT_FORMAT_ARRAY or oracledb.OUT_FORMAT_OBJECT. The default value is oracledb.OUT_FORMAT_ARRAY which is more efficient. The older, equivalent constants oracledb.ARRAY and oracledb.OBJECT are deprecated.

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

If specified as oracledb.OUT_FORMAT_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.

From node-oracledb 5.1, when duplicate column names are used in queries, then node-oracledb will append numeric suffixes in oracledb.OUT_FORMAT_OBJECT mode as necessary, so that all columns are represented in the JavaScript object. This was extended in node-oracledb 5.2 to also cover duplicate columns in nested cursors and REF CURSORS.

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

See Query Output Formats for more information.

Example

const oracledb = require('oracledb');
oracledb.outFormat = oracledb.OUT_FORMAT_ARRAY;
oracledb.poolIncrement

This property is the number of connections that are opened whenever a connection request exceeds the number of currently open connections.

The default value is 1.

With fixed-size homogeneous pools (where poolMin equals poolMax), and when using Oracle Client 18c (or later) for node-oracledb Thick mode, you may wish to evaluate setting poolIncrement greater than 1. This can expedite regrowth when the number of connections established has become lower than poolMin, for example, when network issues cause connections to become unusable and get them dropped from the pool.

This property may be overridden when creating a connection pool.

Example

const oracledb = require('oracledb');
oracledb.poolIncrement = 1;
oracledb.poolMax

This property is 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.

Importantly, if you increase poolMax you should also increase the number of threads available to node-oracledb. See Connections and Number of Threads.

A fixed pool size where poolMin equals poolMax is strongly recommended. This helps prevent connection storms and helps overall system stability.

See Connection Pooling for pool sizing guidelines.

Example

const oracledb = require('oracledb');
oracledb.poolMax = 4;
oracledb.poolMaxPerShard

Added in version 4.1.

This property sets the maximum number of connection in the pool that can be used for any given shard in a sharded database. This lets connections in the pool be balanced across the shards. A value of zero will not set any maximum number of sessions for each shard.

This property may be overridden when creating a connection pool.

When this property is greater than zero, and a new connection request would cause the number of connections to the target shard to exceed the limit, then that new connection request will block until a suitable connection has been released back to the pool. The pending connection request will consume one worker thread.

See Connecting to Sharded Databases for more information.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

It is available when node-oracledb uses Oracle client libraries 18.3, or later.

Example

const oracledb = require('oracledb');
oracledb.poolMaxPerShard = 0;
oracledb.poolMin

This property is a number that identifies the number of connections established to the database when a pool is created. Also, this is the minimum number of connections that a pool maintains when it shrinks, see oracledb.poolTimeout.

The default value is 0.

This property may be overridden when creating a connection pool.

A fixed pool size where poolMin equals poolMax is strongly recommended. This helps prevent connection storms and helps overall system stability.

For pools created with External Authentication, with homogeneous set to false, or when using Database Resident Connection Pooling (DRCP), then the number of connections initially created is zero even if a larger value is specified for poolMin. Also in these cases the pool increment is always 1, regardless of the value of poolIncrement. Once the number of open connections exceeds poolMin then the number of open connections does not fall below poolMin.

Example

const oracledb = require('oracledb');
oracledb.poolMin = 0;
oracledb.poolPingInterval

Added in version 1.12.

This property is a number value. When a pool pool.getConnection() is called and the connection has been idle in the pool for at least poolPingInterval seconds, node-oracledb internally “pings” the database to check the connection is alive. After a ping, an unusable connection is destroyed and a usable one is returned by getConnection(). Connection pinging improves the chance a pooled connection is usable by the application because unusable connections are less likely to be returned by oracledb.getConnection().

The default poolPingInterval value is 60 seconds. Possible values are:

Table 1.24 poolPingInterval Values

poolPingInterval Value

Behavior of a Pool getConnection() Call

n < 0

Never checks for connection validity.

n = 0

Always checks for connection validity. This value is not recommended for most applications because of the overhead in performing each ping.

n > 0

Checks validity if the connection has been idle in the pool (not “checked out” to the application by getConnection()) for at least n seconds.

This property may be overridden when creating a connection pool using oracledb.createPool().

See Connection Pool Pinging for more discussion.

It was disabled when using Oracle Client 12.2 (and later) until node-oracledb 3.0.

Example

const oracledb = require('oracledb');
oracledb.poolPingInterval = 60;     // seconds
oracledb.poolPingTimeout

Added in version 6.4.

This property is the number of milliseconds that a connection should wait for a response from connection.ping(). If ping() does not respond by the time specified in this property, then the connection is forcefully closed.

The default value is 5000 milliseconds. The behavior of a pool getConnection() call differs based on the value specified in the poolPingTimeout property as detailed below.

Table 1.25 poolPingTimeout Values

poolPingTimeout Value

Behavior of a Pool getConnection() Call

n < 0

Returns the error NJS-007: invalid value for "poolPingTimeout" in parameter 1 if the poolPingTimeout property in oracledb.createPool() is set to a negative value.

Returns the error NJS-004: invalid value for property "poolPingTimeout" if oracledb.poolPingTimeout is set to a negative value.

n = 0

Waits until connection.ping() succeeds with a response or fails with an error.

n > 0

Waits for connection.ping() to respond by n milliseconds.

If ping() does not respond by n milliseconds, then the connection is forcefully closed.

This property may be overridden when creating a connection pool.

Example

const oracledb = require('oracledb');
oracledb.poolPingTimeout = 5000; // milliseconds
oracledb.poolTimeout

This property is a number that allows the number of open connections in a pool to shrink to oracledb.poolMin.

If the application returns connections to the pool with connection.close(), and the connections are then unused for more than poolTimeout seconds, then any excess connections above poolMin will be closed. When using Oracle Client prior to version 21, this pool shrinkage is only initiated when the pool is accessed.

If poolTimeout is set to 0, then idle connections are never terminated.

If you wish to change poolTimeout with pool.reconfigure(), then the initial poolTimeout used by oracledb.createPool() must be non-zero.

The default value is 60.

This property may be overridden when creating a connection pool.

Example

const oracledb = require('oracledb');
oracledb.poolTimeout = 60;
oracledb.prefetchRows

This property is a query tuning option to set the number of additional rows the underlying Oracle Client library fetches during the internal initial statement execution phase of a query. The prefetch size does not affect when, or how many, rows are returned by node-oracledb to the application.

The prefetchRows attribute can be used in conjunction with oracledb.fetchArraySize to tune query performance, memory use, and to reduce the number of round-trip calls needed to return query results, see Tuning Fetch Performance.

The prefetchRows value is ignored in some cases, such as when the query involves a LOB.

If you fetch a REF CURSOR, retrieve rows from that cursor, and then pass it back to a PL/SQL block, you should set prefetchRows to 0 during the initial statement that gets the REF CURSOR. This ensures that rows are not internally fetched from the REF CURSOR by node-oracledb thus making them unavailable in the final PL/SQL code.

The default value is 2.

This property may be overridden in an connection.execute() call, which is preferred usage if you need to change the value.

This attribute is not used in node-oracledb version 2, 3 or 4. In those versions use only oracledb.fetchArraySize instead.

Example

const oracledb = require('oracledb');
oracledb.prefetchRows = 2;
oracledb.Promise

The ``oracledb.Promise`` property is no longer used in node-oracledb 5 and has no effect.

Node-oracledb supports Promises on all methods. The native Promise library is used. See Promises and node-oracledb for a discussion of using Promises.

Example

Prior to node-oracledb 5, this property could be set to override or disable the Promise implementation.

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

Prior to node-oracledb 5, Promises could be completely disabled by setting:

oracledb.Promise = null;
oracledb.queueMax

Added in version 5.0.

This property is the maximum number of pending pool.getConnection() calls that can be queued.

When the number of pool.getConnection() calls that have been queued waiting for an available connection reaches queueMax, then any future pool.getConnection() calls will immediately return an error and will not be queued.

If queueMax is -1, then the queue length is not limited.

The default value is 500.

This property may be overridden when creating a connection pool.

Example

const oracledb = require('oracledb');
oracledb.queueMax = 500;
oracledb.queueRequests

This property was removed in node-oracledb 3.0 and queuing was always enabled. From node-oracledb 5.0, set queueMax to 0 to disable queuing. See Connection Pool Queue for more information.

oracledb.queueTimeout

Added in version 1.7.

This property is 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.

If immediate timeout is desired, set related property oracledb.queueMax to 0.

The default value is 60000.

This property may be overridden when creating a connection pool.

See Connection Pool Queue for more information.

Example

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

This property is the number of statements that are cached in the statementcache 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

const oracledb = require('oracledb');
oracledb.stmtCacheSize = 30;
oracledb.thin

Added in version 6.0.

This property is a boolean that determines the node-oracledb driver mode which is in use. If the value is true, it indicates that node-oracledb Thin mode is in use. If the value is false, it indicates that node-oracledb Thick mode is in use.

The default value is true.

Immediately after node-oracledb is imported, this property is set to true indicating that node-oracledb defaults to Thin mode. If oracledb.initOracleClient() is called, then the value of this property is set to False indicating that Thick mode is enabled. Once the first standalone connection or connection pool is created, or a call to oracledb.initOracleClient() is made, then node-oracledb’s mode is fixed and the value set in oracledb.thin will never change for the lifetime of the process.

The property connection.thin can be used to check a connection’s mode and the attribute pool.thin can be used to check a pool’s mode. The value that is displayed for the connection.thin, pool.thin, and oracledb.thin attributes will be the same.

oracledb.version

This read-only 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

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

Added in version 2.1.

This read-only property gives a string representation of the node-oracledb version, including the version suffix if one is present.

Example

const oracledb = require('oracledb');
console.log("Driver version is " + oracledb.versionString);
oracledb.versionSuffix

Added in version 2.1.

This read-only property gives a string representing the version suffix (for example, “-dev” or “-beta”) or an empty string if no version suffix is present.

Example

const oracledb = require('oracledb');
console.log("Driver version suffix is " + oracledb.versionSuffix);

1.3. Oracledb Methods

oracledb.createPool()

Promise:

promise = createPool(Object poolAttrs);

Creates a pool of connections with the specified user name, password and connection string. A pool is typically created once during application initialization.

In node-oracledb Thick mode, createPool() internally creates an Oracle Call Interface 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(). This allows pools to later be accessed by name, removing the need to pass the pool object through code. See Connection Pool Cache for more details.

A pool should be terminated with the pool.close() call.

From node-oracledb 3.1.0, the createPool() error callback will return a DPI-1047 error if node-oracledb cannot load Oracle Client libraries. Previous versions threw this error from require('oracledb').

See Connection Pooling for more information about pooling.

The parameters of the oracledb.createPool() method are:

Table 1.26 oracledb.createPool() Parameters

Parameter

Data Type

Description

poolAttrs

Object

The poolAttrs parameter object provides connection credentials and pool-specific configuration properties, such as the maximum or minimum number of connections for the pool, or the statement cache size for the connections.

The properties provided in the poolAttrs parameter override the default pooling properties of the Oracledb object. If an attribute is not set, or is null, the value of the related Oracledb property will be used.

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

See createPool(): poolAttrs Parameter Properties for information on the properties of poolAttrs.

The properties of poolAttrs are:

Table 1.27 createPool(): poolAttrs Parameter Properties

Property

Data Type

node-oracledb Mode

Description

accessToken

Function, String, Object

Both

For Microsoft Azure Active Directory OAuth 2.0 token-based authentication, accessToken can be:

  • a callback function returning the token as a string

  • an object with a token attribute containing the token as a string

  • or the token as a string

Tokens can be obtained using various approaches. For example, using the Azure Active Directory API.

For Oracle Cloud Infrastructure Identity and Access Management (IAM) token-based authentication, accessToken can be:

  • a callback function returning an object containing token and privateKey attributes

  • or an object containing token and privateKey attributes

The properties of the accessToken object are described in createPool(): accessToken Object Attributes.

If accessToken is a callback function:

function accessToken(boolean refresh, object accessTokenConfig)

When accessToken is a callback function, it will be invoked at the time the pool is created (even if poolMin is 0). It is also called when the pool needs to expand (causing new connections to be created) and the current token has expired. The returned token is used by node-oracledb for authentication. The refresh parameter is described in createPool(): refresh Parameter Values. The accessTokenConfig parameter is described in accessTokenConfig.

When the callback is first invoked, the refresh parameter will be set to false. This indicates that the application can provide a token from its own application managed cache, or it can generate a new token if there is no cached value. Node-oracledb checks whether the returned token has expired. If it has expired, then the callback function will be invoked a second time with refresh set to true. In this case the function must externally acquire a token, optionally add it to the application’s cache, and return the token.

For token-based authentication, the externalAuth and homogeneous pool attributes must be set to true. The user (or username) and password attributes should not be set.

See Token-Based Authentication for more information.

Added in version 5.4: The accessToken property was added to support IAM token-based authentication.For IAM token-based authentiation, this property must be an Object. For node-oracledb Thick mode, Oracle Client libraries 19.14 (or later), or 21.5 (or later) must be used for IAM token-based authentication.

Changed in version 5.5: The accessToken property was extended to allow OAuth 2.0 token-based authentication in node-oracledb 5.5. For OAuth 2.0, the property should be a string, or a callback. For node-oracledb Thick mode, Oracle Client libraries 19.15 (or later), or 21.7 (or later) must be used. The callback usage supports both OAuth 2.0 and IAM token-based authentication.

accessTokenCallback

Object

NA

This optional attribute is a Node.js callback function. It gets called by the connection pool if the pool needs to grow and create new connections but the current token has expired.

The callback function must return a JavaScript object with attributes token and privateKey for IAM. See Connection Pool Creation with Access Tokens for IAM.

Added in version 5.4.

It should be used with Oracle Client libraries 19.14 (or later), or 21.5 (or later).

Deprecated since version 5.5.

Desupported in version 6.0.

Use accessToken with a callback instead.

accessTokenConfig

Object

Both

An object containing the Azure-specific or OCI-specific parameters that need to be set when using the Azure Software Development Kit (SDK) or Oracle Cloud Infrastructure (OCI) SDK for token generation. This property should only be specified when the accessToken property is a callback function. For more information on the Azure-specific parameters, see sampleazuretokenauth.js and for the OCI-specific parameters, see sampleocitokenauth.js.

For OAuth2.0 token-based authentication and when using node-oracledb Thick mode, Oracle Client libraries 19.15 (or later), or 21.7 (or later) must be used. For IAM token-based authentication and when using node-oracledb Thick mode, Oracle Client libraries 19.14 (or later), or 21.5 (or later) are required.

Added in version 6.3.

connectString, connectionString

String

Both

The Oracle database instance used by connections in the pool. 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.

Added in version 2.1: The alias connectionString.

walletPassword

String

Thin

The password to decrypt the Privacy Enhanced Mail (PEM)-encoded private certificate, if it is encrypted.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

walletLocation

String

Thin

The directory where the wallet can be found. In node-oracledb Thin mode, this must be the directory that contains the PEM-encoded wallet file.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

edition

String

Thick

Sets the name used for Edition-Based Redefinition by connections in the pool.

This optional property overrides the oracledb.edition property.

Added in version 2.2.

enableStatistics

Boolean

Both

Recording of pool statistics can be enabled by setting enableStatistics to true. Statistics can be retrieved with pool.getStatistics(), or pool.logStatistics(). See Connection Pool Monitoring.

The default value is false.

Added in version 5.2.

The obsolete property _enableStats can still be used, but it will be removed in a future version of node-oracledb.

events

Boolean

Thick

Indicates whether Oracle Call Interface events mode should be enabled for this pool.

This optional property overrides the oracledb.events property.

Added in version 2.2.

externalAuth

Boolean

Both

Indicates whether pooled connections should be established using External Authentication.

The default is false.

In Thin mode, when token-based authentication is required, this property must be set to true. In all the other cases where this property is set to true, an error is thrown.

This optional property overrides the oracledb.externalAuth property.

The user (or username) and password properties should not be set when externalAuth is true.

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

homogeneous

Boolean

Both

Indicates whether connections in the pool all have the same credentials (a ‘homogeneous’ pool), or whether different credentials can be used (a ‘heterogeneous’ pool).

The default is true.

For the Thin mode, only homogeneous pools can be created. If this property is set to false in Thin mode, an error will be thrown.

When set to false in Thick mode, the user name and password can be omitted from the connection.createPool() call, but will need to be given for subsequent pool.getConnection() calls. Different pool.getConnection() calls can provide different user credentials. Alternatively, when homogeneous is false, the user name (the ‘proxy’ user name) and password can be given, but subsequent pool.getConnection() calls can specify a different user name to access that user’s schema.

Heterogeneous pools cannot be used with the connection pool cache. Applications should ensure the pool object is explicitly passed between code modules, or use a homogeneous pool and make use of connection.clientId.

See Heterogeneous Connection Pools and Pool Proxy Authentication for details and examples.

Added in version 2.3.

password

String

Both

The password of the database user used by connections in the pool. A password is also necessary if a proxy user is specified at pool creation.

If homogeneous is false, then the password may be omitted at pool creation but given in subsequent pool.getConnection() calls.

poolAlias

String

Both

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.

Added in version 1.11.

privilege

Number

Thin

The privilege to use when establishing a connection to the database. This optional property should be one of the privileged connection constants. All privileges must be specified individually except for oracledb.SYSPRELIM.

oracledb.SYSPRELIM is specified only for startup and shutdown calls and must be used in combination with SYSDBA (oracledb.SYSDBA | oracledb.SYSPRELIM) or SYSOPER (oracledb.SYOPER | oracledb.SYSPRELIM).

See Privileged Connections for more information.

Added in version 6.5.1.

configDir

String

Thin

The directory in which the Optional Oracle Net Configuration Files are found.

For node-oracledb Thick mode, use the oracledb.initOracleClient() option configDir instead.

Added in version 6.0.

sourceRoute

String

Thin

Enables network routing through multiple protocol addresses. The value of this property can be ON or OFF.

The default value is ON.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sslServerCertDN

String

Thin

The distinguished name (DN) that should be matched with the certificate DN. If not specified, a partial match is performed instead. A partial match matches the hostname that the client connected to against the common name (CN) of the certificate DN or the Subject Alternate Names (SAN) of the certificate.

This value is ignored if the sslServerDNMatch property is not set to the value True.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sslServerDNMatch

Boolean

Thin

Determines whether the server certificate DN should be matched in addition to the regular certificate verification that is performed.

If the sslServerCertDN property is not provided, a partial DN match is performed instead. A partial match matches the hostname that the client connected to against the CN of the certificate DN or the SAN of the certificate.

The default value is True.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sslAllowWeakDNMatch

Boolean

Thin

Enables the connection to use either a weaker or more secure DN matching behavior when the sslServerDNMatch property is set.

If the value is True, then the sslServerDNMatch property uses a weaker DN matching behavior which only checks the server certificate (and not the listener certificate), and allows the service name to be used for partial DN matching. The DN matching for a partial match first matches the host name that the client connected to against the CN of the database server certificate DN or the SAN of the database server certificate. If this fails, then the service name is matched against the CN of the database server certificate DN.

If the value is False, then the sslServerDNMatch property uses a more secure DN matching behavior which checks both the listener and server certificates, and does not allow a service name check for partial DN matching. The DN matching for a partial match matches the host name that the client connected to against the CN of the certificate DN or the SAN of the certificate. The service name is not checked in this case.

The default value is False.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.1.

httpsProxy

String

Thin

The name or IP address of a proxy host to use for tunneling secure connections.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

httpsProxyPort

Number

Thin

The port to be used to communicate with the proxy host.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

retryCount

Number

Thin

The number of times that a connection attempt should be retried before the attempt is terminated.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

retryDelay

Number

Thin

The number of seconds to wait before making a new connection attempt.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

connectTimeout

Number

Thin

The timeout duration in seconds for an application to establish an Oracle Net connection.

There is no timeout by default.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

transportConnectTimeout

Number

Thin

The maximum number of seconds to wait to establish a connection to the database host.

The default value is 60.0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

expireTime

Number

Thin

The number of minutes between the sending of keepalive probes. If this property is set to a value greater than zero, it enables the keepalive probes.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sdu

Number

Thin

The Oracle Net Session Data Unit (SDU) packet size in bytes. The database server configuration should also set this parameter.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

connectionIdPrefix

String

Thin

The application specific prefix parameter that is added to the connection identifier.

Added in version 6.0.

poolIncrement

Number

Both

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

The default value is 1.

This optional property overrides the oracledb.poolIncrement property.

poolMax

Number

Both

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

The default value is 4.

This optional property overrides the oracledb.poolMax property.

Importantly, if you increase poolMax you should also increase the number of threads available to node-oracledb. See Connections and Number of Threads.

See Connection Pooling for other pool sizing guidelines.

poolMaxPerShard

Number

Thick

Sets the maximum number of connections per shard for connection pools. This ensures that the pool is balanced towards each shard.

This optional property overrides the oracledb.poolMaxPerShard property.

Added in version 4.1.

poolMin

Number

Both

The number of connections established to the database when a pool is created. Also this is the minimum number of connections that a pool maintains when it shrinks.

The default value is 0.

This optional property overrides the oracledb.poolMin property.

poolPingInterval

Number

Both

When a pool pool.getConnection() is called and the connection has been idle in the pool for at least poolPingInterval seconds, an internal “ping” will be performed first to check the validity of the connection.

The default value is 60.

This optional property overrides the oracledb.poolPingInterval property.

See Connection Pool Pinging for more information.

poolPingTimeout

Number

Both

The number of milliseconds that a connection should wait for a response from connection.ping(). Refer to oracledb.poolPingTimeout for details.

The default value is 5000 milliseconds.

This optional property overrides the oracledb.poolPingTimeout property.

See Connection Pool Pinging for more information.

Added in version 6.4.

poolTimeout

Number

Both

The number of seconds after which idle connections (unused in the pool) may be terminated. Refer to oracledb.poolTimeout for details.

The default value is 60.

This optional property overrides the oracledb.poolTimeout property.

queueMax

Number

Both

The maximum number of pending pool.getConnection() calls that can be queued.

When the number of pool.getConnection() calls that have been queued waiting for an available connection reaches queueMax, then any future pool.getConnection() calls will immediately return an error and will not be queued.

If queueMax is -1, then the queue length is not limited.

The default value is 500.

This optional property overrides the oracledb.queueMax property.

Added in version 5.0.

queueRequests

NA

NA

This property was removed in node-oracledb 3.0 and queuing was always enabled. From node-oracledb 5.0, set queueMax to 0 to disable queuing. See Connection Pool Queue for more information.

queueTimeout

Number

Both

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.

The default value is 60000.

This optional property overrides the oracledb.queueTimeout property.

sessionCallback

String or Function

Both

If the sessionCallback is a callback function:

function sessionCallback(Connection connection, String requestedTag, function callback(Error error, Connection connection){})

When sessionCallback is a Node.js function, each pool.getConnection() will select a connection from the pool and may invoke sessionCallback before returning. The sessionCallback function is called:

  • when the pool selects a brand new, never used connection in the pool.

  • if the pool selects a connection from the pool with a given tag but that tag string value does not match the connection’s current, actual tag. The tag requested (if any) by pool.getConnection() is available in the requestedTag parameter. The actual tag in the connection selected by the pool is available in connection.tag.

It will not be invoked for other pool.getConnection() calls.

The session callback is called before pool.getConnection() returns so it can be used for logging or to efficiently set session state, such as with ALTER SESSION statements. Make sure any session state is set and connection.tag is updated in the sessionCallback function prior to it calling its own callback() function otherwise the session will not be correctly set when getConnection() returns. The connection passed into sessionCallback should be passed out through callback() so it is returned from the application’s pool.getConnection() call.

When node-oracledb Thick mode is using Oracle Client libraries 12.2 or later, tags are multi-property tags with name=value pairs like “k1=v1;k2=v2”.

When node-oracledb Thick mode is using Oracle Client libraries 12.2 or later, sessionCallback can be a string containing the name of a PL/SQL procedure to be called when pool.getConnection() requests a tag, and that tag does not match the connection’s actual tag. When the application uses DRCP connections, a PL/SQL callback can avoid the round-trip calls that a Node.js function would require to set session state. For non-DRCP connections, the PL/SQL callback will require a round-trip from the application.

The PL/SQL procedure declaration is:

PROCEDURE mycallback (
  desired_props IN  VARCHAR2,
  actual_props  IN  VARCHAR2
);

See Connection Tagging and Session State for more information.

Added in version 3.1.

sodaMetaDataCache

Boolean

Thick

Indicates whether the pool’s connections should share a cache of SODA metadata. This improves SODA performance by reducing round-trips to the database when opening collections. It has no effect on non-SODA operations.

The default is false.

There is no global equivalent for setting this attribute. SODA metadata caching is restricted to pooled connections only.

Note that if the metadata of a collection is changed externally, the cache can get out of sync. If this happens, the cache can be cleared by calling pool.reconfigure({sodaMetadataCache: false}). See pool.reconfigure().

A second call to reconfigure() should then be made to re-enable the cache.

Added in version 5.2.

It requires Oracle Client 21.3 (or later). The feature is also available in Oracle Client 19c from 19.11 onward.

stmtCacheSize

Number

Both

The number of statements to be cached in the statementcache of each connection in the pool.

This optional property overrides the oracledb.stmtCacheSize property.

user, username

String

Both

The two properties are aliases for each other. Use only one of the properties.

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

If homogeneous is false, then the pool user name and password need to be specified only if the application wants that user to proxy the users supplied in subsequent pool.getConnection() calls.

Added in version 5.2: The alias username.

createPool(): accessToken Object Properties

The properties of the accessToken object are:

Table 1.28 createPool(): accessToken Object Attributes

Attribute

Description

token

The database authentication token.

privateKey

The database authentication private key.

The token and privateKey values can be obtained using various approaches. For example the Oracle Cloud Infrastructure Command Line Interface can be used.

createPool(): refresh Parameter

The refresh parameter values are:

Table 1.29 createPool(): refresh Parameter Values

refresh Value

Description

false

The application can return a token from an application-specific cache. If there is no cached token, the application must externally acquire one.

true

The token previously passed to driver is known to be expired, the application should externally acquire a new token.

Callback:

If you are using the callback programming style:

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

See oracledb.createPool() Parameters for information on the poolAttrs parameter.

The parameters of the callback function function(Error error, Pool pool) are:

Callback Function Parameter

Description

Error error

If createPool() succeeds, error is NULL. If an error occurs, then error contains the error message.

Pool pool

The newly created connection pool. If createPool() fails, pool will be NULL. If the pool will be accessed via the pool cache, this parameter can be omitted. See Pool class for more information.

oracledb.getConnection()

Promise:

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

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

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

Note: It is recommended to explicitly close a connection. If not, you may experience a short delay when the application terminates. This is due to the timing behavior of Node.js garbage collection which needs to free the connection reference.

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

Signature

Description

oracledb.getConnection()

Gets a connection from the previously created default pool. Returns a promise.

oracledb.getConnection(callback)

Gets a connection from the previously created default pool. Invokes the callback.

oracledb.getConnection(poolAlias)

Gets a connection from the previously created pool with the specified poolAlias. Returns a promise.

oracledb.getConnection(poolAlias, callback)

Gets a connection from the previously created pool with the specified poolAlias. Invokes the callback.

oracledb.getConnection(connAttrs)

Creates a standalone, non-pooled connection. Returns a promise.

oracledb.getConnection(connAttrs, callback)

Creates a standalone, non-pooled connection. Invokes the callback.

Note if the application opens a number of connections, you should increase the number of threads available to node-oracledb. See Connections and Number of Threads.

From node-oracledb 3.1.0, a non-pooled oracledb.getConnection() call will return a DPI-1047 error if node-oracledb cannot load Oracle Client libraries. Previous versions threw this error from require('oracledb').

See Connection Handling for more information on connections.

The parameters of the oracledb.getConnection() method are:

Table 1.30 oracledb.getConnection() Parameters

Parameter

Data Type

Description

poolAlias

String

Specifies which previously created pool in the connection pool cache to use to obtain the connection.

connAttrs

Object

The connAttrs parameter object provides connection credentials and connection-specific configuration properties.

Any connAttrs properties that are not used by the getConnection() method are ignored.

See getConnection(): connAttrs Parameter Properties for information on the properties of the connAttrs object.

The properties of the connAttrs object are:

Table 1.31 getConnection(): connAttrs Parameter Properties

Property

Data Type

node-oracledb Mode

Description

accessToken

Function, String, or Object

Both

For Microsoft Azure Active Directory OAuth 2.0 token-based authentication, accessToken can be:

  • a callback function returning the token as a string

  • or the token as a string

For OAuth 2.0, tokens can be obtained using various approaches. For example, using the Azure Active Directory API.

For Oracle Cloud Infrastructure Identity and Access Management (IAM) token-based authentication, accessToken can be:

  • an object containing token and privateKey attributes

  • or a callback function returning an object containing token and privateKey attributes

For OCI IAM, the token and privateKey values can be obtained using various approaches. For example the Oracle Cloud Infrastructure Command Line Interface can be used.

The properties of the accessToken object are described in getConnection(): accessToken Object Properties.

If accessToken is a callback function:

function accessToken(boolean refresh, object accessTokenConfig)

When accessToken is a callback function, the returned token is used by node-oracledb for authentication. The refresh parameter is described in getConnection(): refresh Parameter Values. See accessTokenConfig for information on this parameter.

For each connection, the callback is invoked with the refresh parameter set to false. This indicates that the application can provide a token from its own application managed cache, or it can generate a new token if there is no cached value. Node-oracledb checks whether the returned token has expired. If it has expired, then the callback function will be invoked a second time with refresh set to true. In this case, the function must externally acquire a token, optionally add it to the application’s cache, and return the token.

For token-based authentication, the externalAuth connection attribute must be set to true. The user (or username) and password attributes should not be set.

See Token-Based Authentication for more information.

Added in version 5.4: The accessToken property was added to support IAM token-based authentication. For IAM token-based authentication, this property must be an Object. For node-oracledb Thick mode, Oracle Client libraries 19.14 (or later), or 21.5 (or later) must be used for IAM token-based authentication.

Changed in version 5.5: The accessToken property was extended to allow OAuth 2.0 token-based authentication in node-oracledb 5.5. For OAuth 2.0, the property should be a string, or a callback. For node-oracledb Thick mode, Oracle Client libraries 19.15 (or later), or 21.7 (or later) must be used. The callback usage supports both OAuth 2.0 and IAM token-based authentication.

accessTokenConfig

Object

Both

An object containing the Azure-specific or OCI-specific parameters that need to be set when using the Azure Software Development Kit (SDK) or Oracle Cloud Infrastructure (OCI) SDK for token generation. This property should only be specified when the accessToken property is a callback function. For more information on the Azure-specific parameters, see sampleazuretokenauth.js and for the OCI-specific parameters, see sampleocitokenauth.js.

For OAuth2.0 token-based authentication and when using node-oracledb Thick mode, Oracle Client libraries 19.15 (or later), or 21.7 (or later) must be used. For IAM token-based authentication and when using node-oracledb Thick mode, Oracle Client libraries 19.14 (or later), or 21.5 (or later) are required.

Added in version 6.3.

connectString, connectionString

String

Both

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.

The two properties are aliases for each other. Use only one of the properties.

Added in version 2.1: The alias connectionString.

walletPassword

String

Thin

The password to decrypt the Privacy Enhanced Mail (PEM)-encoded private certificate, if it is encrypted.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

walletLocation

String

Thin

The directory where the wallet can be found. In node-oracledb Thin mode, this must be the directory that contains the PEM-encoded wallet file.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

edition

String

Thick

Sets the name used for Edition-Based Redefinition by this connection.

This optional property overrides the oracledb.edition property.

Added in version 2.2.

events

Boolean

Thick

Determines if the standalone connection is created using Oracle Call Interface events mode.

This optional property overrides the oracledb.events property.

Added in version 2.2.

externalAuth

Boolean

Both

If this optional property is set to true in Thick mode, then the connection will be established using External Authentication.

In Thin mode, when token-based authentication is required, this property must be set to true. In all the other cases where this property is set to true, an error is thrown.

This optional property overrides the oracledb.externalAuth property.

The user (or username) and password properties should not be set when externalAuth is true.

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

matchAny

Boolean

Thick

Used in conjunction with tag when getting a connection from a connection pool.

Indicates that the tag in a connection returned from a connection pool may not match the requested tag.

See Connection Tagging and Session State.

Added in version 3.1.

newPassword

String

Both

The new password to use for the database user. When using newPassword, the password property should be set to the current password.

This allows passwords to be changed at the time of connection, in particular it can be used to connect when the old password has expired.

See Changing Passwords and Connecting with an Expired Password.

Added in version 2.2.

poolAlias

String

Both

Specifies which previously created pool in the connection pool cache to obtain the connection from. See Pool Alias.

configDir

String

Thin

The directory in which the Optional Oracle Net Configuration Files are found.

For node-oracledb Thick mode, use the oracledb.initOracleClient() option configDir instead.

Added in version 6.0.

sourceRoute

String

Thin

Enables network routing through multiple protocol addresses. The value of this property can be ON or OFF.

The default value is ON.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sslServerCertDN

String

Thin

The distinguished name (DN) that should be matched with the certificate DN. If not specified, a partial match is performed instead. A partial match matches the hostname that the client connected to against the common name (CN) of the certificate DN or the Subject Alternate Names (SAN) of the certificate.

This value is ignored if the sslServerDNMatch property is not set to the value True.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sslServerDNMatch

Boolean

Thin

Determines whether the server certificate DN should be matched in addition to the regular certificate verification that is performed.

If the sslServerCertDN property is not provided, a partial DN match is performed instead. A partial match matches the hostname that the client connected to against the CN of the certificate DN or the SAN of the certificate.

The default value is True.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sslAllowWeakDNMatch

Boolean

Thin

Enables the connection to use either a weaker or more secure DN matching behavior when the sslServerDNMatch property is set.

If the value is True, then the sslServerDNMatch property uses a weaker DN matching behavior which only checks the server certificate (and not the listener certificate), and allows the service name to be used for partial DN matching. The DN matching for a partial match first matches the host name that the client connected to against the common name (CN) of the database server certificate DN or the Subject Alternate Names (SAN) of the database server certificate. If this fails, then the service name is matched against the CN of the database server certificate DN.

If the value is False, then the sslServerDNMatch property uses a more secure DN matching behavior which checks both the listener and server certificates, and does not allow a service name check for partial DN matching. The DN matching for a partial match matches the host name that the client connected to against the CN of the certificate DN or the SAN of the certificate. The service name is not checked in this case.

The default value is False.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.1.

httpsProxy

String

Thin

The name or IP address of a proxy host to use for tunneling secure connections.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

httpsProxyPort

Number

Thin

The port to be used to communicate with the proxy host.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

debugJdwp

String

Thin

Specifies the host and port of the PL/SQL debugger with the format host=<host>;port=<port>. This allows using the Java Debug Wire Protocol (JDWP) to debug PL/SQL code called by node-oracledb.

The default value is the value of environment variable ORA_DEBUG_JDWP.

For node-oracledb Thick mode, set the ORA_DEBUG_JDWP environment variable with the same syntax instead. See Application Tracing.

Added in version 6.0.

retryCount

Number

Thin

The number of times that a connection attempt should be retried before the attempt is terminated.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

retryDelay

Number

Thin

The number of seconds to wait before making a new connection attempt.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

connectTimeout

Number

Thin

The timeout duration in seconds for an application to establish an Oracle Net connection.

There is no timeout by default.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

transportConnectTimeout

Number

Thin

The maximum number of seconds to wait to establish a connection to the database host.

The default value is 60.0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

expireTime

Number

Thin

The number of minutes between the sending of keepalive probes. If this property is set to a value greater than zero, it enables the keepalive probes.

The default value is 0.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

sdu

Number

Thin

The Oracle Net Session Data Unit (SDU) packet size in bytes. The database server configuration should also set this parameter.

For node-oracledb Thick mode, use an Easy Connect string or a Connect Descriptor string instead.

Added in version 6.0.

connectionIdPrefix

String

Thin

The application specific prefix parameter that is added to the connection identifier.

Added in version 6.0.

password

String

Both

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

privilege

Number

Both

The privilege to use when establishing connection to the database. This optional property should be one of the privileged connection constants. All privileges must be specified individually except for oracledb.SYSPRELIM.

oracledb.SYSPRELIM is specified only for startup and shutdown calls and must be used in combination with SYSDBA (oracledb.SYSDBA | oracledb.SYSPRELIM) or SYSOPER (oracledb.SYOPER | oracledb.SYSPRELIM).

See Privileged Connections for more information.

Added in version 2.1.

Changed in version 6.5.1: The database privilege can be specified for pooled connections.

shardingKey

Array

Thick

Allows a connection to be established directly to a database shard. See Connecting to Sharded Databases.

Array values may be of String type (mapping to VARCHAR2 sharding keys), Number (NUMBER), Date (DATE), or Buffer (RAW). Multiple types may be used in the array. Sharding keys TIMESTAMP type are not supported.

Added in version 4.1.

stmtCacheSize

Number

Both

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

superShardingKey

Array

Thick

Allows a connection to be established directly to a database shard. See Connecting to Sharded Databases.

Array values may be of String type (mapping to VARCHAR2 sharding keys), Number (NUMBER), Date (DATE), or Buffer (RAW). Multiple types may be used in the array. Sharding keys TIMESTAMP type are not supported.

Added in version 4.1.

tag

String

Thick

Used when getting a connection from a connection pool.

Indicates the tag that a connection returned from a connection pool should have. Various heuristics determine the tag that is actually returned, see Connection Tagging and Session State.

Added in version 3.1.

user, username

String

Both

The two properties are aliases for each other. Use only one of the properties.

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

Added in version 5.2: The alias username.

getConnection(): accessToken Object Properties

The properties of the accessToken object are described below.

Table 1.32 getConnection(): accessToken Object Properties

Attribute

Description

token

The database authentication token.

privateKey

The database authentication private key.

getConnection(): refresh Parameter

Table 1.33 getConnection(): refresh Parameter Values

refresh Value

Description

false

The application can return a token from an application-specific cache. If there is no cached token, the application must externally acquire one.

true

The token previously passed to driver is known to be expired, the application should externally acquire a new token.

Callback:

If you are using the callback programming style:

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

See oracledb.getConnection() Parameters for information on the poolAlias and connAttrs parameters.

The parameters of the callback function function(Error error, Connection connection) are:

Callback Function Parameter

Description

Error error

If getConnection() succeeds, error is NULL. If an error occurs, then error contains the error message.

Connection connection

The newly created connection. If getConnection() fails, connection will be NULL. See Connection class for more details.

oracledb.getPool()
getPool([String poolAlias]);

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

The parameters of the oracledb.getPool() method are:

Table 1.34 oracledb.getPool() Parameters

Parameter

Data Type

Description

alias

String

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

oracledb.initOracleClient()

Added in version 5.0.

initOracleClient([Object options]);

From node-oracledb 6.0, this synchronous function enables node-oracledb Thick mode by initializing the Oracle Client library (see Enabling node-oracledb Thick Mode). This method must be called before any standalone connection or pool is created. If a connection or pool is first created in Thin mode, then initOracleClient() will raise an exception and Thick mode will not be enabled. If the first call to initOracleClient() had an incorrect path specified, then a second call with the correct path will work. The initOracleClient() method can be called multiple times in each Node.js process as long as the arguments are the same each time.

In node-oracledb 5.5 and earlier versions, this synchronous function loads and initializes the Oracle Client libraries that are necessary for node-oracledb to communicate with Oracle Database. This function is optional. If used, it should be the first node-oracledb call made by an application. If initOracleClient() is not called, then the Oracle Client libraries are loaded at the time of first use in the application, such as when creating a connection pool. The default values described for options will be used in this case. If the Oracle Client libraries cannot be loaded, or they have already been initialized, either by a previous call to this function or because another function call already required the Oracle Client libraries, then initOracleClient() raises an exception.

See Initializing node-oracledb for more information.

The parameters of the oracledb.initOracleClient() method are:

Table 1.35 oracledb.initOracleClient() Parameters

Parameter

Data Type

Description

options

Object

The options parameter and option attributes are optional. If an attribute is set, it should be a string value. See initOracleClient(): options Parameter Attributes for information on the options attributes.

The properties of the options parameter are:

Table 1.36 initOracleClient(): options Parameter Attributes

Attribute

Description

binaryDir

This directory is added to the start of the default search path used by initOracleClient() to load the node-oracledb Thick mode binary module.

The default search path includes node_modules/oracledb/build/Release and node_modules/oracledb/build/Debug.

Added in version 6.2.

configDir

This specifies the directory in which the Optional Oracle Net Configuration and Optional Oracle Client Configuration files reside.

It is equivalent to setting the Oracle environment variable TNS_ADMIN to this value. Any value in that environment variable prior to the call to oracledb.initOracleClient() is ignored. On Windows, remember to double each backslash used as a directory separator.

If configDir is not set, Oracle’s default configuration file search heuristics are used.

driverName

This specifies the driver name value shown in database views, such as V$SESSION_CONNECT_INFO.

It can be used by applications to identify themselves for tracing and monitoring purposes. The convention is to separate the product name from the product version by a colon and single space characters.

If this attribute is not specified, then the default value in node-oracledb Thick mode is like “node-oracledb thk : version”. See Other Node-oracledb Initialization.

errorUrl

This specifies the URL that is included in the node-oracledb exception message if the Oracle Client libraries cannot be loaded.

This allows applications that use node-oracledb to refer users to application-specific installation instructions.

If this attribute is not specified, then the node-oracledb installation instructions are used. See Other Node-oracledb Initialization.

libDir

This specifies the directory containing the Oracle Client libraries.

If libDir is not specified, the default library search mechanism is used.

If your client libraries are in a full Oracle Client or Oracle Database installation, such as Oracle Database “XE” Express Edition, then you must have previously set environment variables like ORACLE_HOME before calling initOracleClient(). On Windows, remember to double each backslash used as a directory separator. See Locating the Oracle Client Libraries.

On Linux, ensure a libclntsh.so file exists. On macOS ensure a libclntsh.dylib file exists. Node-oracledb will not directly load libclntsh.*.XX.1 files in libDir. Note other libraries used by libclntsh* are also required.

On Linux, using libDir is only useful for forcing initOracleClient() to immediately load the Oracle Client libraries because those libraries still need to be in the operating system search path, such as from running ldconfig or set in the environment variable LD_LIBRARY_PATH.

oracledb.shutdown()

Added in version 5.0.

Promise:

promise = shutdown([Object connAttr [, Number shutdownMode]]);

This is the simplified form of connection.shutdown() used for shutting down a database instance. It accepts connection credentials and shuts the database instance completely down.

Note

This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Internally it creates, and closes, a standalone connection using the oracledb.SYSOPER privilege.

See Database Start Up and Shut Down.

The parameters of the oracledb.shutdown() method are:

Table 1.37 oracledb.shutdown() Parameters

Parameter

Data Type

Description

connAttr

Object

Connection credentials similar to oracledb.getConnection() credentials. The properties user, username password, connectString, connectionString, and externalAuth may be specified.

shutdownMode

Number

oracledb.SHUTDOWN_MODE_ABORT, oracledb.SHUTDOWN_MODE_DEFAULT, oracledb.SHUTDOWN_MODE_IMMEDIATE, oracledb.SHUTDOWN_MODE_TRANSACTIONAL, or oracledb.SHUTDOWN_MODE_TRANSACTIONAL_LOCAL.

The default mode is oracledb.SHUTDOWN_MODE_DEFAULT.

Callback:

If you are using the callback programming style:

shutdown([Object connAttr, [Number shutdownMode, ] ] function(Error error) {});

See oracledb.shutdown() Parameters for information on the parameters.

The parameters of the callback function function(Error error) are:

Callback Function Parameter

Description

Error error

If shutdown() succeeds, error is NULL. If an error occurs, then error contains the error message.

oracledb.startup()

Added in version 5.0.

Promise:

promise = startup([Object connAttrs [, Object options ]]);

This is the simplified form of connection.startup() used for starting a database instance up. It accepts connection credentials and starts the database instance completely.

Note

This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

As part of the start up process, a standalone connection using the oracledb.SYSOPER privilege is internally created and closed.

See Database Start Up and Shut Down.

The parameters of the oracledb.startup() method are:

Table 1.38 oracledb.startup() Parameters

Parameter

Data Type

Description

connAttr

Object

Connection credentials similar to oracledb.getConnection() credentials. The properties username, password, connectString, connectionString, and externalAuth may be specified.

options

Object

The optional options object can contain one or more of the properties listed in startup(): options Parameter Properties.

The properties of the options property are:

Table 1.39 startup(): options Parameter Properties

Attribute

Data Type

Description

force

Boolean

Shuts down a running database using oracledb.SHUTDOWN_MODE_ABORT before restarting the database. The database start up may require instance recovery. The default for force is false.

restrict

Boolean

After the database is started, access is restricted to users who have the CREATE_SESSION and RESTRICTED SESSION privileges. The default is false.

pfile

String

The path and filename for a text file containing Oracle Database initialization parameters. If pfile is not set, then the database server-side parameter file is used.

Callback:

If you are using the callback programming style:

startup([Object connAttrs, [Object options, ] ] function(Error error) {});

See oracledb.startup() Parameters for information on the connAttrs and options parameters.

The parameters of the callback function function(Error error) are:

Callback function parameter

Description

Error error

If startup() succeeds, error is NULL. If an error occurs, then error contains the error message.

1.4. Oracledb Future Object

A special object that contains properties which control the behavior of node-oracledb, allowing use of new features.

Added in version 6.3.

oracledb.future.oldJsonColumnAsObj

This property is a boolean which when set to true while using Oracle Database 12c (or later), fetches VARCHAR2 and LOB columns that were created with the IS JSON constraint in the same way that columns of type JSON are fetched when using Oracle Database 21c (or later). The IS JSON constraint that is specified when creating VARCHAR2 and LOB columns ensures that only JSON data is stored in these columns.

Also, BLOB columns that were created with the IS JSON FORMAT OSON check constraint are fetched in the same way as columns of type JSON when this property is set to true. The node-oracledb Thick mode requires Oracle Client 21c (or later).

The default value is false.

In a future version of node-oracledb, the setting of this attribute will no longer be required since this will be the default behavior.

Added in version 6.3.

Changed in version 6.4: BLOB columns with the IS JSON FORMAT OSON check constraint enabled can now be fetched as JSON type columns when this property is set.

1.5. Oracledb JsonId Class

Objects of this class are returned by SODA in the _id attribute of documents stored in native collections when using Oracle Database 23.4 (or later). The JsonId class is a subclass of Uint8Array and can be instantiated in the same way as an Uint8Array datatype object.

Added in version 6.5.