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.

 

 

 

Running a Web Server on Oracle Compute Instance

Every website sits on a computer which is basically as a Web server. A web server processes requests via HTTP(s) and other related protocols.

There are several web servers out there, but i shall focus on installing Apache HTTP server on Oracle Linux and Ubuntu operating systems running on Oracle compute instances.

Prerequisites:

  • Oracle Cloud Infrastructure compute instance
  • SSH Client ie. PuTTY
  1. Installing Apache HTTP on Oracle Linux.
Install Apache http
sudo yum install httpd -y

Start Apache server and configure it to start at system reboots

sudo apachectl start
sudo systemctl enable httpd

Check if the Apache configuration syntax is correct, run:

sudo apachectl configtest

Now create firewall rule to access all ports that HTTP listens to.

sudo firewall-cmd --permanent --zone=public --add-service=http

sudo firewall-cmd --reload

Find the web root directory for your web server and add your web files. ( Found at the “/var/www/html” folder)

One more thing!

Open port 80 in the security lists of your compute instance. 

Navigate to your compute instance and select your VCN.

Click on security lists on the left bar under resources and select the default security list for your VCN.

Add an ingress rule for port 80, edit the values as shown on the image below.

Source Type: CIDR
Source CIDR: 0.0.0.0/0
IP Protocol: TCP
Source Port Range: All
Destination Port Range: 80

Click on Save Security List Rules at the bottom.

ingress

Almost done now…

Create an Egress rule to allow traffic for all ports.

egress

All done!

To test open your  favorite web browser and navigate to the public IP address of the Linux VM.

http://Public-IPAddress/your-web-file.index.html

webpage

I have configured DNS to resolve to my public IP address on my Oracle compute instance. I will show you how to do that on the next post 🙂

It works!

2. Installing Apache HTTP server on Ubuntu

The whole process of configuring the compute instance security lists is the same as above, only the commands for installing Apache on Ubuntu change a little bit.

Log in to your compute instance running on Ubuntu using SSH.

Search the Apache package:

apt-cache search apache

Install the Apache2 package using the root privileges.

sudo apt-get install apache2

Where is the Apache HTTP server installed?

To find out,  run the find command:

sudo find / -name apache2

find

Navigate to your web root folder and add your web files. (Found at /var/www/html)

Test out your web browser using your favorite browser.

http://Public-IPAddress/your-web-file.index.html

Final Thoughts:

Apache HTTP server is an open source cross platform web server software which can can also act as an application server. There are several other web servers out there ie. Nginx, IIS etc.

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.

Run Node.js Applications on Oracle Cloud Infrastructure using PM2

Prerequisites:

What is PM2?

Pm2 is a production process manager for Node.js applications with built-in load balancer. It allows you to keep you applications alive forever, reloads them at zero downtime. It’s simple to use and makes managing a production environment seamless.

Starting a node app in pm2 is as easy as;

$ pm2 start app.js

Installing PM2 on Oracle Compute;

First, you need to take care of your firewall and open the necessary ports.

Login to your compute instance on OCI through cmd and open the ports you want to use using the firewalld command.

First install firewalld (If not yet installed)

 sudo yum install firewalld

Next expose port you want to use ie.5001 to the public to allow in-bound web traffic via HTTP. (Adding it on the public zone)

sudo firewall-cmd --zone=public --add-port=5001/tcp --permanent

Reload the firewalld – you can even check the features enabled on the public zone.

sudo firewall-cmd --reload
sudo firewall-cmd --info-zone public

node1

Now that we have exposed port 5001 to the public, lets now install PM2. It’s a piece of cake! Run;

npm install pm2 -g

On your node application, specify the port you exposed.

node2

Run your app using pm2 start command (My node app is saved as app.js)

pm2 start app.js

node3

You can now test your Node.js app on your browser using the public IP address and the port you exposed;

 <your compute instance public IP address>: <port>

node4

It works! Note that you can run more than one Node.js application using PM2, just expose them in different ports.

Finally a few other PM2 commands and resources to keep you going.

pm2 ls — Show a list of all applications
pm2 stop <app> — Stops a specific application
pm2 start <app> — Starts a specific application
pm2 <app> scale N — Scales the application you specify to N number of instances (can be used to scale up or down)
pm2 kill — Kills all running applications
pm2 restart — Restarts all running applications
pm2 reload — Reloads the app configuration
pm2 monit -will return a rich set of data around your application’s health
pm2 logs — Outputs logs from all running applications
pm2 logs app — Outputs logs from only the app application
pm2 flush — Flushes all log data, freeing up disk space

Final thoughts;

Pm2 is an amazing open source project. Many thanks to everyone putting all the resources out there!

If you need any support feel free to reach out, or if you have any additional tips and tricks, feel free to share with me!

Here are additional resources and references:

 

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

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

 

 

Build Custom Service Components for the Oracle Digital Assistant in Minutes!

Hello there,

I will not keep you with a long post. You need to be familiar with the Oracle Digital Assistant to try out this.

To implement custom components, you use the Oracle Digital Assistant Node.js SDK to interface with Digital Assistant’s custom component service. You can then deploy to the Digital Assistant embedded container, a Mobile Hub backend, or a Node.js server.

Here i will explain deploying on Digital Assistant Embedded container.

Prerequisites:

  • Node Package Manager, Node version 8.x or lower
  • Access to Oracle Digital Assistant 18.4.3+

1. Install node globally on your windows machine;

npm install -g @oracle/bots-node-sdk

2. Create a directory on windows ie. helloworld

3. Enter in the directory using cmd (terminal)

3. Initialize new node project inside the folder ie. helloworld

npm init -y

4. Install Node SDK locally

npm install --save-dev @oracle/bots-node-sdk

5. Create a custom component with a custom name. ie LaHelloWorld

bots-node-sdk init -c LaHelloWorld

6. Navigate out of the folder.

cd ..

7. Start node server and test the custom component just created.

bots-node-sdk service helloworld

8. Open the browser and test the URL given on cmd

Capture

9. Crt + C to cancel the node server process

10. Navigate back to the folder

cd helloworld

11. Create a package to deploy.

npm pack

12. Go to the skills on Oracle Digital Assistant ie. LN_customc101

(Download and import one I  created on here)

13. Open it and go to custom component service menu

14. Click add a service

15. Give it a name ie. Helloworld and select Embedded container.

16. Upload the packaged helloworld1.x.0.0.tgz packaged earlier.

17. Click create and wait for it to be deployed.

Capture1

18. Test your work! It works! 🙂

Capture2

 

Check out this code on Github

Learn more | Useful resources, check out;