Connect to Oracle ATP database using Node.js

In this week series I will explore the capabilities of Oracle Autonomous Transaction Processing (ATP) database beyond connecting to SQL developer.

There are several ways to achieve this, however this is the simplest way to show the capabilities/possibilities that can be attained. 

Prerequisites:

  • Node.js installed in your computer
  • node-oracledb library
  • Oracle Instant Client
  • Oracle ATP database

Installing the Oracle Instant Client in Windows OS

We need the Oracle Instant client to connect and run remote Oracle databases in Node.js.

Download and install the Oracle Instant Client.

Unzip the package into a single directory ie. C:\oracle\instantclient_18_5

Set the environment variable PATH to include the path that you created.

Download the ATP Database connection Wallet.

Log in to your ATP database and download your credential wallet. This contains your connection information to your Oracle ATP database.

ATP1

Extract the wallet files in a given folder. Mine are in:

C:\wallets

We need to update the sqlnet.ora & ojdbc.properties  files in the wallet folder to reflect the location of the wallet.

In ojdbc.properties file:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="c:\wallets")))

sqlnet.ora:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="c:\wallets")))
SSL_SERVER_DN_MATCH=yes

Set the TNS_ADIMN variable:

Since we are using Node.js we can define the Environment variables in an .env configurations file many thanks to the custom-env node library.

ATP2

Install these Node.js libraries to help you run the connection. (Each of the libraries below has their specific functions- We shall see on a later post)

npm install oracledb
npm install async
npm install app
npm install express

You can create a simple testconnection.js file to confirm that the connection to ATP database is working. This file requires an .env configurations file and the dbconfig.js file.

require ('custom-env').env('stagging') //find the .env.stagging file and place the right location of your wallet

console.log(process.env.TNS_ADMIN)

var oracledb = require('oracledb');

var dbConfig = require('./dbconfig.js');

let error;

let user;

oracledb.getConnection({

user: dbConfig.dbuser,

password: dbConfig.dbpassword,

connectString: dbConfig.connectString

},

function(err, connection) {

if (err) {

error = err;

return;

}

connection.execute('select user from dual', [], function(err, result) {

if (err) {

error = err;

return;

}

user = result.rows[0][0];

console.log('Connection test succeeded. You connected to ATP as ' + user + '!');

error = null;

connection.close(function(err) {

if (err) {

console.log(err);

}

});

})

}

);
The dbconfig.js file:
 
module.exports= {

dbuser: 'admin',

dbpassword: 'Your Password',

connectString: 'YourDatabaseName_TP'

}

The .env configurations file helps you load the Node.js app environment variable configurations on different environments. ie. on my .env.stagging file i have:

TNS_ADMIN='c:\wallets' 

Run the testconnection.js file:

node testconnection.js 

ATP3

We succeeded in connecting to the ATP database using Node.js.

Installing the Oracle Instant Client in Oracle Linux

Download a “Basic” or “Basic Light” zip file matching your architecture.

Unzip the package in the folder that is accessible to your application

mkdir -p /opt/oracle
cd /opt/oracle
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip

Install the libaio package as root.

sudo yum install libaio

If there is no other Oracle software on the machine that will be impacted, permanently add Instant Client to the run time link path

sudo sh -c "echo /opt/oracle/instantclient_18_3 > /etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig

Else set an environment variable LD_LIBRARY_PATH   to the directory of the Instant Client.

export LD_LIBRARY_PATH=/opt/oracle/instantclient_18_3:$LD_LIBRARY_PATH

Co locate the ATP connection wallet with the Instant client, create a network/admin subdirectory if it does not exist.

mkdir -p /opt/oracle/instantclient_12_2/network/admin

Edit the sql.ora file to reflect the directory “?/network/admin”

linux1

Load your Node.js files as shown previously. Test to see if your connection worked!

linux2

On the next post we shall explore how we can use this ability to connect an Oracle Digital Assistant with ATP database using a custom component.

References:

 

Thank you and Happy coding! If you liked it, share it!

This blog reflects  my own thoughts and doesn’t reflect the thoughts of my employer.

 

 

 

Author: Labanish

Through embracing an agile mindset, and an attitude of learning, unlearning and re-learning, with my teachers being experience, customers and colleagues, I have attained vast knowledge in solution engineering. My technical skill set ranges from; Oracle APEX, Node.js, Machine Learning, Python, Low Code development, Integration, Data Management, Digital Assistants and Analytics. Feel free to reach out to me for more info.

One thought on “Connect to Oracle ATP database using Node.js”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s