2. API: Connection Class
A Connection object is obtained by a Pool class
getConnection()
or Oracledb class
getConnection()
call.
The connection is used to access an Oracle database.
2.1. Connection Properties
The properties of a Connection object are listed below.
- connection.action
This write-only property is a string and it identifies the action attribute for end-to-end application tracing.
Displaying a Connection object will show a value of
null
for this attribute. See End-to-end Tracing, Mid-tier Authentication, and Auditing.
- connection.callTimeout
New in version 3.0.
This read/write property is a number which sets the maximum number of milliseconds that each underlying round-trip between node-oracledb and Oracle Database may take on a connection. Each node-oracledb method or operation may make zero or more round-trips. The
callTimeout
value applies to each round-trip individually, not to the sum of all round-trips. Time spent processing in node-oracledb before or after the completion of each round-trip is not counted.The
callTimeout
setting has no effect when using IPC connections, that is, when node-oracledb is running on the same host as the Oracle Database Network listener.See Database Call Timeouts for more information about limiting statement execution time, and also about limiting the time taken to open new connections.
The default is 0, meaning that there is no timeout.
An exception will occur if node-oracledb Thick mode is not using Oracle Client library version 18.1 or later.
- connection.clientId
This write-only property is a string that indicates the client identifier for end-to-end application tracing, use with mid-tier authentication, and with Virtual Private Databases.
Displaying
Connection.clientId
will show a value ofnull
. See End-to-end Tracing, Mid-tier Authentication, and Auditing.Note
This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
- connection.clientInfo
New in version 4.1.
This write-only property is a string that includes the client information for end-to-end application tracing.
Displaying
Connection.clientInfo
will show a value ofnull
. See End-to-end Tracing, Mid-tier Authentication, and Auditing.Note
This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
- connection.currentSchema
New in version 4.0.
This read/write property is a string. After setting
currentSchema
, SQL statements using unqualified references to schema objects will resolve to objects in the specified schema.This setting does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.
The value of
currentSchema
will be empty until it has been explicitly set.This property is an efficient alternative to ALTER SESSION SET CURRENT_SCHEMA.
- connection.dbDomain
New in version 6.3.
This read-only property is a string that specifies the Oracle Database domain name associated with the connection. This property returns the same value as the SQL expression:
SELECT UPPER(VALUE) FROM V$PARAMETER WHERE NAME = 'db_domain';
The above SQL expression returns NULL if the domain name is not specified. The
dbDomain
property returns an empty string in this case.
- connection.dbName
New in version 6.3.
This read-only property is a string that specifies the name of the Oracle Database associated with the connection. This property returns the same value as the SQL expression:
SELECT UPPER(NAME) FROM V$DATABASE;
- connection.dbOp
New in version 4.1.
This write-only property is a string that includes the database operation information for end-to-end application tracing.
Displaying
Connection.dbOp
will show a value ofnull
. See End-to-end Tracing, Mid-tier Authentication, and Auditing.It is available from Oracle 12c onwards.
- connection.ecid
New in version 5.3.
This write-only property is a string that sets the execution context identifier.
The value is available in the
ECID
column of theV$SESSION
view. It is also shown in audit logs.Note
This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
- connection.instanceName
New in version 6.1.
This read-only attribute specifies the Oracle Database instance name associated with the connection. It returns the same value as the SQL expression
sys_context('userenv', 'instance_name')
.
- connection.maxOpenCursors
New in version 6.3.
This read-only property is a number that indicates the maximum number of SQL statements that can be concurrently opened in one connection. This value can be specified in the server parameter file using the open_cursors parameter. This property returns the same value as the SQL expression:
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors';
This property requires Oracle Database 12.1 or later.
- connection.module
This write-only property is a string and it is the module attribute for end-to-end application tracing.
Displaying
Connection.module
will show a value ofnull
. See End-to-end Tracing, Mid-tier Authentication, and Auditing.
- connection.oracleServerVersion
New in version 1.3.
This read-only property gives a numeric representation of the Oracle database 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
Note if you connect to Oracle Database 18, or later, then the version will only be accurate if node-oracledb is also using Oracle Database 18, or later, client libraries. Otherwise it will show the base release such as 1800000000 instead of 1803000000.
- connection.oracleServerVersionString
New in version 2.2.
This read-only property gives a string representation of the Oracle database version which is useful for display.
Note if you connect to Oracle Database 18, or later, then the version will only be accurate if node-oracledb is also using Oracle Database 18, or later, client libraries. Otherwise it will show the base release such as “18.0.0.0.0” instead of “18.3.0.0.0”.
- connection.serviceName
New in version 6.3.
This read-only property is a string that identifies the Oracle Database service name associated with the connection. This property returns the same value as the SQL expression:
SELECT UPPER(SYS_CONTEXT('USERENV', 'SERVICE_NAME')) FROM DUAL;
- connection.stmtCacheSize
This read-only property is a number that identifies the number of statements to be cached in the statement cache of the connection. The default value is the
stmtCacheSize
property in effect in the Pool object when the connection is created in the pool.
- connection.tag
New in version 3.1.
This read/write property is a string. Applications can set the tag property on pooled connections to indicate the ‘session state’ that a connection has. The tag will be retained when the connection is released to the pool. A subsequent
pool.getConnection()
can request a connection that has a given tag. It is up to the application to set any desired session state and setconnection.tag
prior to closing the connection.The tag property is not used for standalone connections.
Note
This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
When node-oracledb Thick mode is using Oracle Client libraries 12.2 or later, the tag must be a multi-property tag with name=value pairs like “k1=v1;k2=v2”.
An empty string represents not having a tag set.
See Connection Tagging and Session State.
Getting the tag
After a
pool.getConnection()
requests a tagged connection:When no sessionCallback is in use, then
connection.tag
will contain the actual tag of the connection.When a Node.js
sessionCallback
function is used, thenconnection.tag
will be set to the value of the connection’s actual tag prior to invoking the callback. The callback can then set connection state and alterconnection.tag
, as desired, before the connection is returned frompool.getConnection()
.When a PL/SQL
sessionCallback
procedure is used, then afterpool.getConnection()
returns,connection.tag
contains a tag with the same property values as the tag that was requested. The properties may be in a different order. IfmatchAnyTag
is true, thenconnection.tag
may contain other properties in addition to the requested properties. Code after eachpool.getConnection()
call mirroring the PL/SQL code may be needed soconnection.tag
can be set to a value representing the session state changed in the PL/SQL procedure.
Setting the tag
A tag can be set anytime prior to closing the connection. If a Node.js
sessionCallback
function is being used, the best practice recommendation is to set the tag in the callback function.To clear a connection’s tag, set
connection.tag = ""
.
- connection.thin
New in version 6.0.
This read-only attribute is a boolean that identifies the node-oracledb mode in which the connection was established. If the value is true, then it indicates that the connection was established in node-oracledb Thin mode. If the value is false, then it indicates that the connection was established in node-oracledb Thick mode.
The default value is true.
- connection.tpcInternalName
New in version 5.3.
This read/write attribute is a string that specifies the internal name that is used by the connection when logging two-phase commit transactions.
Note
This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
- connection.tpcExternalName
New in version 5.3.
This read/write attribute is a string that specifies the external name that is used by the connection when logging two-phase commit transactions.
Note
This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
- connection.transactionInProgress
New in version 6.3.
This read-only property is a boolean that indicates whether a transaction is currently in progress in the connection. If the value is True, then it indicates that the specified connection has an active transaction. If the value is False, then the specified connection does not have an active transaction.
- connection.warning
New in version 6.3.
This read-only property provides an error object that gives information about any database warnings (such as password being in the grace period) that were generated during
connection establishment
(both standalone connections and pooled connections). This attribute is present if a warning is thrown by the database but the operation is otherwise completed successfully. The connection will be usable despite the warning.For standalone connections, the error object returned by
connection.warning
will be present for the lifetime of the connection.For pooled connections, the error object returned by
connection.warning
will be cleared when a connection is released to the pool usingconnection.close()
.In node-oracledb Thick mode, warnings may be generated during pool creation itself. These warnings will be placed on the new connections created by the pool, provided no warnings were generated by the individual connection creations, in which case those connection warnings will be returned.
2.2. Connection Methods
- connection.break()
Promise:
promise = break();
Stops the currently running operation on the connection.
If there is no operation in progress or the operation has completed by the time the break is issued, the
break()
is effectively a no-op.If the running asynchronous operation is interrupted, its callback will return an error.
In network configurations that drop (or in-line) out-of-band breaks,
break()
may hang unless you have DISABLE_OOB=ON in asqlnet.ora
file, see Optional Oracle Net Configuration.Note
Connections can receive out-of-band (OOB) break messages from the Oracle Database only in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
If you use
break()
with DRCP connections, it is currently recommended to drop the connection when releasing it back to the poolawait connection.close({drop: true})
. See Oracle bug 29116892.Callback:
If you are using the callback programming style:
break(function(Error error){});
The parameter of the callback function is:
Callback Function Parameter
Description
Error
error
If
break()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.changePassword()
New in version 2.2.
Promise:
promise = changePassword(String user, String oldPassword, String newPassword);
Changes the password of the specified user.
Only users with the ALTER USER privilege can change passwords of other users.
See Changing Passwords and Connecting with an Expired Password.
The parameters of the
connection.changePassword()
method are: Parameter
Data Type
Description
User
String
The name of the user whose password is to be changed.
oldPassword
String
The current password of the currently connected user.
If
changePassword()
is being used by a DBA to change the password of another user, the value ofoldPassword
is ignored and can be an empty string.newPassword
String
The new password of the user whose password is to be changed.
Callback:
If you are using the callback programming style:
changePassword(String user, String oldPassword, String newPassword, function(Error error){});
See connection.changePassword() Parameters for information on the
user
,oldPassword
, andnewPassword
parameters.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
changePassword()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.close()
New in version 1.9.
Promise:
promise = close([Object options]);
Releases a connection.
Calling
close()
as soon as a connection is no longer required is strongly encouraged for system efficiency. Callingclose()
for pooled connections is required to prevent the pool running out of connections.When a connection is released, any ongoing transaction on the connection is rolled back.
If an error occurs on a pooled connection and that error is known to make the connection unusable, then
close()
will drop that connection from the connection pool so a future pooledgetConnection()
call that grows the pool will create a new, valid connection.This method replaces the obsolete equivalent alias
connection.release()
which will be removed in a future version of node-oracledb.The parameters of the
connection.close()
method are: Parameter
Data Type
Description
options
Object
This parameter only affects pooled connections. The only valid option attribute is drop.
For pooled connections, if drop is false, then the connection is returned to the pool for reuse. If drop is true, the connection will be completely dropped from the connection pool, for example:
await connection.close({drop: true});
The default is false.
Callback:
If you are using the callback programming style:
close([Object options, ] function(Error error){});
See connection.close() Parameters for information on the
options
parameter.The parameter of the callback function
function(Error error)
is:Callback Function Parameter
Description
Error
error
If
close()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.commit()
Promise:
promise = commit();
Commits the current transaction in progress on the connection.
Callback:
If you are using the callback programming style:
commit(function(Error error){});
The parameter of the callback function
function(Error error)
is:Callback Function Parameter
Description
Error
error
If
commit()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.createLob()
Promise:
promise = createLob(Number type);
Creates a Lob as an Oracle temporary LOB. The LOB is initially empty. Data can be streamed to the LOB, which can then be passed into PL/SQL blocks, or inserted into the database.
When no longer required, Lobs created with
createLob()
should be closed withlob.destroy()
because Oracle Database resources are held open if temporary LOBs are not closed.Open temporary LOB usage can be monitored using the view V$TEMPORARY_LOBS.
LOBs created with
createLob()
can be bound for IN, IN OUT and OUT binds.See Working with CLOB, NCLOB and BLOB Data and LOB Bind Parameters for more information.
The parameters of the
connection.createLob()
method are: Parameter
Data Type
Description
type
Number
One of the constants oracledb.CLOB, oracledb.BLOB, or oracledb.NCLOB (or equivalent
DB_TYPE_*
constants).Callback:
If you are using the callback programming style:
createLob(Number type, function(Error error, Lob lob){});
See connection.createLob() Parameters for information on the
type
parameter.The parameter of the callback function
function(Error error)
is:Callback Function Parameter
Description
Error
error
If
createLob()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.decodeOSON()
New in version 6.4.
decodeOSON(Buffer buf);
This synchronous method decodes an OSON Buffer and returns a Javascript value. This method is useful for fetching BLOB columns that have the check constraint
IS JSON FORMAT OSON
enabled.The parameters of the
connection.decodeOSON()
are: Parameter
Data Type
Description
buf
Buffer
The OSON buffer that is to be decoded.
See Using BLOB columns with OSON Storage Format in node-oracledb for an example.
- connection.encodeOSON()
New in version 6.4.
encodeOSON(Any value);
This synchronous method encodes a JavaScript value to OSON bytes and returns a Buffer. This method is useful for inserting OSON bytes directly into BLOB columns that have the check constraint
IS JSON FORMAT OSON
enabled.The parameters of the
connection.encodeOSON()
are: Parameter
Data Type
Description
value
Any
The JavaScript value that is to be encoded into OSON bytes. The JavaScript value can be any value supported by JSON.
See Using BLOB columns with OSON Storage Format in node-oracledb for an example.
- connection.execute()
Promise:
promise = execute(String sql [, Object bindParams [, Object options]]); promise = execute(Object sql [, Object options]);
Executes a single SQL statement, PL/SQL statement, or the SQL statement in the object that was returned by the
sql
function of the third-party sql-template-tag module. See SQL Execution for examples.The statement to be executed may contain IN binds, OUT or IN OUT bind values or variables, which are bound using either an object or an array.
The parameters of the
connection.execute()
method are: Parameter
Data Type
Description
sql
String or Object
This function parameter can either be a string or an object.
If the parameter is a string, then it is the SQL statement that is executed. The statement may contain bind parameters.
If the parameter is an object, then it is the object that is returned from the
sql
function of the third-party sql-template-tag module. This object exposes the SQL statement and values properties to retrieve the SQL string and bind values. See example. If the object returned by thesql
function contains a SQL statement with aRETURNING INTO
clause, thenconnection.execute()
will not work and an error will be thrown.Changed in version 6.4: The ability to accept an object (returned from the
sql
function of the third-partysql-template-tag
module) as an input parameter was added toconnection.execute()
.bindParams
Object or Array
This function parameter is needed if there are bind parameters in the SQL statement. It can be either an object that associates values or JavaScript variables to the statement’s bind variables by name, or an array of values or JavaScript variables that associate to the statement’s bind variables by their relative positions. See Bind Parameters for Prepared Statements for more details on binding.
If a bind value is an object it may have the properties listed in execute(): bindParams Parameter Properties.
options
Object
This is an optional parameter to
execute()
that may be used to control statement execution. See execute(): options Parameter Properties for detailed information on its properties.execute(): bindParams Parameter Properties
The properties of the
bindParams
parameter are: Bind Property
Description
dir
The direction of the bind, indicating whether data is being passed into, or out from, the database. The value can be one of the Execute Bind Direction Constants
oracledb.BIND_IN
,oracledb.BIND_INOUT
, ororacledb.BIND_OUT
. The default isoracledb.BIND_IN
.maxArraySize
The number of array elements to be allocated for a PL/SQL Collection INDEX BY associative array OUT or IN OUT array bind variable. For IN binds, the value of
maxArraySize
is ignored. See PL/SQL Collection Associative Arrays.maxSize
The maximum number of bytes that OUT or IN OUT bind variable values of type String or Buffer can use to get data. The default value is 200. The maximum limit depends on the database type, see below. When binding IN OUT, then
maxSize
refers to the size of the returned value: the input value can be smaller or bigger. For IN binds,maxSize
is ignored.The limit for
maxSize
when binding a value that is returned as a Buffer is 2000 bytes. For Strings, the limit is 4000 bytes unless you are using Oracle Database 12 or later, and the database initialization parameterMAX_STRING_SIZE
has a value ofEXTENDED
. In this case the limit is 32767 bytes.When binding Oracle LOBs as
oracledb.STRING
,oracledb.DB_TYPE_NVARCHAR
ororacledb.BUFFER
, the data cannot be greater than 1 GB. See LOB Bind Parameters. For larger data, use the Lob Class.Similarly, when binding LONG as
oracledb.STRING
and LONG RAW asoracledb.BUFFER
, data cannot be greater than 1 GB.When binding to get a UROWID value from the database, note that UROWIDs can take up to 5267 bytes when fetched from the database so
maxSize
should be set to at least this value.type
The
type
indicates to the database how data should be handled.If
type
is not set for IN or IN OUT binds its value will be derived from the type of the input data. It is recommended to explicitly set the type because null data will be assumed to beoracledb.STRING
. With OUT binds,type
defaults tooracledb.STRING
.Commonly,
type
is set to a node-oracledb Type Constant that matches the JavaScript type. Node-oracledb and the underlying Oracle Client libraries then do a mapping to, or from, the actual database data type. Since Oracle Database does not provide actual database type information prior to binding, some special cases needtype
set explicitly to avoid data conversion issues. For example, binding a String to an NVARCHAR needstype
set tooracledb.DB_TYPE_NVARCHAR
.For each JavaScript and database type combination, the
type
property can be one of the values in the execute(): type Property Values table. For example, if you are inserting data from a String into an Oracle Database CHAR column, then settype
tooracledb.DB_TYPE_CHAR
.This table does not cover implicit data type conversions that will take place in Oracle libraries. In particular many Oracle types will allow JavaScript values to be bound as
oracledb.STRING
. For example, you can bind the string “1234” to insert into a NUMBER column. Another example is that the string “31-01-2019” can be bound for insert into a DATE column (if the NLS_DATE_FORMAT is “DD-MM-YYYY”).Similarly when binding a JavaScript Date,
type
can be set tooracledb.DATE
for all date and timestamp database types. This bind type is the default for Date IN and IN OUT binds. Using the date or timestamp type constant corresponding to the database type may be preferred when binding in node-oracledb 4.2. This reduces type conversions and it may be useful in cases such as when calling overloaded PL/SQL procedures, or to ensure the correct index is used by a query.val
The input value or variable to be used for an IN or IN OUT bind variable.
execute(): Type Property Values
The values of the
type
property are listed in the table below: Node.js Type
Database Type
Bind
type
ValueNotes
String
VARCHAR2
oracledb.STRING
ororacledb.DB_TYPE_VARCHAR
Default
type
for String IN and IN OUT bindsString
CHAR
oracledb.DB_TYPE_CHAR
This combination is supported from node-oracledb 4.2.
String
NVARCHAR
oracledb.DB_TYPE_NVARCHAR
This combination is supported from node-oracledb 4.2.
String
NCHAR
oracledb.DB_TYPE_NCHAR
This combination is supported from node-oracledb 4.2.
String
LONG
oracledb.STRING
ororacledb.DB_TYPE_VARCHAR
Not available for PL/SQL binds.
Number
NUMBER
oracledb.NUMBER
ororacledb.DB_TYPE_NUMBER
Default
type
for Number IN and IN OUT binds.Number
BINARY_DOUBLE
oracledb.DB_TYPE_BINARY_DOUBLE
This combination is supported from node-oracledb 4.2.
Number
BINARY_FLOAT
oracledb.DB_TYPE_BINARY_FLOAT
This combination is supported from node-oracledb 4.2.
Number
BINARY_INTEGER
oracledb.DB_TYPE_BINARY_INTEGER
This combination is supported from node-oracledb 4.2. Only supported for PL/SQL binds.
Date
DATE
oracledb.DB_TYPE_DATE
This combination is supported from node-oracledb 4.2. It is not the default for Date IN and IN OUT binds.
Date
TIMESTAMP
oracledb.DB_TYPE_TIMESTAMP
This combination is supported from node-oracledb 4.2.
Date
TIMESTAMP WITH TIMEZONE
oracledb.DB_TYPE_TIMESTAMP_TZ
This combination is supported from node-oracledb 4.2.
Date
TIMESTAMP WITH LOCAL TIME ZONE
oracledb.DATE
ororacledb.DB_TYPE_TIMESTAMP_LTZ
Default
type
for Date IN and IN OUT binds.Buffer
RAW
oracledb.BUFFER
ororacledb.DB_TYPE_RAW
Default
type
for Buffer IN and IN OUT binds.Buffer
LONG RAW
oracledb.BUFFER
ororacledb.DB_TYPE_RAW
Not available for PL/SQL binds.
Lob
CLOB
oracledb.CLOB
ororacledb.DB_TYPE_CLOB
Default
type
for CLOB Lob IN and IN OUT binds. Binding a String asoracledb.DB_TYPE_VARCHAR
will generally be preferred.Lob
BLOB
oracledb.BLOB
ororacledb.DB_TYPE_BLOB
Default
type
for BLOB Lob IN and IN OUT binds. Binding a Buffer asoracledb.DB_TYPE_RAW
will generally be preferred.Lob
NCLOB
oracledb.NCLOB
ororacledb.DB_TYPE_NCLOB
This combination is supported from node-oracledb 4.2. Binding a String with
type
oforacledb.DB_TYPE_NVARCHAR
will generally be preferred.String
ROWID
oracledb.STRING
ororacledb.DB_TYPE_VARCHAR
String
UROWID
oracledb.STRING
ororacledb.DB_TYPE_VARCHAR
Object
JSON
oracledb.DB_TYPE_JSON
String
XMLType
oracledb.STRING
ororacledb.DB_TYPE_VARCHAR
Size is limited to the maximum database VARCHAR length.
Boolean
BOOLEAN
oracledb.DB_TYPE_BOOLEAN
This combination is supported from node-oracledb 4.2. Only supported for PL/SQL binds.
ResultSet
CURSOR
oracledb.CURSOR
ororacledb.DB_TYPE_CURSOR
Only supported for OUT binds.
DbObject
Named type or collection
A string with the name of the Oracle Database object or collection, or a DbObject.
This combination is supported from node-oracledb 4.0.
When binding LONG, LONG RAW, CLOB, NCLOB, and BLOB database types using string or buffer bind types, then data is limited to a maximum size of 1 GB.
Binding Oracle Database INTERVAL types or BFILE not supported.
execute(): Options Parameter Properties
The properties of the
options
parameter are: Property
Data Type
Description
autoCommit
Boolean
Overrides
oracledb.autoCommit
.dbObjectAsPojo
Boolean
Overrides
oracledb.dbObjectAsPojo
.extendedMetaData
Boolean
Overrides
oracledb.extendedMetaData
.Desupported in version 6.0.
Extended metadata is now always returned.
fetchArraySize
Number
Overrides
oracledb.fetchArraySize
.fetchInfo
Object
Object defining how query column data should be represented in JavaScript. It can be used in conjunction with, or instead of, the global settings
fetchAsString
andfetchAsBuffer
.For example:
fetchInfo: { // return the date as a string "HIRE_DATE": { type: oracledb.STRING }, // override fetchAsString or fetchAsBuffer "HIRE_DETAILS": { type: oracledb.DEFAULT } }
Each column is specified by name, using Oracle’s standard naming convention.
The
type
property can be set to one of:oracledb.STRING for number, date and raw columns in a query to indicate they should be returned as Strings instead of their native format. For CLOB and NCLOB columns, data will be returned as Strings instead of Lob instances. Raw columns returned as strings will be returned as hex-encoded strings. The maximum length of a string created by type mapping number and date columns is 200 bytes. If a database column that is already being fetched as type
oracledb.STRING
is specified infetchInfo
, then the actual database metadata will be used to determine the maximum length.oracledb.BUFFER for a BLOB column, each BLOB item will be returned as a Buffer instead of a Lob instance.
oracledb.DEFAULT overrides any global mapping given by
fetchAsString
orfetchAsBuffer
. The column data is returned in default format for the type.
Strings and Buffers created for LOB columns will generally be limited by Node.js and V8 memory restrictions.
See Query Result Type Mapping for more information on query type mapping.
Deprecated since version 6.0: Use fetchTypeHandler functionality instead.
fetchTypeHandler
Function
Overrides
oracledb.fetchTypeHandler
.New in version 6.0.
keepInStmtCache
Boolean
When
keepInStmtCache
is true, and statement caching is enabled, then the statement will be added to the cache if it is not already present. This helps the performance of re-executed statements. See Statement Caching.The default value is true.
New in version 5.3.
In earlier versions, statements were always added to the statement cache, if caching was enabled.
maxRows
Number
Overrides
oracledb.maxRows
.outFormat
Number
Overrides
oracledb.outFormat
.prefetchRows
Number
Overrides
oracledb.prefetchRows
.This attribute is not used in node-oracledb version 2, 3 or 4.
resultSet
Boolean
Determines whether query results, Implicit Results, and nested cursors should be returned as ResultSet objects or directly.
The default is false.
Callback:
If you are using the callback programming style:
execute(String sql [, Object bindParams [, Object options]], function(Error error, Object result){});
See connection.execute() Parameters for information on the
sql
,bindParams
, andoptions
parameters.The parameters of the callback function
function(Error error, Object result)
are:Callback Function Parameter
Description
Error
error
If
execute()
succeeds, error is NULL. If anerror
occurs, then error contains the error message.Object
result
The result contains any fetched rows, the values of any OUT and IN OUT bind variables, and the number of rows affected by the execution of DML statements.
This parameter can be omitted for DDL and DML statements where the application only checks
error
for success or failure. See execute() callback: result Object Properties for information on its properties.execute() callback: result Object Properties
The properties of
result
object from theexecute()
callback are described below. Property
Description
implicitResults
This property will be defined if the executed statement returned Implicit Results. Depending on the value of resultSet it will either be an array, each element containing an array of rows from one query, or an array of ResultSets each corresponding to a query.
See Implicit Results for examples.
New in version 4.0.
Implicit Results requires Oracle Database 12.1 or later, and Oracle Client 12.1 or later.
lastRowid
This read-only property is a string that identifies the ROWID of a row affected by an INSERT, UPDATE, DELETE, or MERGE statement. For other statements, or if no row was affected, it is not set.
If more than one row was affected, only the ROWID of the last row is returned. To get all ROWIDs of multiple rows see DML RETURNING Bind Parameters.
New in version 4.2.
metaData
This read-only property is an array. For
SELECT
statements, this contains an array of objects describing details of columns for the select list. For non queries, this property is undefined.Each column’s
name
is always given. If the column is a nested cursor, then the column’s object will also contain ametaData
attribute which is an array describing each column in the nested query.Extended metadata is now always returned and includes the following information:
annotations
: The annotations object associated with the fetched column. If the column has no associated annotations, this property value is undefined. Annotations are supported from Oracle Database 23c onwards. If node-oracledb Thick mode is used, Oracle Client 23c is also required.byteSize
: The database byte size. This is only set fororacledb.DB_TYPE_VARCHAR
,oracledb.DB_TYPE_CHAR
andoracledb.DB_TYPE_RAW
column types.dbType
: one of the Oracle Database Type Constant values.dbTypeClass
: The class associated with the database type. This is only set if the database type is an object type.dbTypeName
: The name of the database type, such as “NUMBER” or “VARCHAR2”. For object types, this will be the object name.domainName
: The name of the SQL domain associated with the fetched column. If the column does not have a SQL domain, this property value is undefined. SQL domains are supported from Oracle Database 23c onwards. If node-oracledb Thick mode is used, Oracle Client 23c is also required.domainSchema
: The schema name of the SQL domain associated with the fetched column. If the column does not have a SQL domain, this property value is undefined. SQL domains are supported from Oracle Database 23c onwards. If node-oracledb Thick mode is used, Oracle Client 23c is also required.fetchType
: One of the Node-oracledb Type Constant values.isJson
: Indicates if the column is known to contain JSON data. This will betrue
for JSON columns (from Oracle Database 21c) and for LOB and VARCHAR2 columns where “IS JSON” constraint is enabled (from Oracle Database 19c). This property will befalse
for all the other columns. It will also befalse
for any column when Oracle Client 18c or earlier is used in Thick mode or the Oracle Database version is earlier than 19c.isOson
: Indicates if the column is known to contain binary encoded OSON data. This attribute will betrue
in Thin mode and while using Oracle Client version 21c (or later) in Thick mode when the “IS JSON FORMAT OSON” check constraint is enabled on BLOB and RAW columns. It will be set tofalse
for all other columns. It will also be set tofalse
for any column when the Thick mode uses Oracle Client versions earlier than 21c. Note that the “IS JSON FORMAT OSON” check constraint is available from Oracle Database 19c onwards.name
: The column name follows Oracle’s standard name-casing rules. It will commonly be uppercase, since most applications create tables using unquoted, case-insensitive names.nullable
: Indicates whetherNULL
values are permitted for this column.precision
: Set only fororacledb.DB_TYPE_NUMBER
,oracledb.DB_TYPE_TIMESTAMP
,oracledb.DB_TYPE_TIMESTAMP_TZ
, andoracledb.DB_TYPE_TIMESTAMP_LTZ
columns.scale
: Set only fororacledb.DB_TYPE_NUMBER
columns.
Changed in version 6.4: The
isOson
information attribute was added.Changed in version 6.3: The
annotations
,domainName
,domainSchema
, andisJson
information attributes were added.For numeric columns: when
precision
is0
, then the column is simply a NUMBER. Ifprecision
is nonzero andscale
is-127
, then the column is a FLOAT. Otherwise, it is a NUMBER(precision, scale).Metadata for ResultSets and REF CURSORS is available in a
ResultSet property
. For Lobs, aLob type property
also indicates whether the object is a BLOB or CLOB.To get query metadata without fetching rows, use a ResultSet. Access
resultset.metaData
and then close the ResultSet. Do not callgetRow()
orgetRows()
. Preferably use a query clause such asWHERE 1 = 0
so the database does minimal work.If you wish to change the case of
name
, then use a column alias in your query. For example, the queryselect mycol from mytab
will return thename
as ‘MYCOL’. However, executingselect mycol as "myCol" from mytab
will return the name ‘myCol’.See Query Column Metadata for examples.
outBinds
This array or object property contains the output values of OUT and IN OUT binds.
If bindParams is passed as an array, then
outBinds
is returned as an array. IfbindParams
is passed as an object, thenoutBinds
is returned as an object. If there are no OUT or IN OUT binds, the value is undefined.resultSet
This property is an object. For
SELECT
statements, when the resultSet option is true, use theresultSet
object to fetch rows. See ResultSet Class and Fetching Rows with ResultSets.When using this option,
resultSet.close()
must be called when the ResultSet is no longer needed. This is true whether or not rows have been fetched from the ResultSet.rows
This property is an array. For
SELECT
statements using direct fetches,rows
contains an array of fetched rows. It will be NULL if there is an error or the SQL statement was not a SELECT statement. By default, the rows are in an array of column value arrays, but this can be changed to arrays of objects by settingoracledb.outFormat
tooracledb.OUT_FORMAT_OBJECT
. If a single row is fetched, thenrows
is an array that contains one single row.The number of rows returned is limited by
oracledb.maxRows
or the maxRows option in anexecute()
call. IfmaxRows
is 0, then the number of rows is limited by Node.js memory constraints.If the query contains nested cursors, then each nested cursor is returned as an array of rows fetched from that cursor. The number of rows returned for each cursor is limited by
maxRows
.rowsAffected
This property is a number. For DML statements this contains the number of rows affected, for example the number of rows inserted. For non-DML statements such as queries and PL/SQL statements,
rowsAffected
is undefined.Due to Node.js type limitations, the largest value shown will be 232 - 1, even if more rows were affected. Larger values will wrap.
warning
This property provides an error object that gives information about any database warnings (such as PL/SQL compilation warnings) that were generated during the last call to
connection.execute()
.See PL/SQL Compilation Warnings for more information.
New in version 6.3.
- connection.executeMany()
New in version 2.2.
Promise:
promise = executeMany(String sql, Array binds [, Object options]); promise = executeMany(String sql, Number numIterations [, Object options]);
Allows sets of data values to be bound to one DML or PL/SQL statement for execution. It is like calling
connection.execute()
multiple times but requires fewer round-trips. This is an efficient way to handle batch changes, for example when inserting or updating multiple rows. The method cannot be used for queries.The
executeMany()
method supports IN, IN OUT and OUT binds for most data types except PL/SQL Collection Associative Arrays.The version of this function which accepts a number of iterations should be used when no bind parameters are required or when all bind parameters are OUT binds.
When
executeMany()
is used for PL/SQL code that returns OUT binds it will have the same performance characteristics as repeated calls toexecute()
.See Batch Statement Execution and Bulk Loading for more information.
The parameters of the
connection.executeMany()
method are: Parameter
Data Type
Description
sql
String
The SQL or PL/SQL statement that
executeMany()
executes. The statement should contain bind variable names.binds
Array
The
binds
parameter contains the values or variables to be bound to the executed statement. It must be an array of arrays (for ‘bind by position’) or an array of objects whose keys match the bind variable names in the SQL statement (for ‘bind by name’). Each sub-array or sub-object should contain values for the bind variables used in the SQL statement. At least one such record must be specified.If a record contains fewer values than expected, NULL values will be used. For bind by position, empty values can be specified using syntax like
[a,,c,d]
.By default, the direction of binds is
oracledb.BIND_IN
. The first data record determines the number of bind variables, each bind variable’s data type, and its name (when binding by name). If a variable in the first record contains a null, this value is ignored and a subsequent record is used to determine that variable’s characteristics.If all values in all records for a particular bind variable are null, the type of that bind is
oracledb.STRING
with a maximum size of 1.The maximum sizes of strings and buffers are determined by scanning all records unless a bindDefs property is used. This property explicitly specifies the characteristics of each bind variable.
options
Object
The
options
parameter is optional. It can contain the properties detailed in executeMany(): options Parameter Properties.executeMany(): options Parameter Properties
The properties of the
options
parameter are: Property
Data Type
Description
autoCommit
Boolean
This optional property overrides
oracledb.autoCommit
. Note batchErrors can affect autocommit mode.batchErrors
Boolean
This optional property allows invalid data records to be rejected while still letting valid data be processed. It can only be set to true for INSERT, UPDATE, DELETE or MERGE statements.
When false, the
executeMany()
call will stop when the first error occurs. The callback error object will be set.When
batchErrors
is true, processing will continue even if there are data errors. TheexecuteMany()
callback error parameter is not set. Instead, a property (also calledbatchErrors
) will be returned in the callbackresult
parameter. The property holds an array of Error objects. Each Erroroffset
indicates the row number of a data record that could not be processed. All other valid data records will be processed and a transaction will be started but not committed, even ifautoCommit
is true. The application can examine the errors, take action, and explicitly commit or rollback as desired.In node-oracledb 4.2, the maximum
offset
value was changed from (2^16)-1 to (2^32)-1.Note that some classes of error will always return via the
executeMany()
callback error object, not as batch errors. No transaction is created in this case.The default value is false.
See Handling Data Errors with executeMany() for examples.
bindDefs
Object
The bindDefs object defines the bind variable types, sizes and directions. This object is optional in some cases but it is more efficient to set it.
It should be an array or an object, depending on the structure of the binds parameter.
Each value in the
bindDefs
array or object should be an object containing the keysdir
,maxSize
, andtype
for one bind variable, similar to how execute() bind parameters are identified. See executeMany(): bindDefs Object Properties for information on the bindDefs object property.dmlRowCounts
Boolean
When true, this optional property enables output of the number of rows affected by each input data record. It can only be set true for INSERT, UPDATE, DELETE or MERGE statements.
The default value is false.
This feature works when node-oracledb is using version 12, or later, of the Oracle Client library, and using Oracle Database 12, or later.
keepInStmtCache
Boolean
When
keepInStmtCache
is true, and statement caching is enabled, then the statement will be added to the cache if it is not already present. This helps the performance of re-executed statements. See Statement Caching.The default value is true.
New in version 5.3.
In earlier versions, statements were always added to the statement cache, if caching was enabled.
executeMany(): bindDefs Object Properties
The properties of the
bindDefs
object are: BindDef Property
Description
dir
The direction of the bind. One of the Execute Bind Direction Constants
oracledb.BIND_IN
,oracledb.BIND_INOUT
, ororacledb.BIND_OUT
. The default isoracledb.BIND_IN
.maxSize
Required for Strings and Buffers. Ignored for other types. Specifies the maximum number of bytes allocated when processing each value of this bind variable.
When data is being passed into the database,
maxSize
should be at least the size of the longest value. When data is being returned from the database,maxSize
should be the size of the longest value. IfmaxSize
is too small,executeMany()
will throw an error that is not handled by batchErrors.type
Specifies the mapping between the node-oracledb and database data type. See the
execute()
type table.Callback:
If you are using the continuation passing style:
executeMany(String sql, Array binds [, Object options], function(Error error, Object result) {}); executeMany(String sql, Number numIterations [, Object options], function(Error error, Object result) {});
See executeMany(): options Parameter Properties for information on the
sql
,binds
, andoptions
parameters.The parameters of the callback function
function(Error error, Object result)
are:Callback Function Parameter
Description
Error
error
If
executeMany()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Object
result
The result object may contain the properties detailed in executeMany(): result Object Properties.
executeMany(): result Object Properties
The properties of the
result
object are: Property
Data Type
Description
batchErrors
Array
This property is an array of error objects that were reported during execution. The
offset
property of each error object corresponds to the 0-based index of theexecuteMany()
binds parameter array, indicating which record could not be processed.It will be present only if batchErrors was true in the executeMany() options parameter and there are data errors to report. Some classes of execution error will always return via the
executeMany()
callback error object, not inbatchErrors
.dmlRowCounts
Array
This is an array of integers identifying the number of rows affected by each record of the binds parameter.
It is present only if dmlRowCounts was true in the executeMany() options parameter and a DML statement was executed.
outBinds
Object
This contains the value of any returned IN OUT or OUT binds. It is an array of arrays, or an array of objects, depending on the binds parameters structure. The length of the array will correspond to the length of the array passed as the binds parameter.
It will be present only if there is at least one OUT bind variable identified.
rowsAffected
Number
This is an integer identifying the total number of database rows affected by the processing of all records of the binds parameter.
It is only present if a DML statement was executed.
Due to Node.js type limitations, the largest value shown will be 232 - 1, even if more rows were affected. Larger values will wrap.
warning
Object
This property provides an error object that gives information about any database warnings (such as PL/SQL compilation warnings) that were generated during the last call to
connection.executeMany()
.See PL/SQL Compilation Warnings for more information.
New in version 6.4.
- connection.getDbObjectClass()
New in version 4.0.
Promise:
promise = getDbObjectClass(String className)
Returns a DbObject prototype object representing the named Oracle Database object or collection.
When the definition of a type changes in the database, such as might occur in a development environment, you should fully close connections to clear the object caches used by node-oracledb and the Oracle Client libraries. For example, when using a pool you could use await connection.close({drop: true}), or restart the pool. Then
getDbObjectClass()
can be called again to get the updated type information.See Oracle Database Objects and Collections.
The parameters of the
connection.getDbObjectClass()
method are: Parameter
Data Type
Description
className
String
The name of the Oracle object or collection.
Callback:
If you are using the callback programming style:
getDbObjectClass(String className, function(error, DbObject obj) {})
See connection.getDbObjectClass() Parameters for information on the parameters.
The parameters of the callback function
function(Error error, DbObject obj)
are:Callback Function Parameter
Description
Error
error
If
getDbObjectClass()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.DbObject
obj
A DbObject representing an Oracle Database object or collection.
- connection.getQueue()
Promise:
promise = getQueue(String name [, Object options])
This method returns an AqQueue Class object.
This method returns a queue for enqueuing and dequeuing Oracle Advanced Queuing (AQ) messages.
Note
This method is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
The parameters of the
connection.getQueue()
method are: Parameter
Data Type
Description
name
String
The name of the Advanced Queue to use. This queue should have been created previously, for example with the
DBMS_AQADM.CREATE_QUEUE()
function.If the Advanced Queue does not exist in the database, an error will occur when the queue is attempted to be used.
options
Object
This optional argument can be used to specify the payload type. If the argument is not passed, then the database queue must be a RAW queue. See getQueue(): options Parameter Attributes for information on the attributes.
getQueue(): options Parameter Attributes
The
options
object has the following attributes: Attribute Name
Description
payloadType
For simple string or stream of bytes (RAW) messages, it is not necessary to explicitly specify this attribute. This is the default setting for the payload type. For example:
connection.getQueue(queueName)
will have RAW messages as the default
payloadType
setting.Or you can also explicitly set this attribute to
oracledb.DB_TYPE_RAW
. For example:connection.getQueue(queueName, { payloadType: oracledb.DB_TYPE_RAW })
For JSON messages, set this attribute to
oracledb.DB_TYPE_JSON
. For example:connection.getQueue(queueName, { payloadType: oracledb.DB_TYPE_JSON })
For Database object messages, set this attribute to the name of an Oracle Database object type, or a DbObject Class earlier acquired from
connection.getDbObjectClass()
. If the name of an object type is used, it is recommended that a fully qualified name be used. For example, if the Oracle Database object type name isDEMOQUEUE.USER_ADDRESS_TYPE
:connection.getQueue(queueName, {payloadType: "DEMOQUEUE.USER_ADDRESS_TYPE"});
Changed in version 6.1: Previously, the default value was RAW and you did not have to set this attribute for RAW messages. Also, only the name of an Oracle Database object type, or a DbObject Class could be specified in the this attribute. Now, you can also explicitly specify
oracledb.DB_TYPE_RAW
for RAW messages andoracledb.DB_TYPE_JSON
for JSON messages in this attribute.Callback:
If you are using the continuation passing style:
getQueue(String name, [Object options,] function(Error error, AqQueue queue){})
See connection.getQueue() Parameters for information on the
name
andoptions
parameters.The parameters of the callback function
function(Error error, AqQueue queue)
are:Callback Function Parameter
Description
Error
error
If
queue()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.getSodaDatabase()
New in version 3.0.
getSodaDatabase();
This synchronous method returns a SodaDatabase.
Returns a parent SodaDatabase object for use with Simple Oracle Document Access (SODA).
Note
This method is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
SODA can be used with Oracle Database 18.3 and above, when node-oracledb uses Oracle Client 18.5 or Oracle Client 19.3, or later. The SODA bulk insert methods
sodaCollection.insertMany()
andsodaCollection.insertManyAndGet()
are in Preview status.See Simple Oracle Document Access (SODA) for more information about using SODA in node-oracledb.
- connection.getStatementInfo()
New in version 2.2.
Promise:
promise = getStatementInfo(String sql);
Parses a SQL statement and returns information about it. This is most useful for finding column names of queries, and for finding the names of bind variables used.
This method performs a round-trip to the database, so unnecessary calls should be avoided.
The information is provided by lower level APIs that have some limitations. Some uncommon statements will return the statement type as
oracledb.STMT_TYPE_UNKNOWN
. DDL statements are not parsed, so syntax errors in them will not be reported. The direction and types of bind variables cannot be determined.The statement is always added to the statement cache. This improves performance if
getStatementInfo()
is repeatedly called with the same statement, or if the statement is used in anconnection.execute()
call or similar.The parameters of the
connection.getStatementInfo()
method are: Parameter
Data Type
Description
sql
String
The SQL statement to parse.
Callback:
If you are using the callback programming style:
getStatementInfo(String sql, function(Error error, Object information){});
See connection.getStatementInfo() Parameters for information on the
sql
parameter.The parameters of the callback function
function(Error error, Object information)
are:Callback Function Parameter
Description
Error
error
If
getStatementInfo()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Object
information
Depending on the statement type, the information object may contain:
bindNames
: An array of strings corresponding to the unique names of the bind variables used in the SQL statement.metaData
: Contains properties equivalent to those given byexecute()
metaData. This property exists only for queries.statementType
: An integer corresponding to one of the SQL Statement Type Constants.
- connection.isHealthy()
New in version 5.4.
isHealthy()
This synchronous function returns a boolean indicating the health status of a connection.
Connections may become unusable in several cases, such as if the network socket is broken, if an Oracle error indicates the connection is unusable or after receiving a planned down notification from the database.
This function is best used before starting a new database request on an existing standalone connection. Pooled connections internally perform this check before returning a connection to the application.
If this function returns false, the connection should be closed by the application and a new connection should be established instead.
This function performs a local check. To fully check a connection’s health, use
connection.ping()
which performs a round-trip to the database.
- connection.ping()
New in version 2.2.
Promise:
promise = ping();
Checks that a connection is currently usable and the network to the database is valid. This call can be useful for system health checks. A ping only confirms that a single connection is usable at the time of the ping.
Pinging does not replace error checking during statement execution, since network or database failure may occur in the interval between
ping()
andexecute()
calls.Pinging requires a round-trip to the database. So, unnecessary
ping()
calls should be avoided.If
ping()
returns an error, the application should close the connection.Callback:
If you are using the callback programming style:
ping(function(Error error){});
The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
ping()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.queryStream()
New in version 1.8.
queryStream(String sql [, Object bindParams, [Object options]]);
This synchronous method will return a Readable Stream for queries.
This function provides query streaming support. The parameters are the same as
connection.execute()
except a callback is not used. Instead this function returns a stream used to fetch data.Each row is returned as a
data
event. Query metadata is available via ametadata
event. Theend
event indicates the end of the query results. After theend
event has been received, the Stream destroy() function should be called to clean up resources properly. Any further end-of-fetch logic, in particular the connection release, should be in theclose
event. Alternatively the Stream destroy() method can be used to terminate a stream early.For tuning, adjust the values of the options fetchArraySize and prefetchRows, see Tuning Fetch Performance.
See Query Streaming for more information.
Support for Node.js version 8 Stream
destroy()
method was added in node-oracledb 2.1.See
execute()
.
- connection.rollback()
Promise:
promise = rollback();
Rolls back the current transaction in progress on the connection.
Callback:
If you are using the continuation passing style:
rollback(function(Error error){});
The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
rollback()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.shutdown()
New in version 5.0.
Promise:
promise = shutdown([Number shutdownMode])
Shuts down a database instance. This is the flexible version of
oracledb.shutdown()
, allowing more control over behavior.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
This method must be called twice. The first call blocks new connections. SQL statements such as await
ALTER DATABASE CLOSE NORMAL
andALTER DATABASE DISMOUNT
can then be used to close and unmount the database instance. Alternatively database administration can be performed. Finally, a second callconnection.shutdown(oracledb.SHUTDOWN_MODE_FINAL)
is required to fully close the database instance.If the initial
connection.shutdown()
shutdownMode modeoracledb.SHUTDOWN_MODE_ABORT
is used, thenconnection.shutdown()
does not need to be called a second time.See Database Start Up and Shut Down.
The parameters of the
connection.shutdown()
method are: Parameter
Data Type
Description
shutdownMode
Number
One of the constants oracledb.SHUTDOWN_MODE_ABORT, oracledb.SHUTDOWN_MODE_DEFAULT, oracledb.SHUTDOWN_MODE_FINAL, oracledb.SHUTDOWN_MODE_IMMEDIATE, oracledb.SHUTDOWN_MODE_TRANSACTIONAL, or oracledb.SHUTDOWN_MODE_TRANSACTIONAL_LOCAL.
If
oracledb.SHUTDOWN_MODE_ABORT
is used, thenconnection.shutdown()
does not need to be called a second time.Only the second invocation of
connection.shutdown()
should useoracledb.SHUTDOWN_MODE_FINAL
.Callback:
If you are using the callback programming style:
shutdown([Number shutdownMode,] function(Error error) {});
See connection.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, thenerror
contains the error message.
- connection.subscribe()
New in version 2.3.
Promise:
promise = subscribe(String name, Object options);
Register a JavaScript callback method to be invoked when data is changed in the database by any committed transaction, or when there are Advanced Queuing messages to be dequeued.
For notification to work, the connection must be created with
oracledb.events
mode true.The database must be able to connect to the node-oracledb machine for notifications to be received. Typically this means that the machine running node-oracledb needs a fixed IP address. If there is any problem sending a notification, then the callback method will not be invoked.
The
connection.subscribe()
method may be called multiple times with the samename
, as long as the same connection is used. In this case, the second and subsequent invocations ignore alloptions
properties other than sql and binds. Instead, the new SQL statement is registered to the same subscription, and the same JavaScript notification callback is used. For performance reasons this can be preferable to creating a new subscription for each query.See Continuous Query Notification (CQN) and Advanced Queuing Notifications for more information.
AQ notifications were added in node-oracledb 4.0
The parameters of the
connection.subscribe()
method are: Parameter
Data Type
Description
name
String
For Continuous Query Notification this is an arbitrary name given to the subscription. For Advanced Queuing notifications this must be the queue name.
options
Object
The options that control the subscription. See subscribe(): options Parameter Properties for the properties that can be set.
subscribe(): options Parameter Properties
The properties of the
options
parameter are: Property
Data Type
Description
binds
Array or Object
An array (bind by position) or object (bind by name) containing the bind values to use in the sql property.
callback
function
The notification callback that will be called whenever notifications are sent by the database. It accepts one parameter which contains details of the notification. The syntax of the callback function is:
function callback(Object message)
The
message
parameter contains information about the notification. See message Parameter Properties for information about the properties.clientInitiated
Boolean
This property enables CQN “client initiated” connections which internally use the same approach as normal connections to the database, and do not require the database to be able to connect back to the application. Since client initiated connections do not need additional network configuration, they have ease-of-use and security advantages.
The default is false.
New in version 4.2.
It is available when Oracle Database and the Oracle Client libraries are version 19.4 or higher.
groupingClass
Number
An integer mask which currently, if set, can only contain the value oracledb.SUBSCR_GROUPING_CLASS_TIME. If this value is set then notifications are grouped by time into a single notification.
groupingType
Number
Either oracledb.SUBSCR_GROUPING_TYPE_SUMMARY (the default) indicating notifications should be grouped in a summary, or oracledb.SUBSCR_GROUPING_TYPE_LAST indicating the last notification in the group should be sent.
groupingValue
Number
If
groupingClass
contains oracledb.SUBSCR_GROUPING_CLASS_TIME then groupingValue` can be used to set the number of seconds over which notifications will be grouped together, invokingcallback
once. IfgroupingClass
is not set, thengroupingValue
is ignored.ipAddress
String
A string containing an IPv4 or IPv6 address on which the subscription should listen to receive notifications. If not specified, then the Oracle Client library will select an IP address.
namespace
Number
One of the oracledb.SUBSCR_NAMESPACE_AQ or oracledb.SUBSCR_NAMESPACE_DBCHANGE (the default) constants.
You can use
oracledb.SUBSCR_NAMESPACE_AQ
to get notifications that Advanced Queuing messages are available to be dequeued, see Advanced Queuing Notifications.operations
Number
An integer mask containing one or more of the operation type oracledb.CQN_OPCODE_* constants to indicate what types of database change should generation notifications.
port
Number
The port number on which the subscription should listen to receive notifications. If not specified, then the Oracle Client library will select a port number.
qos
Number
An integer mask containing one or more of the quality of service oracledb.SUBSCR_QOS_* constants.
sql
String
The SQL query string to use for notifications.
timeout
Number
The number of seconds the subscription should remain active. Once this length of time has been reached, the subscription is automatically unregistered and a deregistration notification is sent.
subscribe(): message Parameter Properties
The
message
parameter in the notification callback is an object containing the following properties: Property
Description
dbName
The name of the database which sent a notification. This property is only defined for CQN. It is not defined when
type
is oracledb.SUBSCR_EVENT_TYPE_DEREG.queueName
The name of the Advanced Queue. Undefined for CQN.
New in version 4.0.
queries
An array of objects specifying the queries which were affected by the Query Change notification. This is only defined if the
type
key is the value oracledb.SUBSCR_EVENT_TYPE_QUERY_CHANGE.It contains the
table
key which is an array of objects identical to the objects created for Database Change Notification (see thetables
property below).registered
A boolean indicating whether the subscription is registered with the database. Will be false if
type
is oracledb.SUBSCR_EVENT_TYPE_DEREG or if the subscription was created with the qos property set to oracledb.SUBSCR_QOS_DEREG_NFY.tables
An array of objects specifying the tables which were affected by the notification. This is only defined if
type
is oracledb.SUBSCR_EVENT_TYPE_OBJ_CHANGE. It contains the following properties:name
- The name of the table which was modified in some way.operation
- An integer mask composed of one or more values of the following constants:oracledb.CQN_OPCODE_ALL_ROWS - if row information is not available. This occurs if the qos quality of service flags do not specify the desire for ROWIDs or if grouping has taken place and summary notifications are being sent. This may also be set when too many rows are returned.
oracledb.CQN_OPCODE_ALTER - if the table was altered in the notifying transaction.
oracledb.CQN_OPCODE_DELETE - if the notifying transaction included deletes on the table.
oracledb.CQN_OPCODE_DROP - if the table was dropped in the notifying transaction.
oracledb.CQN_OPCODE_INSERT - if the notifying transaction included inserts on the table.
oracledb.CQN_OPCODE_UPDATE - if the notifying transaction included updates on the table.
rows
- An array of objects specifying the rows which were changed. This will only be defined if the qos quality of service used when creating the subscription indicated the desire for ROWIDs and no summary grouping took place. It contains the following properties:operation: An integer which is one of oracledb.CQN_OPCODE_INSERT, oracledb.CQN_OPCODE_UPDATE, oracledb.CQN_OPCODE_DELETE as described earlier.
rowid: A string containing the ROWID of the row that was affected
txId
A buffer containing the identifier of the CQN transaction which spawned the notification.
type
The type of notification sent. This will be the value of one of the following constants:
oracledb.SUBSCR_EVENT_TYPE_AQ - One or more Advanced Queuing messages are available to be dequeued.
oracledb.SUBSCR_EVENT_TYPE_DEREG - the subscription has been closed or the timeout value has been reached.
oracledb.SUBSCR_EVENT_TYPE_OBJ_CHANGE - object-level notifications are being used (Database Change Notification).
oracledb.SUBSCR_EVENT_TYPE_QUERY_CHANGE - query-level notifications are being used (Continuous Query Notification).
Callback:
If you are using the continuation passing style:
subscribe(String name, Object options, function(Error error, Object result){});
See connection.subscribe() Parameters for information on the
name
andoptions
parameters.The parameters of the callback function
function(Error error, Object result)
are:Callback Function Parameter
Description
Error
error
If
subscribe()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Object
result
For CQN
oracledb.SUBSCR_NAMESPACE_DBCHANGE
subscriptions this contains a single propertyregId
corresponding the value ofREGID
in the database viewUSER_CHANGE_NOTIFICATION_REGS
or the value ofREG_ID
inUSER_SUBSCR_REGISTRATIONS
. For AQoracledb.SUBSCR_NAMESPACE_AQ
subscriptions,regId
is undefined. Due to Node.js type limitations, the largestregId
shown will be 232 - 1. Larger values will wrap.New in version 4.0.
- connection.startup()
New in version 5.0.
Promise:
promise = startup([Object options]);
Starts up a database instance. This is the flexible version of
oracledb.startup()
, allowing more control over behavior.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
The connection must be a standalone connection, not a pooled connection.
This function starts the database in an unmounted state. SQL statements such as
ALTER DATABASE MOUNT
andALTER DATABASE OPEN
can then be executed to completely open the database instance. Database recovery commands could also be executed at this time.The connection used must have the privilege set to oracledb.SYSPRELIM, along with either oracledb.SYSDBA or oracledb.SYSOPER. For example
oracledb.SYSDBA | oracledb.SYSPRELIM
.See Database Start Up and Shut Down.
The parameters of the
connection.startup()
method are: Parameter
Data Type
Description
options
Object
See startup(): options Properties for information on the properties.
The following properties can be set using the connection.startup()
options
parameter: Property
Description
force
Shuts down a running database using oracledb.SHUTDOWN_MODE_ABORT before restarting the database instance. The next database start up may require instance recovery.
The default for
force
is false.pfile
After the database is started, access is restricted to users who have the CREATE_SESSION and RESTRICTED SESSION privileges.
The default is false.
restrict
The path and filename for a local 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 options,] function(Error error) {});
See connection.startup() Parameters for information on the
options
parameter.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, thenerror
contains the error message.
- connection.tpcBegin()
New in version 5.3.
Promise:
promise = tpcBegin(Object xid [, Number flag [, Number transactionTimeout]]);
Explicitly begins a new two-phase commit (TPC) transaction using the specified transaction identifier (XID). The XID is made up of a format identifier, a transaction identifier, and a branch identifier.
Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
The parameters of the
connection.tpcBegin()
method are: Parameter
Data Type
Description
xid
Object
The transaction identifier (XID). It should be an object with the following three attributes:
Number formatId
- the XID format.String | Buffer globalTransactionId
- the global transaction identifier of the XID.String | Buffer branchQualifier
- the branch identifier of the XID.
flag
Number
One of the constants oracledb.TPC_BEGIN_JOIN, oracledb.TPC_BEGIN_NEW, oracledb.TPC_BEGIN_PROMOTE, or oracledb.TPC_BEGIN_RESUME.
The default is
oracledb.TPC_BEGIN_NEW
.The flag
oracledb.TPC_BEGIN_RESUME
can be used to resume a transaction previously suspended byconnection.tpcEnd()
.transactionTimeout
Number
When
flag
isoracledb.TPC_BEGIN_RESUME
ororacledb.TPC_BEGIN_JOIN
, thetransactionTimeout
value is the number of seconds to wait for a transaction to become available.When
flag
isoracledb.TPC_BEGIN_NEW
, thetransactionTimeout
value is the number of seconds the transaction can be inactive before it is automatically terminated by the system. A transaction is inactive between the time it is detached withtpcEnd()
and the time it is resumed withtpcBegin()
.The default value is 60 seconds.
Callback:
If you are using the callback programming style:
tpcBegin(Object xid, [Number flag, [Number transactionTimeout, ]] function(Error error){});
See connection.tpcBegin() Parameters for information on the
xid
,flag
, andtransactionTimeout
parameters.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
tpcBegin()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.tpcCommit()
New in version 5.3.
Promise:
promise = tpcCommit([Object xid,] [Boolean onePhase]);
Commits the transaction previously prepared with
connection.tpcPrepare()
.If
xid
is not passed then theonePhase
parameter value is ignored andtpcCommit()
has the same behavior as a regularconnection.commit()
call.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
Note: When using an external transaction manager with two-phase commits,
autocommitting
should be disabled.The parameters of the
connection.tpcCommit()
method are: Parameter
Data Type
Description
xid
Object
The transaction identifier previously passed to
tpcBegin()
when starting the transaction branch.onePhase
Boolean
If
onePhase
is true, a single-phase commit is performed. The default is false.Callback:
If you are using the callback programming style:
tpcCommit([Object xid,] [Boolean onePhase,] function(Error error){});
See connection.tpcCommit() Parameters for information on the
xid
andonePhase
parameters.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
tpcCommit()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.tpcEnd()
New in version 5.3.
Promise:
promise = tpcEnd([Object xid] [, Number flag]);
Detaches a two-phase commit transaction from the connection when an application needs to end or suspend work on a transaction branch. The transaction becomes inactive at the end of this call but the branch still exists.
If
xid
is not passed, the transaction identifier used by the previousconnection.tpcBegin()
call is used.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
The parameters of the
connection.tpcEnd()
method are: Parameter
Data Type
Description
xid
Object
The transaction identifier previously passed to
tpcBegin()
when starting the transaction branch.flag
Number
One of the constants oracledb.TPC_END_NORMAL or oracledb.TPC_END_SUSPEND.
The default is
oracledb.TPC_END_NORMAL
.If the flag is
oracledb.TPC_END_SUSPEND
then the transaction may be resumed later by callingtpcBegin()
with the flagoracledb.TPC_BEGIN_RESUME
.Callback:
If you are using the callback programming style:
tpcEnd([Object xid,] [Number flag,] function(Error error){});
See connection.changePassword() Parameters for information on the
xid
andflag
parameters.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
tpcEnd()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.tpcForget()
New in version 5.3.
Promise:
promise = tpcForget(Object xid);
Causes the database to forget a heuristically completed two-phase commit transaction.
Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
The parameters of the
connection.tpcForget()
method are: Parameter
Data Type
Description
xid
Object
The transaction identifier previously passed to
tpcBegin()
when starting the transaction branch.Callback:
If you are using the callback programming style:
tpcForget(Object xid, function(Error error){});
See connection.tpcForget() Parameters for information on the
xid
parameter.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
tpcForget()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.tpcPrepare()
New in version 5.3.
Promise:
promise = tpcPrepare([Object xid]);
Prepares a two-phase commit transaction for commit.
Returns a boolean indicating the transaction requires a commit.
Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
After calling this function, no further activity should take place on this connection until either
connection.tpcCommit()
orconnection.tpcRollback()
have been called.If
xid
is not passed, the transaction identifier used by the previousconnection.tpcBegin()
call is used.Example
const commitNeeded = await connection.tpcPrepare(xid);
The parameters of the
connection.tpcPrepare()
method are: Parameter
Data Type
Description
xid
Object
The transaction identifier previously passed to
tpcBegin()
when starting the transaction branch.Callback:
If you are using the callback programming style:
tpcPrepare([Object xid,] function(Error error, Boolean commitNeeded){});
See connection.tpcPrepare() Parameters for information on the
xid
parameter.The parameters of the callback function
function(Error error, Boolean commitNeeded)
are:Callback Function Parameter
Description
Error
error
If
tpcPrepare()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Boolean
commitNeeded
If true, the branch was prepared and needs to be committed. Read-only branches will set this to false as there is no commit needed for the branch.
- connection.tpcRecover()
New in version 5.3.
Promise:
promise = tpcRecover([Boolean asString]);
Returns an array of pending two-phase commit transaction identifiers (XIDs) suitable for use with
connection.tpcCommit()
orconnection.tpcRollback()
.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
This function is a convenience wrapper that queries the view
DBA_PENDING_TRANSACTIONS
. It requires SELECT privilege on that view.The parameters of the
connection.tpcRecover
method are: Parameter
Data Type
Description
asString
Boolean
If
asString
is true, then theglobalTransactionId
andbranchQualifier
attributes will be converted to Strings. Otherwise the values are returned as Buffers.The default value for
asString
is true.Callback:
If you are using the callback programming style:
tpcRecover([Boolean asString,] function(Error error));
See connection.tpcRecover() Parameters for information on the
asString
parameter.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
tpcRecover()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.tpcRollback()
New in version 5.3.
Promise:
promise = tpcRollback([Object xid]);
Rolls back the specified transaction.
If
xid
is not passed, the transaction associated with the connection is rolled back making it equivalent toconnection.rollback()
.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
The parameters of the
connection.tpcRollback
method are: Parameter
Data Type
Description
xid
Object
The transaction identifier previously passed to
tpcBegin()
when starting the transaction branch.Callback:
If you are using the callback programming style:
tpcRollback([Object xid,] function(Error error);
See connection.tpcRollback() Parameters for information on the
xid
parameter.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
tpcRollback()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.
- connection.unsubscribe()
New in version 2.3.
Promise:
promise = unsubscribe(String name);
Unregisters a Continuous Query Notification (CQN) and Advanced Queuing Notification subscription previously created with
connection.subscribe()
. No further notifications will be sent. The notification callback does not receive a notification of the deregistration event.Note
This method is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
A subscription can be unregistered using a different connection to the initial subscription, as long as the credentials are the same.
If the subscription timeout was reached and the subscription was automatically unregistered, you will get an error if you call
connection.unsubscribe()
.The parameters of the
connection.unsubscribe
method are: Parameter
Data Type
Description
name
String
The name of the subscription used in
connection.subscribe()
.Callback:
If you are using the callback programming style:
unsubscribe(String name, function(Error error){});
See connection.unsubscribe() Parameters for information on the
name
parameter.The parameters of the callback function
function(Error error)
are:Callback Function Parameter
Description
Error
error
If
unsubscribe()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.