3. Initializing node-oracledb
By default, node-oracledb runs in a ‘Thin’ mode which connects directly to Oracle Database. This mode does not need Oracle Client libraries. However, when the driver does use these libraries to communicate to Oracle Database, then node-oracledb is said to be in ‘Thick’ mode and has additional functionality available. See node-oracledb Thick Mode Architecture for the architecture diagram.
3.1. Enabling node-oracledb Thick Mode
To change from the default Thin mode to the Thick mode:
Oracle Client libraries must be available. These need to be installed separately, see Installing node-oracledb.
Various versions of Oracle Client libraries can be used. They do not have to match the version of Oracle Database. Node-oracledb can use the Client Libraries from:
an installation of Oracle Instant Client
or a full Oracle Client installation (installed by running the Oracle Universal installer
runInstaller
)or an Oracle Database installation, if Node.js is running on the same machine as the database
Your application must call the synchronous function
oracledb.initOracleClient()
, for example:const oracledb = require('oracledb'); let clientOpts = {}; if (process.platform === 'win32') { // Windows // If you use backslashes in the libDir string, you will // need to double them. clientOpts = { libDir: 'C:\\oracle\\instantclient_19_19' }; } else if (process.platform === 'darwin' && process.arch === 'x64') { // macOS Intel clientOpts = { libDir: process.env.HOME + '/Downloads/instantclient_19_8' }; } // else on other platforms like Linux the system library search path MUST always be // set before Node.js is started, for example with ldconfig or LD_LIBRARY_PATH. // enable node-oracledb Thick mode oracledb.initOracleClient(clientOpts);
More details and options are shown in the later sections Enabling node-oracledb Thick Mode on Windows, Enabling node-oracledb Thick Mode on macOS, and Enabling node-oracledb Thick Mode on Linux and Related Platforms.
All connections in an application use the same mode.
Once the Thick mode is enabled, you cannot go back to the Thin mode except by
removing calls to initOracleClient()
and restarting the
application.
See Finding the node-oracledb Mode to verify which mode is in use.
Notes on calling initOracleClient()
The
initOracleClient()
function must be called before any standalone connection or connection pool is created. If a connection or pool is first created, then the Thick mode cannot be enabled.If you call
initOracleClient()
with alibDir
attribute, the Oracle Client libraries are loaded immediately from that directory. If you callinitOracleClient()
but do not set thelibDir
attribute, the Oracle Client libraries are loaded immediately using the search heuristics discussed in later sections.If Oracle Client libraries cannot be loaded, then
initOracleClient()
will return an errorDPI-1047: Cannot locate a 64-bit Oracle Client library
. To resolve this, review the platform-specific instructions below. Alternatively, remove the call toinitOracleClient()
and use Thin mode. The features supported by Thin mode can be found in Oracle Database Features Supported by node-oracledb.If you set
libDir
on Linux and related platforms, you must still have configured the system library search path to include that directory before starting Node.js.On any operating system, if you set
libDir
to the library directory of a full database or full client installation (such as from runningrunInstaller
), you will need to have previously set the Oracle environment, for example by setting theORACLE_HOME
environment variable. Otherwise you will get errors likeORA-1804
. You should set this variable, and other Oracle environment variables, before starting Node.js, as shown in Oracle Environment Variables.The
initOracleClient()
function may be called multiple times in your application but must always pass the same arguments.
3.1.1. Enabling node-oracledb Thick Mode on Windows
On Windows, the alternative ways to enable Thick mode are:
By passing the libDir parameter in a call to
initOracleClient()
, for example:const oracledb = require('oracledb'); oracledb.initOracleClient({libDir: 'C:\\oracle\\instantclient_19_18'});
If you use backslashes in the
libDir
string, you will need to double them.This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package. If you pass the library directory from a full client or database installation, such as Oracle Database “XE” Express Edition, then you will need to have previously set your environment to use that software installation otherwise files such as message files will not be located.
Alternatively, copy the Oracle Instant Client libraries to the
node_modules/oracledb/build/Release
directory where theoracledb*.node
binary is. This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package.Update your application to call
oracledb.initOracleClient()
which enables Thick mode:const oracledb = require('oracledb'); oracledb.initOracleClient();
Alternatively, add the Oracle Client library directory to the
PATH
environment variable. If you are running Node.js on the same machine as your database, and node-oracledb can therefore use the client libraries that are available in the Oracle Database software, this variable may already be set correctly. The directory must occur inPATH
before any other Oracle directories. Restart any open command prompt windows.Update your application to call
oracledb.initOracleClient()
which enables Thick mode:const oracledb = require('oracledb'); oracledb.initOracleClient();
Alternatively, use
SET
to change yourPATH
in each command prompt window before you run node.Another way to set the
PATH
variable is to use a batch file that sets this variable before Node.js is executed, for example:REM mynode.bat SET PATH=C:\oracle\instantclient_19_18;%PATH% node %*
Invoke this batch file every time you want to run Node.js.
Update your application to call
oracledb.initOracleClient()
which enables Thick mode:const oracledb = require('oracledb'); oracledb.initOracleClient();
3.1.2. Enabling node-oracledb Thick Mode on macOS
On macOS, the alternative ways to enable Thick mode are:
By passing the libDir parameter in a call to
initOracleClient()
.const oracledb = require('oracledb'); oracledb.initOracleClient({libDir: process.env.HOME + '/Downloads/instantclient_19_8'});
This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package.
Alternatively, you can call
initOracleClient()
without passing alibDir
parameter.const oracledb = require('oracledb'); oracledb.initOracleClient();
In this case, the Oracle Client libraries are first looked for in the directory where the
oracledb*.node
binary is. For example innode_modules/oracledb/build/Release
. This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package. For example, useln -s ~/Downloads/instantclient_19_8/libclntsh.dylibnode_modules/oracledb/build/Release/
.If the libraries are not found, the library search path such as set in
DYLD_LIBRARY_PATH
(note this variable does not propagate to sub-shells) or files in/usr/local/lib
may be used.Alternatively, create a symbolic link for the ‘client shared library’ in the
node_modules/oracledb/build/Release
directory where theoracledb*.node
binary is. For example:ln -s ~/Downloads/instantclient_19_8/libclntsh.dylib node_modules/oracledb/build/Release
This can be added to your
package.json
files:"scripts": { "postinstall": "ln -s $HOME/Downloads/instantclient_19_8/libclntsh.dylib $(npm root)/oracledb/build/Release" },
Instead of linking, you can also copy all the required OCI libraries, for example:
cp ~/Downloads/instantclient_19_8/{libclntsh.dylib.19.1,libclntshcore.dylib.19.1,libnnz19.dylib,libociei.dylib} node_modules/oracledb/build/Release cd node_modules/oracledb/build/Release/ && ln -s libclntsh.dylib.19.1 libclntsh.dylib
With the libraries in place, your application can then enable Thick mode:
const oracledb = require('oracledb'); oracledb.initOracleClient();
Alternatively, create a symbolic link for the ‘client shared library’ in
/usr/local/lib
. Note this may not work on all versions of macOS. If thelib
sub-directory does not exist, you can create it. For example:mkdir /usr/local/lib ln -s ~/Downloads/instantclient_19_8/libclntsh.dylib /usr/local/lib
Instead of linking, you can also copy all the required OCI libraries, for example:
mkdir /usr/local/lib cp ~/Downloads/instantclient_19_8/{libclntsh.dylib.19.1,libclntshcore.dylib.19.1,libnnz19.dylib,libociei.dylib} /usr/local/lib/
With the libraries in place, your application can then enable Thick mode:
const oracledb = require('oracledb'); oracledb.initOracleClient();
3.1.4. Tracing Oracle Client Libraries Loading
To trace the loading of Oracle Client libraries, the environment
variable DPI_DEBUG_LEVEL
can be set to 64 before starting Node.js.
For example, on Linux, you might use:
$ export DPI_DEBUG_LEVEL=64
$ node myapp.js 2> log.txt
On Windows you might set the variable like:
set DPI_DEBUG_LEVEL=64
3.2. Optional Oracle Configuration Files
3.2.1. Optional Oracle Net Configuration Files
Optional Oracle Net configuration files may be read by node-oracledb. These files affect connections and applications. The common files are:
Name |
Description |
---|---|
|
Contains Oracle Net Service names and Oracle Net options for databases that can be connected to, see Net Service Names for Connection Strings. This file is only needed for advanced configuration. Not needed if connection strings use the Easy Connect syntax. The Oracle Net documentation on tnsnames.ora has more information. |
|
A configuration file controlling the network transport behavior. For example it can set call timeouts for high availability, or be used to encrypt network traffic, or be used to configure logging and tracing. The Oracle Net documentation on sqlnet.ora has more information. Many settings can alternatively be specified using Easy Connect syntax This file is only used in node-oracledb Thick mode. In the node-oracledb Thin mode, many settings can be defined in Easy Connect syntax, in |
The documentation Connections and High Availability discusses some specific Oracle Net configuration options useful for node-oracledb applications.
See Using Optional Oracle Configuration Files to understand how node-oracledb locates the files.
3.2.2. Optional Oracle Client Configuration File
If the Oracle Client Libraries used by node-oracledb Thick mode are version 12, or later, then an optional oraaccess.xml file can be used to configure some behaviors of those libraries, such as statement caching and prefetching. This can be useful if the application cannot be altered. The file is read when node-oracledb starts. The file is read from the same directory as the Optional Oracle Net Configuration files.
Note
The oraaccess.xml
files is only used in the node-oracledb Thick mode.
See Enabling node-oracledb Thick Mode.
The following oraaccess.xml
file sets the Oracle client
‘prefetch’ value to 1000 rows. This value
affects every SQL query in the application:
<?xml version="1.0"?>
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<prefetch>
<rows>1000</rows>
</prefetch>
</default_parameters>
</oraaccess>
Prefetching is a tuning feature, see Tuning Fetch Performance.
The oraaccess.xml
file has other uses including:
Changing the value of Fast Application Notification (FAN) events which affects notifications and Runtime Load Balancing (RLB).
Configuring Client Result Caching parameters
Turning on Client Statement Cache Auto-tuning
Refer to the documentation on oraaccess.xml for more information.
See Using Optional Oracle Configuration Files to understand how node-oracledb locates the file.
3.2.3. Using Optional Oracle Configuration Files
If you use optional Oracle configuration files such as tnsnames.ora
,
sqlnet.ora
or oraaccess.xml
, then put the files in an accessible
directory and follow the Thin or Thick mode instructions below.
The files should be in a directory accessible to Node.js, not on the database server host.
For node-oracledb Thin mode
In node-oracledb Thin mode, you must specify the directory that contains the
tnsnames.ora
file by either:
Setting the TNS_ADMIN environment variable to the directory containing the file and then running your application. For example, in a terminal:
export TNS_ADMIN=/opt/oracle/your_config_dir node myapp.js
Or setting the
configDir
attribute to the directory containing the file whenconnecting
or creating aconnection pool
. For example:const oracledb = require('oracledb'); async function run() { const connection = await oracledb.getConnection({ user : "hr", password : mypw, // contains the hr schema password connectString : "myhost/FREEPDB1", configDir : "/opt/oracle/your_config_dir" });
On Windows, if you use backslashes in the configDir
string, you will need
to double them.
Note
In Thin mode, you must explicitly set the directory because traditional
“default” locations such as the Instant Client network/admin/
subdirectory, $ORACLE_HOME/network/admin/
, or
$ORACLE_BASE/homes/XYZ/network/admin/
(in a read-only Oracle Database
home) are not automatically looked in.
For node-oracledb Thick mode
In node-oracledb Thick mode, the directory containing the optional files can be explicitly specified or a default location will be used. Do one of:
Set the configDir attribute to the directory containing the files when enabling Thick mode with
initOracleClient()
:const oracledb = require('oracledb'); oracledb.initOracleClient({configDir: '/opt/oracle/your_config_dir'});
On Windows, if you use backslashes in the
configDir
string, you will need to double them.If
initOracleClient()
is called to enable Thick mode but configDir is not specified, then default directories are searched. They include:The directory specified by the TNS_ADMIN environment variable.
For Oracle Instant Client ZIP files, the
network/admin
subdirectory of Instant Client, for example/opt/oracle/instantclient_19_11/network/admin
.For Oracle Instant RPMs, the
network/admin
subdirectory of Instant Client, for example/usr/lib/oracle/19.18/client64/lib/network/admin
.When using libraries from a local Oracle Database or full client installation, in
$ORACLE_HOME/network/admin
or$ORACLE_BASE_HOME/network/admin
.
3.2.4. Using the Optional Time Zone File
The name of the Oracle time zone file to use can be set in
ORA_TZFILE
.
Note
The Oracle time zone file and ORA_TZFILE
environment variable are only
used in the node-oracledb Thick mode.
If node-oracledb is using Oracle Client libraries from an Oracle
Database or full Oracle Client software installation, and you want to
use a non-default time zone file, then set ORA_TZFILE
to the file
name with a directory prefix, for example:
export ORA_TZFILE=/opt/oracle/myconfig/timezone_31.dat
.
Oracle Instant Client includes embedded small and big time zone ‘files’,
for example timezone_32.dat
and timezlrg_32.dat
. The versions
can be shown by running the utility genezi -v
located in the Instant
Client directory. The small file contains only the most commonly used
time zones. By default the larger timezlrg_n.dat
file is used. If
you want to use the smaller timezone_n.dat
file, then set the
ORA_TZFILE
environment variable to the name of the file without any
directory prefix, for example export ORA_TZFILE=timezone_32.dat
.
With Oracle Instant Client 12.2 or later, you can also use an external
time zone file. Create a subdirectory oracore/zoneinfo
under the
Instant Client directory, and move the file into it. Then set
ORA_TZFILE
to the file name, without any directory prefix. The
genezi -v
utility will show the time zone file in use.
The Oracle Database documentation contains more information about time zone files, see Choosing a Time Zone File.
3.3. Oracle Environment Variables for node-oracledb Thick Mode
Some common environment variables that influence node-oracledb in Thick mode
are shown below. The variables that may be needed depend on how Node.js is
installed, how you connect to the database, and what optional settings are
desired. It is recommended to set Oracle variables in the environment before
invoking Node.js, however they may also be set in application code as long as
they are set before node-oracledb is first used. System environment variables
like LD_LIBRARY_PATH
must be set before Node.js starts.
Note
The variables listed below are only supported in the node-oracledb Thick
mode, with the exception of the TNS_ADMIN
and ORA_SDTZ
which
are also supported in the node-oracledb Thin mode.
Oracle Environment Variables |
Purpose |
---|---|
|
The library search path for Linux and some UNIX platforms. Set this to the directory containing the Oracle Client libraries, for example |
|
The library search path for Windows should include the location where |
|
The location of the optional Oracle Net configuration files and Oracle Client configuration files, including |
|
The default session time zone, see Fetching Dates and Timestamps. |
|
The name of the Oracle time zone file to use. See the notes below. |
|
The directory containing the Oracle Database software. This directory must be accessible by the Node.js process. This variable should not be set if node-oracledb uses Oracle Instant Client. |
|
Determines the ‘national language support’ globalization options for node-oracledb. If not set, a default value will be chosen by Oracle. Note that node-oracledb will always uses the AL32UTF8 character set. See Globalization and National Language Support (NLS). |
|
See Fetching Numbers and Dates as String. The variables are ignored if |
|
See Fetching Numbers and Dates as String. The variables are ignored if |
3.3.1. Scripts for Setting the Default Environment in a Database Installation
If you are using Linux, and node-oracledb is being run on the same
computer as the database, you can set required Oracle environment
variables, such as ORACLE_HOME
and LD_LIBRARY_PATH
in your shell
by executing:
source /usr/local/bin/oraenv
Or, if you are using Oracle Database XE 11.2, by executing:
source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
Make sure the Node.js process has directory and file access permissions for the Oracle libraries and other files. Typically the home directory of the Oracle software owner will need permissions relaxed.
Note
The ORACLE_HOME
and LD_LIBRARY_PATH
environment variables are only
used in the node-oracledb Thick mode.
3.4. Other node-oracledb Thick Mode Initialization
The oracledb.initOracleClient()
function allows
driverName and
errorUrl attributes to be set.
These are useful for applications whose end-users are not aware
node-oracledb is being used. An example of setting the attributes is:
const oracledb = require('oracledb');
oracledb.initOracleClient({
driverName: 'My Great App : 3.1.4'
errorUrl: 'https://example.com/MyInstallInstructions.html',
});
The driverName
value will be shown in Oracle Database views like
V$SESSION_CONNECT_INFO
. The convention for driverName
is to
separate the product name from the product version by a colon and single
space characters. If this attribute is not specified, then the value
“node-oracledb thk : version” is used, see Finding the node-oracledb Mode.
The errorUrl
string will be shown in the exception raised if
initOracleClient()
cannot load Oracle Client libraries.
This allows applications that use node-oracledb in Thick mode to refer users
to application-specific installation instructions. If this attribute is not
set, then the node-oracledb installation instructions
are used.