Oracledb Class — node-oracledb 6.8.0 documentation (original) (raw)

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

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 inmetaData, DbObjecttypes, and in the lob type property. Some database type objects can also be used for:

• the execute() bindParamstype and theexecuteMany() bindDefs typeproperties
• the createLob() type parameter
• fetchAsBuffer, fetchAsString,fetchInfo, and fetchTypeHandler

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_VARCHARinstead of result.metadata[0].fetchType == 2001.

Table 1.2 Oracle Database Type Objects

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

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

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

1.1.6. SQL Statement Type Constants

Constants for connection.getStatementInfo() properties.

Table 1.6 SQL Statement Type Constants

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 for the CQN groupingClass.

Table 1.8 Subscription Constant for the CQN groupingClass Property

Constants for the CQN groupingType.

Table 1.9 Subscription Constants for the CQN groupingType Property

Constants for the CQN qos Quality of Service.

Table 1.10 Subscription Constants for the CQN qos Property

Constants for the CQN namespace.

Table 1.11 Subscription Constants for the CQN namespace Property

1.1.8. Advanced Queuing Constants

Refer to Advanced Queuing documentationfor more details about attributes.

Constants for AqDeqOptions Class mode.

Table 1.12 Constants for the AqDeqOptions Class mode Property

Constants for AqDeqOptions Class navigation.

Table 1.13 Constants for the AqDeqOptions Class navigation Property

Constants for AqDeqOptions Class wait.

Table 1.14 Constants for the AqDeqOptions Class wait Property

Constants for AqEnqOptions Class deliveryMode.

Table 1.15 Constants for the AqDeqOptions Class deliveryMode Property

Constants for AqMessage Class state.

Table 1.16 Constants for the AqMessage Class state Property

Constants for AqEnqOptions Class andAqDeqOptions Class visibility.

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

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:

• operations property
• operation property in themessage parameter

Table 1.18 Constants for the connection.subscribe() optionoperations and notification message operation Properties.

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

1.1.11. Simple Oracle Document Access (SODA) Constants

Table 1.20 SODA Constant

1.1.12. Database Shutdown Constants

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

Added in version 5.0.

Table 1.21 Database Shutdown Constants

1.1.13. Two-Phase Commit Constants

Added in version 5.3.

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

Table 1.22 Two-Phase Commit Constants

1.1.14. Vector Type Constants

Added in version 6.5.

Constants for the vectorFormat attribute.

Table 1.23 Vector Type Constants

Changed in version 6.6: The oracledb.VECTOR_FORMAT_BINARY constant was added.

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() orgetConnection() 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 calledisAutoCommit.

Example

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

oracledb.configProviderCacheTimeout

This property is the number of seconds that node-oracledb keeps the configuration information retrieved from acentralized configuration provider cached.

The default value is 86400 seconds.

Example

const oracledb = require('oracledb'); oracledb.configProviderCacheTimeout = 6;

oracledb.connectionClass

The user-chosen Connection class value is a string which defines a logical name for connections. Most single purpose applications should setconnectionClass 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 Lobinstances and will not be String or Buffer, regardless of anyfetchAsString, 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.dbObjectTypeHandler

Added in version 6.8.

This property is a function which converts the data type of theDbObject property to the desired data type. This function is called once for each property inside a DbObject with a single object argument containing the following attributes:

• type: The value of one of the Oracle Database Type Objects.
• maxSize: The maximum number of bytes allocated.
• typeName: The name of the object.
• precision: Set only for oracledb.DB_TYPE_NUMBER type.
• scale: Set only for oracledb.DB_TYPE_NUMBER type.

The function is expected to return an object containing aconverter attribute which works similar to the existing fetch type handler’s converters. The DbObject type handler’sconverter attribute is a function which accepts the incoming read value of the DbObject property and returns a transformed value (based on the required data type) for the same DbObject property.

This property is not applicable for LOB data type attributes of a DbObject type.

See Using DbObject Type Handlers.

Example

const oracledb = require('oracledb'); const myDbObjectFetchTypeHandler = function(metadata) { if(metadata.type == oracledb.DB_TYPE_NUMBER) { return { converter: (val) => { // The default string value received is converted to new types // like BigInt return BigInt(val); } }; } } oracledb.dbObjectTypeHandler = myDbObjectFetchTypeHandler;

oracledb.driverName

Added in version 6.7.

This property is a string that specifies the name of the driver used by the client to connect to Oracle Database. This is equivalent to the value in the CLIENT_DRIVER column of the V$SESSION_CONNECT_INFO view.

This property may be overridden when creating astandalone connection or aconnection pool.

Note

This property can only be used in the node-oracledb Thin mode.

Example

const oracledb = require('oracledb'); oracledb.driverName = 'mydriver';

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.

Example

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

Changed in version 6.8: Support for this property was added in node-oracledb Thin mode.

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

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 theoracledb.createPool() call and when getting a standalone connection fromoracledb.getConnection().

Events mode is required forContinuous Query Notification,Fast Application Notification (FAN) andRuntime Load Balancing (RLB).

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

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.metaDataobjects 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 orexternal authentication using TLS 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 theoracledb.createPool() call and when getting a standalone connection from oracledb.getConnection().

Note prior to node-oracledb 0.5 this property was calledisExternalAuth.

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 forresultset.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 offetchArraySize 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() optionresultSet: false), the internal buffer size will be based on the lesser of oracledb.maxRows andfetchArraySize.

This property can be overridden by the execute() optionfetchArraySize.

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 equivalentoracledb.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 Lobinstance. Individual query columns in execute() orqueryStream() calls can override thefetchAsBuffer global setting by usingfetchInfo.

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). ThefetchAsString 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, andoracledb.NCLOB. The equivalentDB_TYPE_* constants can also be used.

When any column having one of the types is queried withexecute() 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 thefetchAsString global setting by usingfetchInfo.

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 dbTypeis oracledb.DB_TYPE_VARCHAR, oracledb.DB_TYPE_CHAR, ororacledb.DB_TYPE_RAW.
• dbType: The database type, that is, one of theOracle 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 data use case domain.
• domainSchema: The schema name of the data use case 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 isoracledb.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 isJsoninformation 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.machine

Added in version 6.7.

This property is a string that specifies the name of the host machine where the connection originates. This is equivalent to the value in theMACHINE column of the V$SESSION view.

This property may be overridden when creating astandalone connection or aconnection pool.

Note

This property can only be used in the node-oracledb Thin mode.

Example

const oracledb = require('oracledb'); oracledb.machine = 'mymachine';

oracledb.maxRows

This property is the maximum number of rows that are fetched by a query with connection.execute() when not using aResultSet. 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. ThemaxRows 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 setmaxRows 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 ResultSetor 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 fromrequire('oracledb').

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.oracleClientVersionStringwill throw a DPI-1047 error if node-oracledb cannot load Oracle Client libraries. Previous versions threw this error fromrequire('oracledb').

Example

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

oracledb.osUser

Added in version 6.7.

This property is a string that specifies the name of the operating system user that initiates the database connection. This is equivalent to the value in the OSUSER column of the V$SESSION view.

This property may be overridden when creating astandalone connection or aconnection pool.

Note

This method is only supported in node-oracledb Thin mode.

Example

const oracledb = require('oracledb'); oracledb.osUser = 'myuser';

oracledb.outFormat

This property is a number that identifies the format of query rows fetched when using connection.execute() orconnection.queryStream(). It affects bothResultSet 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_ARRAYor oracledb.OUT_FORMAT_OBJECT. The default value isoracledb.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 inoracledb.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 homogeneouspools (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 whencreating 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 whencreating 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 whencreating 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 Oracle Globally Distributed Databasefor more information.

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, seeoracledb.poolTimeout.

The default value is 0.

This property may be overridden whencreating 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, withhomogeneous 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 ofpoolIncrement. 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 bygetConnection(). 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

This property may be overridden when creating a connection pool usingoracledb.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(). Ifping() 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 poolgetConnection() call differs based on the value specified in thepoolPingTimeout property as detailed below.

Table 1.25 poolPingTimeout Values

This property may be overridden whencreating 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 withconnection.close(), and the connections are then unused for more than poolTimeout seconds, then any excess connections abovepoolMin 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 withpool.reconfigure(), then the initialpoolTimeout used by oracledb.createPool() must be non-zero.

The default value is 60.

This property may be overridden whencreating 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 withoracledb.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.program

Added in version 6.7.

This property is a string that specifies the name of the program connecting to the database. This is equivalent to the value in thePROGRAM column of the V$SESSION view.

This property may be overridden when creating astandalone connection or aconnection pool.

Note

This method is only supported in node-oracledb Thin mode.

Example

const oracledb = require('oracledb'); oracledb.program = 'myprogram';

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-oracledbfor 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.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 beenqueued waiting for an available connection reachesqueueMax, 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 whencreating 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. IfqueueTimeout is 0, then queued connection requests are never terminated.

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

The default value is 60000.

This property may be overridden whencreating 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 thestatementcache 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.terminal

Added in version 6.7.

This property is a string that specifies the name of the terminal from where the connection originates. This is equivalent to the value in theTERMINAL column of the V$SESSION view.

This property may be overridden when creating astandalone connection or aconnection pool.

Note

This method is only supported in node-oracledb Thin mode.

Example

const oracledb = require('oracledb'); oracledb.terminal = 'myterminal';

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 thatnode-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. Iforacledb.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 tooracledb.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 anOracle 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 callingcreatePool(). This allows pools to later be accessed by name, removing the need to pass the pool object through code. SeeConnection 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 fromrequire('oracledb').

See Connection Pooling for more information about pooling.

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

Table 1.26 oracledb.createPool() Parameters

The properties of poolAttrs are:

Table 1.27 createPool(): poolAttrs Parameter Properties

createPool(): accessToken Object Properties

The properties of the accessToken object are:

Table 1.28 createPool(): accessToken Object Attributes

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

createPool(): tokenAuthConfigOci Object Properties

The properties of the tokenAuthConfigOci object are:

Table 1.30 createPool(): tokenAuthConfigOci Object Properties

createPool(): tokenAuthConfigAzure Object Properties

The properties of the tokenAuthConfigAzure object are:

Table 1.31 createPool(): tokenAuthConfigAzure Object Properties

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

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

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.

Note if the application opens a number of connections, you should increase the number of threads available to node-oracledb. SeeConnections 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 fromrequire('oracledb').

See Connection Handling for more information on connections.

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

Table 1.32 oracledb.getConnection() Parameters

The properties of the connAttrs object are:

Table 1.33 getConnection(): connAttrs Parameter Properties

getConnection(): accessToken Object Properties

The properties of the accessToken object are described below.

Table 1.34 getConnection(): accessToken Object Properties

getConnection(): refresh Parameter

Table 1.35 getConnection(): refresh Parameter Values

getConnection(): tokenAuthConfigOci Object Properties

The properties of the tokenAuthConfigOci object are:

Table 1.36 getConnection(): tokenAuthConfigOci Properties

getConnection(): tokenAuthConfigAzure Object Properties

The properties of the tokenAuthConfigAzure object are:

Table 1.37 getConnection(): tokenAuthConfigAzure Object Properties

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 poolAliasand connAttrs parameters.

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

oracledb.getNetworkServiceNames()

Added in version 6.7.

promise = getNetworkServiceNames(String configDir);

Returns a list of TNS Aliases, also known as Network Service Names, defined in the tnsnames.ora file which is inside the directory that is specified in the configDir property or theTNS_ADMIN environment variable if configDir is not specified. If atnsnames.ora file does not exist, then an exception is raised.

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

Table 1.38 oracledb.getNetworkServiceNames() Parameters

See TNS Aliases for Connection Strings.

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.39 oracledb.getPool() Parameters

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 (seeEnabling 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 toinitOracleClient() 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 librariesthat 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.40 oracledb.initOracleClient() Parameters

The properties of the options parameter are:

Table 1.41 initOracleClient(): options Parameter Attributes

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

On Linux, using libDir is only useful for forcinginitOracleClient() 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.registerProcessConfigurationHook()

Added in version 6.8.

registerProcessConfigurationHook(Function fn)

Registers extension modules. These registered modules will be called and executed during standalone and pool connection creation.

The parameters of the registerProcessConfigurationHook() method are:

Table 1.42 oracledb.registerProcessConfigurationHook() Parameters

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.

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

See Database Start Up and Shut Down.

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

Table 1.43 oracledb.shutdown() Parameters

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:

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.

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

See Database Start Up and Shut Down.

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

Table 1.44 oracledb.startup() Parameters

The properties of the options property are:

Table 1.45 startup(): options Parameter Properties

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

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

1.4. Oracledb SparseVector Class

Added in version 6.8.

A SparseVector Class stores information about a sparse vector. This class represents an object that accepts one of the following types in its constructor: typed array, JavaScript array, object, or string. SeeUsing SPARSE Vectors for more information.

1.4.1. SparseVector Properties

SparseVector.indices

This property is a JavaScript array or a 32-bit unsigned integer (Uint32Array) TypedArray that specifies the indices (zero-based) of non-zero values in the vector.

SparseVector.numDimensions

This property is an integer that specifies the number of dimensions of the vector.

SparseVector.values

This property is a JavaScript array or TypedArray that specifies the non-zero values stored in the vector.

1.4.2. SparseVector Methods

SparseVector.dense()

Converts a sparse vector to a dense vector and returns a TypedArray of 8-bit signed integers, 32-bit floating-point numbers, or 64-bit floating-point numbers depending on the storage format of the sparse vector column’s non-zero values in Oracle Database.

This method is best used with sparse vectors read from Oracle Database.

1.5. 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 thatcolumns 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 OSONcheck constraint are fetched in the same way ascolumns 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.6. Oracledb IntervalYM Class

Objects of this class are returned for columns of type INTERVAL YEAR TO MONTH and can be passed to variables of type oracledb.DB_TYPE_INTERVAL_YM The class contains two optional integer attributes,years and months. These attributes can be set by a passed-in JavaScript object containing these attributes.

If no JavaScript object is passed in or if these attributes are not defined in the passed-in JavaScript object, they are set to 0 by default.

If these attribute values are not integers, then the NJS-007 error is thrown when the object is being created.

Added in version 6.8.

1.7. Oracledb IntervalDS Class

Objects of this class are returned for columns of type INTERVAL DAY TO SECOND and can be passed to variables of type oracledb.DB_TYPE_INTERVAL_DS The class contains five optional integer attributes, days, hours, minutes, seconds, and fseconds(fractional seconds denoted in nanoseconds). These attributes can be set by a passed-in JavaScript object containing these attributes.

If no JavaScript object is passed in or if these attributes are not defined in the passed-in JavaScript object, they are set to 0 by default.

If these attribute values are not integers, then the NJS-007 error is thrown when the object is being created.

Added in version 6.8.

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

1.9. Oracledb TraceHandler Interface

The TraceHandlerBase class acts as an interface which provides abstract methods that can be implemented by derived classes. The implemented methods can use the traceContext in/out parameter with OpenTelemetry.

Added in version 6.7.

1.9.1. TraceHandlerBase Class

1.9.1.1. TraceHandlerBase Methods

traceHandlerBase.disable()

Disables invoking the Abstract methods with traceContext data. Note that this is a synchronous method.

traceHandlerBase.enable()

Enables invoking the Abstract methods with traceContext data. Note that this is a synchronous method.

traceHandlerBase.isEnabled()

Determines if the abstract methods will be invoked.

onEnterFn()

onEnterFn([Object traceContext]);

This method is invoked before a public method passes the traceContext. Note that this is a synchronous method.

Table 1.46 onEnterFn() Parameters

onExitFn()

onExitFn(Object traceContext);

This method is invoked after a public method completes passing the traceContext. Note that this is a synchronous method.

Table 1.47 onExitFn() Parameters

onBeginRoundTrip()

onBeginRoundTrip([Object traceContext]);

Called when a round trip begins. OpenTelemetry will start a new span as a child of the public API span.

Table 1.48 onBeginRoundTrip() Parameters

onEndRoundTrip()

onEndRoundTrip([Object traceContext]);

Called when a round trip ends. OpenTelemetry will end the span. The traceContext object passed in onBeginRoundTrip() is also passed in this method.

Table 1.49 onEndRoundTrip() Parameters

1.9.2. TraceHandler Methods

oracledb.traceHandler.getTraceInstance()

Returns the user-defined instance implementing the TraceHandlerBase class. Note that this is a synchronous method.

oracledb.traceHandler.isEnabled()

Checks if an instance implementing theTraceHandlerBase class is registered and if the abstract methods will be invoked. Note that this is a synchronous method.

oracledb.traceHandler.setTraceInstance()

setTraceInstance([Object obj]);

Sets the user-defined implementation of the TraceHandlerBase class. Note that this is a synchronous method.

The parameters of the oracledb.traceHandler.setTraceInstance() method are:

Table 1.50 oracledb.traceHandler.setTraceInstance() Parameters