4. Connecting to Oracle Database

4.2.1. Easy Connect Syntax for Connection Strings

An Easy Connect string is often the simplest connection string to use when creating connections and pools. For example, to connect to the Oracle Database service orclpdb1 that is running on the host mydbmachine.example.com with the default Oracle Database port 1521, use:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com/orclpdb1"
});

If the database is using a non-default port, for example 1984, the port must be given:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com:1984/orclpdb1"
});

The Easy Connect syntax has been extended in recent versions of Oracle Database client since its introduction in Oracle 10g. Check the Easy Connect Naming method in Oracle Net Service Administrator’s Guide for the syntax in your version of the Oracle Client libraries. The Easy Connect syntax supports Oracle Database service names. It cannot be used with the older System Identifiers (SID).

In node-oracledb Thin mode, any unknown Easy Connect options are ignored and are not passed to the database. See Connection String Differences for more information.

If you are using node-oracledb Thick mode with Oracle Client 19c (or later), the latest Easy Connect syntax allows the use of multiple hosts or ports, along with optional entries for the wallet location, the distinguished name of the database server, and even lets some network configuration options be set. Oracle’s Technical Paper on Easy Connect Plus Syntax discusses the syntax. The Easy Connect syntax means that tnsnames.ora or sqlnet.ora files are not needed for some further common connection scenarios.

For example, if a firewall terminates idle connections every five minutes, you may decide it is more efficient to keep connections alive instead of having the overhead of recreation. Your connection string could be "mydbmachine.example.com/orclpdb1?expire_time=2" to send packets every two minutes with the EXPIRE_TIME feature. The general recommendation for EXPIRE_TIME is to use a value that is slightly less than half of the termination period.

Another common use case for Easy Connect is to limit the amount of time required to open a connection. For example, to return an error after 15 seconds if a connection cannot be established to the database, use "mydbmachine.example.com/orclpdb1?connect_timeout=15".

4.2.2. Connect Descriptors

Connect Descriptors can be embedded directly in node-oracledb applications. For example:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"
});

4.2.3. TNS Aliases for Connection Strings

Connect Descriptors are commonly stored in optional tnsnames.ora configuration files and associated with a TNS Alias. This alias can be used directly in the connectString parameter of oracledb.getConnection() and oracledb.createPool(). For example, given a file /opt/oracle/config/tnsnames.ora with the following content:

sales =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

You can connect in node-oracledb Thin mode by passing the TNS Alias “sales” (case insensitive) as the connectString value using the following code:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "sales"
    configDir     : "/opt/oracle/config"
});

See Optional Oracle Net Configuration for more options on how node-oracledb locates the tnsnames.ora files. Note that in node-oracledb Thick mode, the configuration file must be in a default location or be set during initialization, not at connection time.

TNS Aliases may also be defined in a directory server.

For general information on tnsnames.ora files, see the Oracle Net documentation on tnsnames.ora.

You can retrieve the TNS Aliases that are defined in the tnsnames.ora file using oracledb.getNetworkServiceNames(). The directory that contains the tnsnames.ora file can be specified in the configDir property of getNetworkServiceNames(). For example, if the tnsnames.ora file is stored in the /opt/oracle/config directory and contains the following TNS Aliases:

sales =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

finance =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbmachine.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

To retrieve the TNS Aliases from the above tnsnames.ora file, you can use:

const serviceNames = await oracledb.getNetworkServiceNames("/opt/oracle/config");
console.log(serviceNames);

This prints ['sales', 'finance'] as the output.

4.2.4. Centralized Configuration Provider URL Connection Strings

A Centralized Configuration Provider URL connection string allows node-oracledb configuration information to be stored centrally in OCI Object Storage, OCI Vault, local file, Azure App Configuration, or in a Azure Key Vault. Using a provider URL, node-oracledb will access the information stored in the configuration provider and use it to connect to Oracle Database.

The database connect descriptor and any database credentials stored in a configuration provider will be used by any language driver that accesses the configuration. Other driver-specific sections can exist. Node-oracledb will use the settings that are in a section with the prefix “njs”, and will ignore other sections.

The Centralized Configuration Provider URL must begin with “config-<configuration-provider>://” where the configuration-provider value can be set to ociobject, ocivault, file, azure, or azurevault depending on the location of your configuration information.

For example, consider the following connection configuration stored in OCI Object Storage:

{
  "connect_descriptor": "localhost/orclpdb",
  "user": "scott",
  "njs": {
    "poolMin": 2,
    "poolMax": 10,
    "prefetchRows": 2
    "stmtCacheSize": 30
  }
}

You could use this to create a connection pool by specifying the connectString connection string parameter as shown below:

const pool = await oracledb.createPool({
  connectString : "config-ociobject://abc.oraclecloud.com/n/abcnamespace/b/abcbucket/o/abcobject?oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"
});

const connection = await pool.getConnection();

The pool will be created using the pool properties defined in the configuration provider.

See Centralized Configuration Providers for more information.

4.2.5. JDBC and Oracle SQL Developer Connection Strings

The node-oracledb connection string syntax is different from Java JDBC and the common Oracle SQL Developer syntax. If these JDBC connection strings reference a service name like:

jdbc:oracle:thin:@hostname:port/service_name

for example:

jdbc:oracle:thin:@mydbmachine.example.com:1521/orclpdb1

then use Oracle’s Easy Connect syntax in node-oracledb:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com:1521/orclpdb1"
});

You may need to remove JDBC-specific parameters from the connection string and use node-oracledb alternatives.

Alternatively, if a JDBC connection string uses an old-style Oracle system identifier SID, and there is no service name available:

jdbc:oracle:thin:@hostname:port:sid

for example:

jdbc:oracle:thin:@mydbmachine.example.com:1521:orcl

then either embed the Connect Descriptor:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)))"
});

or create a Net Service Name:

# tnsnames.ora

finance =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbmachine.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

This can be referenced in node-oracledb:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "finance"
});

4.4.1. Using a File Centralized Configuration Provider

The File Centralized Configuration Provider enables the storage and management of Oracle Database connection information using local files. This configuration provider support is available from node-oracledb 6.9 onwards.

To use a File Centralized Configuration Provider, you must:

1. Store the connection information in a JSON file on your local file system. See Connection Information for File Configuration Provider.

2. Use a File configuration provider connection string URL in the connectString property of connection and pool creation methods.

Note that node-oracledb caches configurations by default, see Caching Configuration Information.

Connection Information for File Configuration Provider

The connection information stored in a JSON file must contain at least a connect_descriptor key to specify the database connection string. Optionally, you can store the database user name, password, wallet location, and node-oracledb properties. For details on the information that can be stored in this configuration provider, see Configuration Information Stored in Configuration Providers.

An example of a JSON file that can be used with File Centralized Configuration Provider is:

{
    "connect_descriptor": "(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
            (host=adb.region.oraclecloud.com))(connect_data=(service_name=dbsvcname))
            (security=(ssl_server_dn_match=yes)))",

    "user": "scott",
    "password": {
      "type": "base64",
      "value": "dGlnZXI="
    }
    "njs": {
        "stmtCacheSize": 30,
        "prefetchRows": 2,
        "poolMin": 2,
        "poolMax": 10
    }
}

This encodes the password as base64. See Using an OCI Object Storage Centralized Configuration Provider for other password examples.

File Centralized Configuration Provider connectString Syntax

The connectString parameter for oracledb.getConnection() and oracledb.createPool() calls should use a connection string URL in the format:

config-file://<filePath>?[alias=]

For example, if you have the above JSON file stored in /opt/oracle/my-config1.json, you can connect to Oracle Database using:

const connection = await oracledb.getConnection({
    connectString : "config-file:///opt/oracle/my-config1.json"
});

The parameters of the connection string URL format are detailed in the table below.

Multiple alias names can be defined in a JSON file as shown below:

{
    "production": {
        "connect_descriptor": "(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
          (host=adb.region.oraclecloud.com))(connect_data=(service_name=dbsvcname))
          (security=(ssl_server_dn_match=yes)))"
    },
    "testing": {
        "connect_descriptor": "(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
          (host=adb.region.oraclecloud.com))(connect_data=(service_name=dbsvcname))
          (security=(ssl_server_dn_match=yes)))",
        "user": "scott",
        "password": {
          "type": "base64",
          "value": "dGlnZXI="
        }
    }
}

If you have this configuration stored in a JSON file in /opt/oracle/my-config2.json, you can connect to Oracle Database using:

const connection = await oracledb.getConnection({
    connectString : "config-file:///opt/oracle/my-config2.json?alias=production"
});

4.4.2. Using an OCI Object Storage Centralized Configuration Provider

The Oracle Cloud Infrastructure (OCI) Object Storage configuration provider enables the storage and management of Oracle Database connection information as JSON in OCI Object Storage. This configuration provider support was introduced in node-oracledb 6.6.

To use an OCI Object Storage Centralized Configuration Provider, you must:

1. Upload a JSON file that contains the connection information into an OCI Object Storage Bucket. See Connection Information for OCI Object Storage Centralized Configuration Provider.

   Also, see Uploading an Object Storage Object to a Bucket and the Oracle Database Net Services Administrator’s Guide for the steps.

2. Install the required OCI modules. See Install Modules for OCI Object Storage Configuration Provider.

3. Load the ociobject plugin in your application using require('oracledb/plugins/configProviders/ociobject').

4. Use an OCI Object Storage connection string URL in the connectString property of connection and pool creation methods.

Note that node-oracledb caches configurations by default, see Caching Configuration Information.

Connection Information for OCI Object Storage Configuration Provider

The connection information stored in a JSON file must contain a connect_descriptor key. Optionally, you can specify the database user name, password, wallet location, and node-oracledb properties. The database password can also be stored securely using OCI Vault. For details on the information that can be stored in this configuration provider, see Configuration Information Stored in Configuration Providers.

An example of a JSON file that can be used with OCI Object Centralized Storage Configuration Provider is:

{
    "connect_descriptor": "(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
            (host=adb.region.oraclecloud.com))(connect_data=(service_name=dbsvcname))
            (security=(ssl_server_dn_match=yes)))",

    "user": "scott",
    "password": {
        "type": "ocivault",
        "value": "ocid1.vaultsecret.my-secret-id"
    },
    "wallet_location": {
        "type": "ocivault",
        "value": "ocid1.vaultwallet.my-wallet-id"
    },
    "njs": {
        "stmtCacheSize": 30,
        "prefetchRows": 2,
        "poolMin": 2,
        "poolMax": 10
    }
}

OCI Object Storage Centralized Configuration Provider connectString Syntax

The connectString parameter for oracledb.getConnection() and oracledb.createPool() calls should use a connection string URL in the format:

config-ociobject://<objectstorage-name>/n/<namespaceName>/b/<bucketName>/o/
<objectName>[?key=<networkServiceName>&<option1>=<value1>&<option2>=<value2>...]

For example, a connection string to access OCI Object Storage and connect to Oracle Database is:

const connection = await oracledb.getConnection({
    connectString : "config-ociobject://abc.oraclecloud.com/n/abcnamespace/b/abcbucket/o/abcobject?oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"
});

The parameters of the connection string URL format are detailed in the table below.

4.4.3. Using an OCI Vault Centralized Configuration Provider

Oracle Cloud Infrastructure (OCI) Vault Centralized Configuration Provider enables the storage and management of Oracle Database connection information as JSON objects. This Centralized Configuration Provider support is available from node-oracledb 6.9 onwards.

To use an OCI Vault Centralized Configuration Provider, you must:

1. Enter and save the connection information as a secret in OCI Vault by using the Manual secret generation method. The connection information must be entered as a JSON object. See Connection Information for OCI Vault Centralized Configuration Provider.

   Also, see Creating a Secret in OCI Vault for the steps.

2. Install the required OCI modules. See Install Modules for OCI Vault Provider.

3. Load the ocivault plugin in your application using require('oracledb/plugins/configProviders/ocivault').

4. Use an OCI Vault connection string URL in the connectString property of connection and pool creation methods.

Note that node-oracledb caches configurations by default. See Caching Configuration Information.

Connection Information for OCI Vault Configuration Provider

The JSON object must contain a connect_descriptor key. Optionally, you can specify the database user name, password, wallet location, and node-oracledb properties. For details on the information that can be stored in this Centralized Configuration Provider, see Configuration Information Stored in Configuration Providers.

The JSON object syntax for OCI Vault Centralized Configuration Provider is the same as the syntax for OCI Object Storage. See OCI Object Storage example.

OCI Vault Centralized Configuration Provider connectString Syntax

The connectString parameter for oracledb.getConnection() and oracledb.createPool() calls should use a connection string URL in the format:

config-ocivault://<ocidvault>?[<option1>=<value1>&<option2>=<value2>...]

For example, a connection string to access OCI Vault and connect to Oracle Database is:

const connection = await oracledb.getConnection({
    connectString : "config-ocivault://ocid1.vaultsecret.oc1?oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"
});

The parameters of the connection string URL format are detailed in the table below.

4.4.4. Using an Azure App Centralized Configuration Provider

Azure App Configuration is a cloud-based service provided by Microsoft Azure. It can be used for storage and management of Oracle Database connection information as key-value pairs. This configuration provider support was introduced in node-oracledb 6.6.

To use node-oracledb with Azure App Configuration, you must:

1. Enter and save your configuration information in your Azure App Configuration Provider as key-value pairs. See Connection Information for Azure App Centralized Configuration Provider.

2. Install the required Azure Application modules. See Install Modules for Azure App Configuration Provider.

3. Load the azure plugin in your application using require('oracledb/plugins/configProviders/azure').

4. Use an Azure App Configuration connection string URL in the connectString parameter of connection and pool creation methods.

Note that node-oracledb caches configurations by default, see Caching Configuration Information.

Connection Information for Azure App Configuration Provider

Key-value pairs for stored connection information can be added using the Configuration explorer page of your Azure App Configuration. See Create a key-value in Azure App Configuration for more information. Also, see the Oracle Net Service Administrator’s Guide.

You can organize the key-value pairs under a prefix based on your application’s needs. For example, if you have two applications, Sales and Human Resources, then you can store the relevant configuration information under the prefix sales and the prefix hr.

The key-value pairs must contain the key connect_descriptor which specifies the database connect descriptor. This can be set using a prefix as “<prefix>/connect_descriptor”, for example, sales/connect_descriptor.

You can additionally store the database user name using a key such as “<prefix>/user”, and store the password using “<prefix>/password”. For example, sales/user and sales/password. The database password can also be stored securely using Azure Key Vault.

Optional node-oracledb properties can be set using a key such as “<prefix>/njs/<key name>”, for example sales/node-oracledb/poolMin. This is similar to how Oracle Call Interface settings use keys like “<prefix>/oci/<key name>” as shown in Oracle Database Net Services Administrator’s Guide.

For details on the information that can be stored in this configuration provider, see Configuration Information Stored in Configuration Providers.

The following table shows sample configuration information defined using the Configuration explorer page of your Azure App Configuration provider. The example uses the prefix test/.

Azure App Centralized Configuration Provider connectString Syntax

You must define a connection string URL in a specific format in the connectString property of oracledb.getConnection() or oracledb.createPool() to access the information stored in Azure App Configuration. The syntax is:

config-azure://<appconfigname>[?key=<prefix>&label=<value>&<option1>=<value1>&<option2>=<value2>…]

For example, a connection string to access the Azure App Configuration provider and connect to Oracle Database is:

const connection = await oracledb.getConnection({
    connectString : "config-azure://aznetnamingappconfig.azconfig.io/?key=test/&azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"
});

The parameters of the connection string URL format are detailed in the table below.

4.4.5. Using an Azure Key Vault Centralized Configuration Provider

Azure Key Vault is a cloud-based service provided by Microsoft Azure. It can be used for storage and management of Oracle Database connection information as a JSON object. This configuration provider support is available from node-oracledb 6.9 onwards.

To use node-oracledb with Azure Key Vault, you must:

1. Enter and save the connection information as a secret in your Azure Key Vault. The connection information must be stored as a JSON object. See Connection Information for Azure Key Vault Centralized Configuration Provider.

   For information on creating a secret, see Create a secret in Azure Key Vault.

2. Install the required Azure Application modules. See Install Modules for Azure Vault Configuration Provider.

3. Load the azurevault plugin in your application using require('oracledb/plugins/configProviders/azurevault').

4. Use an Azure Key Vault connection string URL in the connectString parameter of connection and pool creation methods.

Note that node-oracledb caches configurations by default, see Caching Configuration Information.

Connection Information for Azure Key Vault Configuration Provider

The JSON object must contain a connect_descriptor key. Optionally, you can specify the database user name, password, wallet location, and node-oracledb properties. For details on the information that can be stored in this configuration provider, see Configuration Information Stored in Configuration Providers.

The JSON object syntax for Azure Key Vault configuration provider is same as the syntax for OCI Object Storage, see the OCI Object Storage example.

Azure Key Vault Configuration Provider connectString Syntax

You must define a connection string URL in a specific format in the connectString property of oracledb.getConnection() or oracledb.createPool() to access the information stored in Azure Key Vault. The syntax is:

config-azurevault://<azure key vault url>?[<option1>=<value1>&<option2>=<value2>...]

For example, a connection string to access Azure Key Vault and connect to Oracle Database is:

const connection = await oracledb.getConnection({
    connectString : "config-azurevault://https://abc.vault.azure.net/secrets/azurevaultjson?azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"
});

The parameters of the connection string URL format are detailed in the table below.

4.4.6. Caching Configuration Information

Node-oracledb caches configurations obtained from Centralized Configuration Providers. This allows you to reuse the cached configuration information which significantly reduces the number of round-trips to the configuration provider.

You can define the number of seconds that node-oracledb should keep the information cached by using the config_time_to_live key in a Centralized Configuration Provider. After the config_time_to_live duration expires, the cached configuration information becomes invalid. So, node-oracledb refreshes the cache the next time the configuration information from the configuration provider is required.

An example of setting the cache time to 12 hours for File or OCI Object Storage Centralized Configuration Providers is:

{
    "connect_descriptor": "(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
            (host=adb.region.oraclecloud.com))(connect_data=(service_name=dbsvcname))
            (security=(ssl_server_dn_match=yes)))",
    "user": "scott",
    "password": {
        "type": "ocivault",
        "value": "ocid1.vaultsecret.my-secret-id"
    },
    "config_time_to_live": 43200,
    "njs": {
        "stmtCacheSize": 30,
        "prefetchRows": 2,
        "poolMin": 2,
        "poolMax": 10
    }
}

Also, you can use the oracledb.configProviderCacheTimeout property to specify the number of seconds that node-oracledb should keep the information cached. The default time is 86,400 seconds (24 hours).

If the config_time_to_live key is defined in a Centralized Configuration Provider, then this value overrides the value specified in the oracledb.configProviderCacheTimeout property. If this key is not defined in a configuration provider, then the value of oracledb.configProviderCacheTimeout is considered to be the duration that node-oracledb keeps the information cached.

4.5.1. Connections and Worker Threads

Node.js has four background worker threads by default (not to be confused with the newer user space worker_threads module). If you are using node-oracledb Thick mode and open more than four standalone connections or pooled connections, such as by increasing pool.poolMax, then you must increase the number of worker threads available to node-oracledb.

A worker thread pool that is too small can cause a decrease in application performance, deadlocks, or failure in connection requests with the error NJS-040: connection request timeout or NJS-076: connection request rejected.

A Node.js worker thread is used by each node-oracledb Thick mode connection to execute a database statement. Each thread will wait until all round-trips between node-oracledb and the database for the statement are complete. When an application handles a sustained number of user requests, and database operations take some time to execute or the network is slow, then all available threads may be held in use. This prevents other connections from beginning work and stops Node.js from handling more user load.

The thread pool size should be equal to, or greater than, the maximum number of connections. If the application does database and non-database work concurrently, then additional threads could also be required for optimal throughput.

Increase the thread pool size by setting the environment variable UV_THREADPOOL_SIZE before starting Node.js. For example, on Linux your package.json may have a script like:

"scripts": {
    "start": "export UV_THREADPOOL_SIZE=10 && node index.js"
},
. . .

Or, on Windows:

"scripts": {
    "start": "SET UV_THREADPOOL_SIZE=10 && node index.js"
},
. . .

With these, you can start your application with npm start. This will allow up to 10 connections to be actively excuting SQL statements in parallel.

On non-Windows platforms, the value can also be set inside the application. It must be set prior to any asynchronous Node.js call that uses the thread pool:

// !! First file executed.  Non-Windows only !!

process.env.UV_THREADPOOL_SIZE = 10

// ... rest of code

If you set UV_THREADPOOL_SIZE too late in the application, or try to set it this way on Windows, then the setting will be ignored and the default thread pool size of 4 will still be used. Note that pool.getStatistics() and pool.logStatistics() can only give the value of the variable, not the actual size of the thread pool created. On Linux you can use pstack to see how many threads are actually running. Node.js will create a small number of threads in addition to the expected number of worker threads.

The libuv library used by Node.js 12.5 and earlier limits the number of threads to 128. In Node.js 12.6 onward the limit is 1024. You should restrict the maximum number of connections opened in an application, that is, poolMax, to a value lower than UV_THREADPOOL_SIZE. If you have multiple pools, make sure the sum of all poolMax values is no larger than UV_THREADPOOL_SIZE.

4.5.2. Parallelism on Each Connection

Oracle Database can only execute operations one at a time on each connection. Examples of operations include connection.execute(), connection.executeMany(), connection.queryStream(), connection.getDbObjectClass(), connection.commit(), connection.close(), SODA calls, and streaming from Lobs. Multiple connections may be in concurrent use, but each connection can only do one thing at a time. Code will not run faster when parallel database operations are attempted using a single connection.

From node-oracledb 5.2, node-oracledb function calls that use a single connection for concurrent database access will be queued in the JavaScript layer of node-oracledb. In earlier node-oracledb versions, locking occurred in the Oracle Client libraries, which meant many threads could be blocked.

It is recommended to structure your code to avoid parallel operations on a single connection. For example, avoid using Promise.all() on a single connection. Similarly, instead of using async.parallel() or async.each() which call each of their items in parallel, use async.series() or async.eachSeries(). If you want to repeat a number of INSERT or UPDATE statements, then use connection.executeMany().

To rewrite code that uses Promise.all() you could, for example, use a basic for loop with async/await to iterate through each action:

async function myfunc() {
    const stmts = [
        `INSERT INTO ADRESSES (ADDRESS_ID, CITY) VALUES (94065, 'Redwood Shores')`,
        `INSERT INTO EMPLOYEES (ADDRESS_ID, EMPLOYEE_NAME) VALUES (94065, 'Jones')`
    ];

    for (const s of stmts) {
        await connection.execute(s);
    }
}

If you use ESlint for code validation, and it warns about await in loops for code that is using a single connection, then disable the no-await-in-loop rule for these cases.

Another alternative rewrite for Promise.all() is to wrap the SQL statements in a single PL/SQL block.

Note that using functions like Promise.all() to fetch rows from nested cursor result sets can result in inconsistent data.

During development, you can set oracledb.errorOnConcurrentExecute to true to help identify application code that executes concurrent database operations on a single connection. Such uses may be logic errors such as missing await keywords that could lead to unexpected results. When errorOnConcurrentExecute is set to true, an error will be thrown so you can identify offending code. Setting errorOnConcurrentExecute is not recommended for production use in case it generates errors during normal operation. For example third-party code such as a framework may naturally use Promise.all() in its generic code. Or your application may be coded under the assumption that node-oracledb will do any necessary serialization. Note the use of errorOnConcurrentExecute will not affect parallel use of multiple connections, which may all be in use concurrently, and each of which can be doing a single operation.

4.7.1. Driver Connection Pooling

Node-oracledb’s driver connection pooling lets applications create and maintain a pool of open connections to the database. Connection pooling is available in both Thin and Thick modes. Connection pooling is important for performance and scalability when applications need to handle a large number of users who do database work for short periods of time but have relatively long periods when the connections are not needed. The high availability features of pools also make small pools useful for applications that want a few connections available for infrequent use and requires them to be immediately usable when acquired. Applications that would benefit from connection pooling but are too difficult to modify from the use of standalone connections can take advantage of Implicit Connection Pooling.

Each node-oracledb process can use one or more connection pools. Each pool can contain zero or more connections. In addition to providing an immediately available set of connections, pools provide dead connection detection and transparently handle Oracle Database High Availability events. This helps shield applications during planned maintenance and from unplanned failures.

In node-oracledb Thick mode, the pool implementation uses Oracle’s session pool technology which supports additional Oracle Database features for example some advanced high availability features.

const pool = await oracledb.createPool({
  user          : "hr",
  password      : mypw,  // mypw contains the hr schema password
  connectString : "localhost/FREEPDB1",
  poolIncrement: 1,
  poolMin: 1,
  poolMax: 5
});

const oracledb = require('oracledb');

const mypw = ...  // set mypw to the hr schema password

// Start a connection pool (which becomes the default pool) and start the webserver
async function init() {
    try {

        await oracledb.createPool({
            user          : "hr",
            password      : mypw,               // mypw contains the hr schema password
            connectString : "localhost/FREEPDB1",
            poolIncrement : 0,
            poolMax       : 4,
            poolMin       : 4
        });

        const server = http.createServer();
        server.on('error', (err) => {
            console.log('HTTP server problem: ' + err);
        });
        server.on('request', (request, response) => {
            handleRequest(request, response);
        });
        await server.listen(3000);

        console.log("Server is running");

    } catch (err) {
        console.error("init() error: " + err.message);
    }
}

async function handleRequest(request, response) {

    response.writeHead(200, {"Content-Type": "text/html"});
    response.write("<!DOCTYPE html><html><head><title>My App</title></head><body>");

    let connection;
    try {

        connection = await oracledb.getConnection();  // get a connection from the default pool
        const result = await connection.execute(`SELECT * FROM locations`);

        displayResults(response, result);  // do something with the results

    } catch (err) {
        response.write("<p>Error: " + text + "</p>");
    } finally {
        if (connection) {
            try {
                await connection.close();  // always release the connection back to the pool
            } catch (err) {
                console.error(err);
            }
        }
    }

    response.write("</body></html>");
    response.end();

}

const connection = await pool.getConnection();

const oracledb = require('oracledb');

const mypw = ...  // set mypw to the hr schema password

async function run() {
    try {
        await oracledb.createPool({
            user          : "hr",
            password      : mypw  // mypw contains the hr schema password
            connectString : "localhost/FREEPDB1"
        });

        let connection;
        try {
            // get connection from the pool and use it
            connection = await oracledb.getConnection();
            result = await connection.execute(`SELECT last_name FROM employees`);
            console.log("Result is:", result);
        } catch (err) {
            throw (err);
        } finally {
            if (connection) {
                try {
                    await connection.close(); // Put the connection back in the pool
                } catch (err) {
                    throw (err);
                }
            }
        }
    } catch (err) {
        console.error(err.message);
    } finally {
        await oracledb.getPool().close(0);
    }
}

run();

await pool.close();

4.7.2. Connection Pool Sizing

The main characteristics of a connection pool are determined by its attributes poolMin, poolMax, poolIncrement, and poolTimeout.

Setting poolMin causes the specified number of connections to be established to the database during pool creation. This allows subsequent pool.getConnection() calls to return quickly for an initial set of users. An appropriate poolMax value avoids overloading the database by limiting the maximum number of connections ever opened.

Pool expansion happens when pool.getConnection() is called and both the following are true:

• all the currently established connections in the pool are “checked out” of the pool by previous pool.getConnection() calls

• the number of those currently established connections is less than the pool’s poolMax setting

Pool shrinkage happens when the application returns connections to the pool, and they are then unused for more than poolTimeout seconds. Any excess connections above poolMin will be closed. When node-oracledb Thick mode is using using Oracle Client 19 or earlier, this pool shrinkage is only initiated when the pool is accessed, so a pool in a completely idle application will not shrink.

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

The Oracle Real-World Performance Group’s recommendation is to use fixed size connection pools. The values of poolMin and poolMax should be the same. This avoids connection storms which can decrease throughput. See Guideline for Preventing Connection Storms: Use Static Pools, which contains more details about sizing of pools. Having a fixed size will guarantee that the database can handle the upper pool size. For example, if a pool needs to grow but the database resources are limited, then pool.getConnection() may return errors such as ORA-28547. With a fixed pool size, this class of error will occur when the pool is created, allowing you to change the size before users access the application. With a dynamically growing pool, the error may occur much later after the pool has been in use for some time.

The Real-World Performance Group also recommends keeping pool sizes small, as this may perform better than larger pools. Use pool.getStatistics() or pool.logStatistics() to monitor pool usage. The pool attributes should be adjusted to handle the desired workload within the bounds of resources available to Node.js and the database.

When the values of poolMin and poolMax are the same, poolIncrement can be set greater than zero. (In Thick mode this needs Oracle Client 18c or later). This value changes how a homogeneous pool grows when the number of connections established has become lower than poolMin, for example if network issues have caused connections to become unusable and they have been dropped from the pool. Setting poolIncrement greater than 1 in this scenario means the next pool.getConnection() call that needs to grow the pool will initiate the creation of multiple connections. That pool.getConnection() call will not return until the extra connections have been created, so there is an initial time cost. However it can allow subsequent connection requests to be immediately satisfied. In this growth scenario, a poolIncrement of 0 is treated as 1.

The optional pool creation property maxLifetimeSession also allows pools to shrink. This property bounds the total length of time that a connection can exist in a pool after first being created. It is mostly used for defensive programming to mitigate against unforeseeable problems that may occur with connections. If a connection was created maxLifetimeSession or longer seconds ago, then it will be a candidate for being closed.

In node-oracledb Thick mode, Oracle Client libraries 12.1, or later, are needed to use maxLifetimeSession. Note that when using node-oracledb in Thick mode with Oracle Client libraries prior to 21c, pool shrinkage is only initiated when the pool is accessed. So, pools in fully dormant applications will not shrink until the application is next used.

If both poolTimeout and maxLifetimeSession properties are set on a pooled connection, the connection will be terminated if either the idle timeout happens or the maxLifeTimeSession setting is exceeded. In this case, if connections are idle for more than poolTimeout, they are dropped only when doing so will ensure that pool.connectionsOpen is more than or equal to the poolMin setting. If the connection lifetime exceeds maxLifetimeSession seconds, it is dropped and a new connection is created in the pool.

Connection pool health can be impacted by firewalls, resource manager, or user profile IDLE_TIME values. For best efficiency, ensure these do not expire idle connections since this will require connections to be recreated which will impact performance and scalability. See Preventing Premature Connection Closing.

4.7.3. Connection Pool Closing and Draining

Closing a connection pool allows database resources to be freed. If Node.js is killed without pool.close() being called successfully, then some time may pass before the unused database-side of connections are automatically cleaned up in the database.

When pool.close() is called with no parameter, the pool will be closed only if all connections have been released to the pool with connection.close(). Otherwise an error is returned and the pool will not be closed.

An optional drainTime parameter can be used to force the pool closed even if connections are in use. This lets the pool be ‘drained’ of connections. The drainTime indicates how many seconds the pool is allowed to remain active before it and its connections are terminated. For example, to give active connections 10 seconds to complete their work before being terminated:

await pool.close(10);

When a pool has been closed with a specified drainTime, then any new pool.getConnection() calls will fail. If connections are currently in use by the application, they can continue to be used for the specified number of seconds, after which the pool and all open connections are forcibly closed. Prior to this time limit, if there are no connections currently “checked out” from the pool with getConnection(), then the pool and its connections are immediately closed.

In network configurations that drop (or in-line) out-of-band breaks, forced pool termination may hang unless you have DISABLE_OOB=ON in a sqlnet.ora file, see Optional Oracle Net Configuration.

Non-zero drainTime values are recommended so that applications have the opportunity to gracefully finish database operations. However, pools can be forcibly closed by specifying a zero drain time:

await pool.close(0);

Closing the pool would commonly be one of the last stages of a Node.js application. A typical closing routine look likes:

// Close the default connection pool with 10 seconds draining, and exit
async function closePoolAndExit() {
    console.log("\nTerminating");
    try {
        await oracledb.getPool().close(10);
        process.exit(0);
    } catch(err) {
        console.error(err.message);
        process.exit(1);
    }
}

It is helpful to invoke closePoolAndExit() if Node.js is sent a signal or interrupted:

// Close the pool cleanly if Node.js is interrupted
process
    .once('SIGTERM', closePoolAndExit)
    .once('SIGINT',  closePoolAndExit);

If pool.close() is called while a pool.reconfigure() is taking place, then an error will be thrown.

4.7.4. Connection Pool Caching

When pools are created, they can be given a named alias. The alias can later be used to retrieve the related pool object for use. This facilitates sharing pools across modules and simplifies getting connections.

Pools are added to the cache by using a poolAlias property in the poolAttrs object:

async function init() {
try {
    await oracledb.createPool({ // no need to store the returned pool
        user: 'hr',
        password: mypw,  // mypw contains the hr schema password
        connectString: 'localhost/FREEPDB1',
        poolAlias: 'hrpool'
    });

    // do stuff
    . . .

    // get the pool from the cache and use it
    const pool = oracledb.getPool('hrpool');
    . . .
}

There can be multiple pools in the cache if each pool is created with a unique alias.

If a pool is created without providing a pool alias:

• If no other pool in the cache already has the alias of ‘default’, then the new pool will be cached using the pool.poolAlias ‘default’.

  This pool is used by default in methods that utilize the connection pool cache.

• If an existing pool in the cache already has the alias ‘default’, then pool.poolAlias of the new pool will be undefined and the pool will be not stored in the pool cache. The application must retain a variable for subsequent pool use: const pool = await oracledb.createPool({   . . . }).

Methods that can affect or use the connection pool cache include:

• oracledb.createPool(): Can add a pool to the cache.

• oracledb.getPool(): Retrieves a pool from the cache.

• oracledb.getConnection(): Can use a pool in the cache to retrieve connections.

• pool.close(): Automatically removes a pool from the cache.

async function init() {
    try {
        await oracledb.createPool({
            user: 'hr',
            password: mypw,  // mypw contains the hr schema password
            connectString: 'localhost/FREEPDB1'
        });

        . . .
}

const connection = await oracledb.getConnection();

. . . // Use connection from the previously created 'default' pool

await connection.close(); // always release the connection back to the pool

const pool = oracledb.getPool();
console.log(pool.poolAlias); // 'default'
const connection = await pool.getConnection();

. . . // Use connection

await connection.close();

await oracledb.createPool({
    user: 'hr',
    password: myhrpw,  // myhrpw contains the hr schema password
    connectString: 'localhost/FREEPDB1',
    poolAlias: 'hrpool'
});

await oracledb.createPool({
    user: 'sh',
    password: myshpw,  // myshpw contains the sh schema password
    connectString: 'localhost/FREEPDB1',
    poolAlias: 'shpool'
});

. . .

const connection = await oracledb.getConnection('hrpool');

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

const connection = await oracledb.getConnection({ poolAlias: 'hrpool' });

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

const connection = await oracledb.getConnection({ poolAlias: 'hrpool', tag: 'loc=cn;p=1' });

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

const connection = await oracledb.getConnection({ poolAlias: 'default', tag: 'loc=cn;p=1' });

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

const pool = oracledb.getPool('hrpool');
const connection = await pool.getConnection();

. . . // Use connection from the pool

await connection.close();

4.7.5. Connection Pool Queue

The number of users that can concurrently do database operations is limited by the number of connections in the pool. The maximum number of connections is poolMax. Node-oracledb queues any additional pool.getConnection() requests to prevent users from immediately getting an error that the database is not available. The connection pool queue allows applications to gracefully handle more users than there are connections in the pool, and to handle connection load spikes without having to set poolMax too large for general operation.

If the application has called pool.getConnection() (or oracledb.getConnection() calls that use a pool) enough times so that all connections in the pool are in use, and further getConnection() calls are made, then each of those new getConnection() requests will be queued and will not return until an in-use connection is released back to the pool with connection.close(). If, instead, poolMax has not been reached, then the additional connection requests can be immediately satisfied and are not queued.

The amount of time that a queued request will wait for a free connection can be configured with queueTimeout. When connections are timed out of the queue, the pool.getConnection() call returns the error NJS-040: connection request timeout to the application.

If more than oracledb.queueMax pending connection requests are in the queue, then pool.getConnection() calls will immediately return an error NJS-076: connection request rejected. Pool queue length queueMax reached and will not be queued. Use this to protect against connection request storms. The setting helps applications return errors early when many connections are requested concurrently. This avoids connection requests blocking (for up to queueTimeout seconds) while waiting an available pooled connection. It lets you see when the pool is too small.

You may also experience NJS-040 or NJS-076 errors if your application is not correctly closing connections, or if are using node-oracledb Thick mode and UV_THREADPOOL_SIZE is too small.

4.7.6. Connection Pool Monitoring

Connection pool usage should be monitored to choose the appropriate settings for your workload. If the current settings are non optimal, then pool.reconfigure() can be called to alter the configuration.

Pool attributes connectionsInUse and connectionsOpen always provide basic information about an active pool:

const pool = await oracledb.createPool(...);

. . .

console.log(pool.connectionsOpen);   // how big the pool actually is
console.log(pool.connectionsInUse);  // how many of those connections are held by the application

Statistics are calculated from the time the pool was created, or since pool.reconfigure() was used to reset the statistics.

The recording of pool queue statistics, pool settings, and related environment variables can be enabled by setting enableStatistics to true when using oracledb.createPool() or pool.reconfigure().

To enable recording of statistics when creating the pool:

const pool = await oracledb.createPool({
    enableStatistics : true,   // default is false
    user             : "hr",
    password         : mypw,   // mypw contains the hr schema password
    connectString    : "localhost/FREEPDB1"
});
. . .

Statistics can alternatively be enabled on a running pool with:

await pool.reconfigure({ enableStatistics: true });

Applications can then get the current statistics by calling pool.getStatistics() which returns a PoolStatistics Class object. Attributes of the object can be accessed individually for your tracing requirements. The complete statistics can be printed by calling poolstatistics.logStatistics().

const poolstatistics = pool.getStatistics();

console.log(poolstatistics.currentQueueLength);  // print one attribute
poolstatistics.logStatistics();                  // print all statistics to the console

Alternatively the statistics can be printed directly by calling pool.logStatistics().

pool.logStatistics();    // print all statistics to the console

The output of poolstatistics.logStatistics() and pool.logStatistics() is identical.

For efficiency, the minimum, maximum, average, and sum of times in the pool queue are calculated when requests are removed from the queue. They include times for connection requests that were dequeued when a pool connection became available, and also for connection requests that timed out. They do not include times for connection requests still waiting in the queue.

The sum of ‘requests failed’, ‘requests exceeding queueMax’, and ‘requests exceeding queueTimeout’ is the number of pool.getConnection() calls that failed.

The PoolStatistics object and logStatistics() function record the following:

4.7.7. Connection Pool Pinging

When a connection is aquired from a pool with getConnection(), node-oracledb does some internal checks to validate if the about-to-be-returned connection is usable. If it is not usable, node-oracledb can replace it with a different connection before returning this to the application.

Connections may become unusable for various reasons including network dropouts, database instance failures, session termination from the database resource manager or user resource profile IDLE_TIME, or from a DBA issuing an ALTER SYSTEM KILL SESSION command.

By default, idle connections in the pool are unaware of these events. So, a getConnection() call could return an unusable connection to the application and errors would only occur when it is later used. The internal pool validation checks help provide tolerance against this situation so that statement execution using a connection is more likely to succeed.

Each time getConnection() is called, a lightweight connection validity check occurs. (In node-oracledb Thick mode, this requires Oracle Client library version 12.2 or later). The lightweight check allows node-oracledb to detect and replace connections that have become unusable due to some network errors.

An additional internal check performed by getConnection() can be configured during pool creation. This extra check helps detect errors such as the connection having exceeded the user profile resource limits, or from an explicit session closure from a DBA. This extra check performs a round-trip ping to the database which impacts performance. So, it is not done for each getConnection() call by default.

The frequency of pinging can be controlled with the oracledb.poolPingInterval property or during pool creation to meet your quality of service requirements.

The default poolPingInterval value is 60 seconds, which is suitable for most active applications. Possible values are:

When getConnection() is called to return a pooled connection, and the connection has been idle in the pool (not “checked out” to the application by getConnection()) for the specified poolPingInterval time, then an internal “ping” will be performed first. If the ping detects the connection is invalid then node-oracledb internally drops the unusable connection and obtains another from the pool. This second connection may also need a ping. This ping-and-release process may be repeated until:

• an existing connection that does not qualify for pinging is obtained. The getConnection() call returns this to the application. Note that since a ping may not have been performed, the connection is not guaranteed to be usable.

• a new, usable connection is opened. This is returned to the application.

• a number of unsuccessful attempts to find a valid connection have been made, after which an error is returned to the application.

Pools in active use may never have connections idle longer than poolPingInterval, so pinging often only occurs for infrequently accessed connection pools.

Because a ping may not occur every time a connection is returned from pool.getConnection(), and also it is possible for network outages to occur after getConnection() is called, applications should continue to use appropriate statement execution error checking.

For ultimate scalability, disable explicit pool pinging by setting poolPingInterval to a negative value, and make sure the firewall, database resource manager, or user profile are not expiring idle connections. See Preventing Premature Connection Closing. When using node-oracledb Thick mode, use use Oracle client 12.2 (or later) libraries.

In all cases, when a bad connection is released back to the pool with connection.close(), the connection is automatically destroyed. This allows a valid connection to the database to be opened by some subsequent getConnection() call.

Explicit pings can be performed at any time with connection.ping().

The time to wait for a response from connection.ping() can be controlled with the oracledb.poolPingTimeout property or with the poolPingTimeout property during pool creation.

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

4.7.8. Connection Tagging and Session State

Applications can set “session” state in each connection. For all practical purposes, connections are synonymous with sessions. Examples of session state are NLS settings from ALTER SESSION statements. Pooled connections will retain their session state after they have been released back to the pool with connection.close(). However, because pools can grow, or connections in the pool can be recreated, there is no guarantee a subsequent pool.getConnection() call will return a database connection that has any particular state.

The oracledb.createPool() option attribute sessionCallback can be used to set session state efficiently so that connections have a known session state. The sessionCallback can be a Node.js function that will be called whenever pool.getConnection() will return a newly created database connection that has not been used before. It is also called when connection tagging is being used and the requested tag is not identical to the tag in the connection returned by the pool. It is called before pool.getConnection() returns in these two cases. It will not be called in other cases. Using a callback saves the cost of setting session state if a previous user of a connection has already set it. The caller of pool.getConnection() can always assume the correct state is set. The sessionCallback can also be a PL/SQL procedure.

Connection tagging and sessionCallback were introduced in node-oracledb 3.1.

There are three common scenarios for sessionCallback:

• When all connections in the pool should have the same state use a simple Node.js Session Callback without tagging.

• When connections in the pool require different state for different users use a Node.js Session Tagging Callback.

• With DRCP, use a PL/SQL Session Tagging Callback.

function initSession(connection, requestedTag, callbackFn) {
connection.execute(
    `alter session set nls_date_format = 'YYYY-MM-DD' nls_language = AMERICAN`,
    callbackFn);
}

try {
    const pool = await oracledb.createPool({
        user: 'hr',
        password: mypw,  // mypw contains the hr schema password
        connectString: 'localhost/FREEPDB1',
        sessionCallback: initSession
    });
    . . .
}

function initSession(connection, requestedTag, callbackFn) {
    connection.clientId = "Chris";
    connection.execute(
        `begin
            execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
            insert into user_log (id, ts) values (sys_context('userenv', 'client_identifier'), systimestamp);
            commit;
         end;`,
        callbackFn);
}

function initSession(connection, requestedTag, callbackFn) {
    const tagParts = requestedTag.split('=');
    if (tagParts[0] != 'USER_TZ') {
        callbackFn(new Error('Error: Only property USER_TZ is supported'));
        return;
    }

    connection.execute(
        `ALTER SESSION SET TIME_ZONE = '${tagParts[1]}'`,
        (err) => {
            // Record the connection's new state and return
            connection.tag = requestedTag;
            callbackFn(err);
        }
    );
}

try {
    await oracledb.createPool({
        user: 'hr',
        password: mypw,  // mypw contains the hr schema password
        connectString: 'localhost/FREEPDB1',
        sessionCallback: initSession
    });

    // Get a connection with a given tag (and corresponding session state) from the pool
    const connection = await oracledb.getConnection({poolAlias: 'default', tag: "USER_TZ=UTC" });

    . . . // Use the connection

    // The connection will be returned to the pool with the tag value of connection.tag
    await connection.close(); // always release the connection back to the pool

    . . .

CREATE OR REPLACE PACKAGE myPackage AS
  TYPE property_t IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  PROCEDURE buildTab(
    tag          IN  VARCHAR2,
    propertyTab  OUT property_t
  );
  PROCEDURE myPlsqlCallback (
    requestedTag IN  VARCHAR2,
    actualTag    IN  VARCHAR2
  );
END;
/

CREATE OR REPLACE PACKAGE BODY myPackage AS

  -- Parse the "property=value" pairs in the tag
  PROCEDURE buildTab(tag IN VARCHAR2, propertyTab OUT property_t) IS
    property  VARCHAR2(64);
    propertyName  VARCHAR2(64);
    propertyValue VARCHAR2(64);
    propertyEndPos NUMBER := 1;
    propertyStartPos NUMBER := 1;
    propertyNameEndPos NUMBER := 1;
  begin
    WHILE (LENGTH(tag) > propertyEndPos)
    LOOP
      propertyEndPos := INSTR(tag, ';', propertyStartPos);
      IF (propertyEndPos = 0) THEN
        propertyEndPos := LENGTH(tag) + 1;
      END IF;
      propertyNameEndPos := INSTR(tag, '=', propertyStartPos);
      propertyName := SUBSTR(tag, propertyStartPos,
                   propertyNameEndPos - propertyStartPos);
      propertyValue := SUBSTR(tag, propertyNameEndPos + 1,
                    propertyEndPos - propertyNameEndPos - 1);
      propertyTab(propertyName) := propertyValue;
      propertyStartPos := propertyEndPos + 1;
    END LOOP;
  END;

  PROCEDURE myPlsqlCallback (
    requestedTag IN VARCHAR2,
    actualTag IN VARCHAR2
  ) IS
    reqPropTab property_t;
    actPropTab property_t;
    propertyName VARCHAR2(64);
  BEGIN
    buildTab(requestedTag, reqPropTab);
    buildTab(actualTag, actPropTab);

    -- Iterate over requested properties to set state when it's not
    -- currently set, or not set to the desired value
    propertyName := reqPropTab.FIRST;
    WHILE (propertyName IS NOT NULL)
    LOOP
      IF ((NOT actPropTab.exists(propertyName)) OR
         (actPropTab(propertyName) != reqPropTab(propertyName))) THEN
        IF (propertyName = 'SDTZ') THEN
          EXECUTE IMMEDIATE
            'ALTER SESSION SET TIME_ZONE=''' || reqPropTab(propertyName) || '''';
        ELSE
          RAISE_APPLICATION_ERROR(-20001,'Unexpected session setting requested');
        END IF;
      END IF;
      propertyName := reqPropTab.NEXT(propertyName);
    END LOOP;
    -- Could iterate over other actual properties to set any to a default state
  END;

END myPackage;
/

const sessionTag = "SDTZ=UTC";

try {
    const pool = await oracledb.createPool({
                 user: 'hr',
                 password: mypw,  // mypw contains the hr schema password
                 connectString: 'localhost/FREEPDB1',
                 sessionCallback: "myPackage.myPlsqlCallback"
                });
    . . .

    const connection = await pool.getConnection({tag: sessionTag});

    . . . // The value of connection.tag will be sessionTag
         // Use connection.

    await connection.close();
}

4.7.9. Heterogeneous and Homogeneous Connection Pools

By default, connection pools are ‘homogeneous’ meaning that all connections use the same database credentials. Both node-oracledb Thin and Thick modes support homogeneous pools.

const pool = await oracledb.createPool({
    connectString : "localhost/FREEPDB1",  // no user name or password
    homogeneous   : false,
    . . .  // other pool options such as poolMax
});

const connection = await pool.getConnection({
    user     : "hr",
    password : mypw,  // mypw contains the hr schema password
});

. . . // use connection

await connection.close();

const connection = await oracledb.getConnection({
    poolAlias: "default",
    user     : "hr",
    password : mypw,  // mypw contains the hr schema password
});

4.8.1. Using Centralized Configuration Provider Hook Functions

The oracledb.registerConfigurationProviderHook() method registers the centralized configuration provider extension modules that were loaded in your code. When this method is called, it registers a user hook function that will be called internally by node-oracledb prior to connection or pool creation. The hook function will be invoked when oracledb.getConnection() or oracledb.createPool() are called with the connectString property prefixed with a specified configuration provider. Your hook function can retrieve the connection information, which node-oracledb can subsequently use to complete the connection or pool creation.

Pre-supplied node-oracledb plugins such as OCI Object Storage (ociobject), OCI Vault (ocivault), Azure App Configuration (azure), and Azure Key Vault (azurevault) make use of oracledb.registerConfigurationProviderHook(). These plugins use the information found in a connection method’s connectString property which allows node-oracledb to access the required information from the configuration provider, and connect to Oracle Database with this information. For the complete plugin implementation, see the respective folders of the configuration providers in the plugins/configProviders directory of the node-oracledb package. Also, you can define your own plugins for centralized configuration providers and register it using oracledb.registerConfigurationProviderHook(), similar to how the pre-supplied node-oracledb plugins are registered.

Once you define the plugin and register it using oracledb.registerConfigurationProviderHook(), you can connect to Oracle Database using the information stored in the configuration provider, for example, with OCI Vault:

// Load the ocivault plugin
require('oracledb/plugins/configProviders/ocivault');

// Use an OCI vault connection string
const connection = await oracledb.getConnection({
    connectString : "config-ocivault://ocid1.vaultsecret.oc1?oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"
});

For more information on the centralized configuration providers supported by node-oracledb, see Centralized Configuration Providers.

4.8.2. Using Process Configuration Hook Functions

The oracledb.registerProcessConfigurationHook() method registers extension modules that were added to your code. When this method is called, it registers a user hook function that will be called internally by node-oracledb prior to connection or pool creation. The hook function will be called when oracledb.getConnection() or oracledb.createPool() are called. Your hook function accepts a copy of the parameters that will be used to create the standalone or pool connections. The function can access and modify them in any way necessary to allow node-oracledb to subsequently complete the connection or pool creation request.

Pre-supplied node-oracledb plugins such as extensionOci and extensionAzure make use of oracledb.registerProcessConfigurationHook(). The extensionOci uses the information found in a connection method’s tokenAuthConfigOci property and modifies the accessToken property with a function that will acquire the authentication token needed to complete a connection. The key code section showing registering of a hook function is:

function hookFn(options) {
  if (options.tokenAuthConfigOci) {
    options.accessToken = async function callbackFn(refresh, config) {
      return await getToken(config);
    };
    options.accessTokenConfig = options.tokenAuthConfigOci;
  }
}
oracledb.registerProcessConfigurationHook(hookFn);

Your code can then try to connection, for example:

await oracledb.getConnection({
      tokenAuthConfigOci: {
        authType: ...,           // OCI-specific parameters to be set when
        profile: ...,            // using extensionOci plugin with authType
        configFileLocation: ...  // configFileBasedAuthentication
      }
      externalAuth: true,        // must specify external authentication
      connectString: ...         // Oracle Autonomous Database connection string
    });

4.9.1. Enabling DRCP in Oracle Database

Oracle Database versions prior to 21c have a single DRCP connection pool. From Oracle Database 21c, each pluggable database can optionally have its own pool, or can use the container level pool. From Oracle Database 23.4, you can create multiple pools at the pluggable, or container, database level. This multi-pool feature is useful where different applications connect to the same database, but there is a concern that one application’s use of the pool may impact other applications. If this is not the case, a single pool may allow best resource sharing on the database host.

Note that DRCP is already enabled in Oracle Autonomous Database and pool management is different to the steps below.

DRCP pools can be configured and administered by a DBA using the DBMS_CONNECTION_POOL package:

EXECUTE DBMS_CONNECTION_POOL.CONFIGURE_POOL(
  pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
  minsize => 4,
  maxsize => 40,
  incrsize => 2,
  session_cached_cursors => 20,
  inactivity_timeout => 300,
  max_think_time => 600,
  max_use_session => 500000,
  max_lifetime_session => 86400)

Alternatively, the method DBMS_CONNECTION_POOL.ALTER_PARAM() can set a single parameter:

EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM(
  pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
  param_name => 'MAX_THINK_TIME',
  param_value => '1200')

The inactivity_timeout parameter terminates idle pooled servers, helping optimize database resources. To avoid pooled servers permanently being held onto by a Node.js script, the max_think_time parameter can be set. The parameters num_cbrok and maxconn_cbrok can be used to distribute the persistent connections from the clients across multiple brokers. This may be needed in cases where the operating system per-process descriptor limit is small. Some customers have found that having several connection brokers improves performance. The max_use_session and max_lifetime_session parameters help protect against any unforeseen problems affecting server processes. The default values will be suitable for most users. See the Oracle DRCP documentation for details on these parameters.

In general, if pool parameters are changed, then the pool should be restarted. Otherwise, server processes will continue to use old settings.

You can use a DBMS_CONNECTION_POOL.RESTORE_DEFAULTS() procedure to reset all of the values.

When DRCP is used with Oracle RAC, each database instance has its own connection broker and pool of servers. Each pool has the identical configuration. For example, all pools start with minsize server processes. A single DBMS_CONNECTION_POOL command will alter the pool of each instance at the same time. The pool needs to be started before connection requests begin. The command below does this by bringing up the broker, which registers itself with the database listener:

EXECUTE DBMS_CONNECTION_POOL.START_POOL()

Once enabled this way, the pool automatically restarts when the database instance restarts, unless explicitly stopped with the DBMS_CONNECTION_POOL.STOP_POOL() command:

EXECUTE DBMS_CONNECTION_POOL.STOP_POOL()

Oracle Database version 23 allows a DRAINTIME argument to be passed to STOP_POOL(), indicating that the pool will only be closed after the specified time. This allows in-progress application work to continue. A draintime value of 0 can be used to immediately close the pool. See the database documentation on DBMS_CONNECTION_POOL.STOP_POOL().

In older Oracle Database versions, the pool cannot be stopped while connections are open.

4.9.2. Configuring Application to Use DRCP

Application connections using DRCP should specify a user-chosen connection class name when requesting a DRCP pooled server. A ‘purity’ of the connection session state can optionally be specified. See the Oracle Database documentation on benefiting from scalability for more information on purity and connection classes.

The best practice is to use DRCP in conjunction with a local driver connection pool created with oracledb.createPool(). The node-oracledb connection pool size does not need to match the DRCP pool size. The limit on overall execution parallelism is determined by the DRCP pool size. Note that when using DRCP with a node-oracledb local connection pool in Thick mode, the local connection pool min value is ignored and the pool will be created with zero connections.

Use a Pooled Server for a Connection

To enable connections to use a pooled server, you can:

• Specify to use a pooled server in the connectString property (or its alias connectionString) of oracledb.createPool() or oracledb.getConnection(). For example with the Easy Connect syntax:

  const pool = await oracledb.createPool({
      user          : "hr",
      password      : mypw,  // mypw contains the hr schema password
      connectString : "mydbmachine.example.com/orclpdb1:pooled"
  });
  

• Alternatively, add (SERVER=POOLED) to the Connect Descriptor such as used in an Oracle Network configuration file tnsnames.ora:

  customerpool = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
            (HOST=dbhost.example.com)
            (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CUSTOMER)
            (SERVER=POOLED)))
  

Setting DRCP Connection Classes

The best practice is to specify a name for a connection by using the oracledb.connectionClass property. If it is set, then the connection class specified in this property is used in both standalone and pooled connections. This user-chosen name provides some partitioning of DRCP session memory so reuse is limited to similar applications. It provides maximum pool sharing if multiple application processes are started and use the same class name. A class name also allows better DRCP usage tracking in the database. In the database monitoring views, the class name shown will be the value specified in the application prefixed with the user name.

To enable a connection to use a pooled server and to specify a class name, you can use:

oracledb.connectionClass = 'HRPOOL';

const pool = await oracledb.createPool({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com/orclpdb1:pooled"
});

You can also specify the connection class in a connection string by setting the POOL_CONNECTION_CLASS parameter. If this parameter is set, then this connection class is used in both standalone and pooled connections. See Setting the Connection Class and Purity in the Connection String.

If both the oracledb.connectionClass property and the POOL_CONNECTION_CLASS connection string parameter are set, then the POOL_CONNECTION_CLASS parameter has the highest priority and overrides the default or application specified values.

If oracledb.connectionClass and POOL_CONNECTION_CLASS connection string parameter are not set, then:

• For standalone connections, the session request is sent to the shared connection class in DRCP.

• For pooled connections, the pool generates a unique connection class if a previously generated connection class does not exist. This connection class is used when acquiring connections from the pool. Node-oracledb Thin mode generates a connection class with the prefix “NJS” while Thick mode generates a connection class with the prefix “OCI”.

If the connection class is not set, the pooled server session memory will not be reused optimally, and the statistic views will record large values for NUM_MISSES.

Acquiring a DRCP Connection

Once DRCP has been enabled and the driver connection pool has been created with the appropriate connection string, then your application can get a connection from a pool that uses DRCP by calling:

const connection = pool.getConnection();

Closing Connections when using DRCP

Similar to using a node-oracledb connection pool, Node.js scripts where node-oracledb connections do not go out of scope quickly (which releases them), or do not currently use connection.close() should be examined to see if the connections can be closed earlier. This allows maximum reuse of DRCP pooled servers by other users:

const pool = await oracledb.createPool({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb:pooled?pool_connection_class=MYAPP&pool_purity=self"
});

// Do some database operations
const connection = pool.getConnection();

...

connection.close();

// Do lots of non-database work
. . .

// Do some more database operations
const connection = pool.getConnection();   // Get a new pooled server only when needed
. . .
connection.close();

4.9.3. Setting DRCP Parameters in Connection Strings

You can specify the connection class and pool purity in connection strings when using node-oracledb Thin mode with Oracle Database 21c (or later). For node-oracledb Thick mode, you require Oracle Database 21c (or later) and Oracle Client 19c (or later).

DRCP allows the connection session memory to be reused or cleaned each time a connection is acquired from the pool by specifying the pool purity. You can specify the pool purity in node-oracledb by setting the POOL_PURITY parameter in a connection string. The valid values for POOL_PURITY are SELF and NEW. These values are not case-sensitive. The value NEW indicates that the application must use a new session. The value SELF allows the application to reuse both the pooled server process and session memory, giving maximum benefit from DRCP. By default, node-oracledb pooled connections use SELF and standalone connections use NEW.

The connection class can be specified in a connection string by setting the POOL_CONNECTION_CLASS parameter. The value for POOL_CONNECTION_CLASS can be any string conforming to connection class semantics and is case-sensitive.

An example of setting the connection class and pool purity in an Easy Connect syntax is shown below:

const pool = await oracledb.createPool({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb:pooled?pool_connection_class=MYAPP&pool_purity=self"
});

An example of setting the connection class and pool purity in a Connect Descriptor is shown below:

db_alias =
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=abc.oraclecloud.com))
      (CONNECT_DATA=(SERVICE_NAME=cdb1_pdb1.regress.rdbms.dev.us.oracle.com)(SERVER=POOLED)
      (POOL_CONNECTION_CLASS=cclassname)(POOL_PURITY=SELF)))

4.9.4. Monitoring DRCP

Data dictionary views are available to monitor the performance of DRCP. Database administrators can check statistics such as the number of busy and free servers, and the number of hits and misses in the pool against the total number of requests from clients. The views include:

• DBA_CPOOL_INFO

• V$PROCESS

• V$SESSION

• V$CPOOL_STATS

• V$CPOOL_CC_STATS

• V$CPOOL_CONN_INFO

DBA_CPOOL_INFO View

DBA_CPOOL_INFO displays configuration information about the DRCP pool. The columns are equivalent to the dbms_connection_pool.configure_pool() settings described in the table of DRCP configuration options, with the addition of a STATUS column. The status is ACTIVE if the pool has been started and INACTIVE otherwise. Note that the pool name column is called CONNECTION_POOL. This example checks whether the pool has been started and finds the maximum number of pooled servers:

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL              STATUS        MAXSIZE
---------------------------- ---------- ----------
SYS_DEFAULT_CONNECTION_POOL  ACTIVE             40

V$PROCESS and V$SESSION Views

The V$SESSION view shows information about the currently active DRCP sessions. It can also be joined with V$PROCESS through V$SESSION.PADDR = V$PROCESS.ADDR to correlate the views.

V$CPOOL_STATS View

The V$CPOOL_STATS view displays information about the DRCP statistics for an instance. The V$CPOOL_STATS view can be used to assess the efficiency of the pool settings. This example query shows an application using the pool effectively. The low number of misses indicates that servers and sessions were reused. The wait count shows just over 1% of requests had to wait for a pooled server to become available:

NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS
------------ ---------- ---------- ----------
       10031      99990         40       1055

If connectionClass was set (allowing pooled servers and sessions to be reused), then NUM_MISSES will be low. If the pool maxsize is too small for the connection load, then NUM_WAITS will be high.

V$CPOOL_CC_STATS View

The view V$CPOOL_CC_STATS displays information about the connection class level statistics for the pool per instance:

SQL> select cclass_name, num_requests, num_hits, num_misses
     from v$cpool_cc_stats;

CCLASS_NAME                      NUM_REQUESTS   NUM_HITS NUM_MISSES
-------------------------------- ------------ ---------- ----------
HR.MYCLASS                             100031      99993         38

The class name columns shows the database user name appended with the connection class name.

V$CPOOL_CONN_INFO View

The V$POOL_CONN_INFO view gives insight into client processes that are connected to the connection broker, making it easier to monitor and trace applications that are currently using pooled servers or are idle. This view was introduced in Oracle 11gR2.

You can monitor the view V$CPOOL_CONN_INFO to, for example, identify misconfigured machines that do not have the connection class set correctly. This view maps the machine name to the class name. In node-oracledb Thick mode, the class name will default to one as shown below:

SQL> select cclass_name, machine from v$cpool_conn_info;

CCLASS_NAME                             MACHINE
--------------------------------------- ------------
GK.OCI:SP:wshbIFDtb7rgQwMyuYvodA        gklinux

In this example, you would examine applications on gklinux and make them set connectionClass.

When connecting to Oracle Autonomous Database on Shared Infrastructure (ADB-S), the V$CPOOL_CONN_INFO view can be used to track the number of connection hits and misses to show the pool efficiency.