Create a NodeJS Application to connect to Exadata Express

Anyone who knows me, knows I’m a big NodeJS geek, so I thought I’d see how easily I could create a NodeJS application to connect to my Exadata Express instance.

First of all, let’s create a basic Node application with the node-oracledb driver installed.


bash-3.2$ pwd
/Users/jes/Work/Node/oracle
bash-3.2$ npm init -y
Wrote to /Users/jes/Work/Node/oracle/package.json:

{
"name": "oracle",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}

The installation instructions for the Oracle Node driver are pretty comprehensive, but at a high level we need to –

  • Set OCI_LIB_DIR andOCI_INC_DIR environment variables to point to a valid instant client location
  • Run npm install oracledb

On my Mac I already have the 12c Instant Client installed –


bash-3.2$ export OCI_LIB_DIR=/Users/jes/tools/instantclient/instantclient_12_1
bash-3.2$ export OCI_INC_DIR=/Users/jes/tools/instantclient/instantclient_12_1/sdk/include

So I can just go ahead and run the npm install


bash-3.2$ npm install oracledb --save

> oracledb@1.13.1 install /Users/jes/Work/Node/oracle/node_modules/oracledb
> node-gyp rebuild

gyp WARN download NVM_NODEJS_ORG_MIRROR is deprecated and will be removed in node-gyp v4, please use NODEJS_ORG_MIRROR
gyp WARN download NVM_NODEJS_ORG_MIRROR is deprecated and will be removed in node-gyp v4, please use NODEJS_ORG_MIRROR
gyp WARN download NVM_NODEJS_ORG_MIRROR is deprecated and will be removed in node-gyp v4, please use NODEJS_ORG_MIRROR
CXX(target) Release/obj.target/oracledb/src/njs/src/njsOracle.o
CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o
CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o
CXX(target) Release/obj.target/oracledb/src/njs/src/njsResultSet.o
CXX(target) Release/obj.target/oracledb/src/njs/src/njsMessages.o
CXX(target) Release/obj.target/oracledb/src/njs/src/njsIntLob.o
../src/njs/src/njsIntLob.cpp:1711:3: warning: cannot refer to a non-static member from the handler of a constructor function try block
[-Wexceptions]
cleanup();
^
1 warning generated.
CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnv.o
CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnvImpl.o
../src/dpi/src/dpiEnvImpl.cpp:117:3: warning: cannot refer to a non-static member from the handler of a constructor function try block
[-Wexceptions]
cleanup();
^

<OUTPUT OMITTED>

clang: warning: libstdc++ is deprecated; move to libc++ with a minimum deployment target of OS X 10.9 [-Wdeprecated]
oracle@1.0.0 /Users/jes/Work/Node/oracle
└─┬ oracledb@1.13.1
└── nan@2.5.1

npm WARN oracle@1.0.0 No description
npm WARN oracle@1.0.0 No repository field.
bash-3.2$

I did get some warnings, but I’ve done this enough times to know that this successfully installed it.

Now lets create a basic Node application that connects to a local XE instance (just to check connectivity).

var oracledb = require('oracledb');

oracledb.getConnection({

user: 'jes',

password: 'password',

connectString: 'localhost:1521/xe'

},

function(err, connection) {

if (err) {

console.error(err.message);

return;

}

connection.execute(

// The statement to execute

"SELECT * "+

"FROM emp "+

"WHERE ename = 'SCOTT'",

[],

function(err, result) {

if (err) {

console.error(err.message);

doRelease(connection);

return;

}

console.log(result.metaData); //

console.log(result.rows); //

doRelease(connection);

});

});

function doRelease(connection) {

connection.close(

function(err) {

if (err) {

console.error(err.message);

}

});

}
this is a pretty simple Node / Oracle script (very similar to some of the examples you’ll find in the node driver documentation).
So let’s run our script to check it works ok –
bash-3.2$ node index.js
[ { name: 'EMPNO' },
{ name: 'ENAME' },
{ name: 'JOB' },
{ name: 'MGR' },
{ name: 'HIREDATE' },
{ name: 'SAL' },
{ name: 'COMM' },
{ name: 'DEPTNO' } ]
[ [ 7788,
'SCOTT',
'ANALYST',
7566,
1982-12-08T23:00:00.000Z,
3000,
null,
20 ] ]
Success! So we can connect to a local XE database fine.
But what about our Exadata Express instance?
The first step is to copy the client_credentials.zip file to a location your node script can access it, you should also unzip it.
bash-3.2$ ls -al
total 16
drwxr-xr-x 6 jes staff 204 15 May 12:39 .
drwxr-xr-x 13 jes staff 442 15 May 11:40 ..
drwxr-xr-x 3 jes staff 102 15 May 12:39 credentials
-rw-r--r-- 1 jes staff 816 15 May 12:04 index.js
drwxr-xr-x 4 jes staff 136 15 May 11:53 node_modules
-rw-r--r-- 1 jes staff 271 15 May 11:53 package.json</div>
<div></div>
<div>bash-3.2$ unzip credentials/client_credentials.zip
Archive: credentials/client_credentials.zip
extracting: cwallet.sso
extracting: sqlnet.ora
extracting: tnsnames.ora
extracting: ewallet.p12
extracting: keystore.jks
extracting: truststore.jks
 Note it’s really important that nobody else can access these files – otherwise they’ll be able to access your Exadata Express instance.
Now we need to edit the sqlnet.ora file to specify the path of the WALLET_LOCATION, so in my case it meant changing –
</div>
<div>WALLET_LOCATION = (SOURCE = (METHOD = file)
(METHOD_DATA = (DIRECTORY="?/network/admin")))</div>
<div>
to
</div>
<div>WALLET_LOCATION = (SOURCE = (METHOD = file)
(METHOD_DATA = (DIRECTORY="/Users/jes/Work/Node/oracle/credentials")))</div>
<div>
Now we need to export the TNS_ADMIN environment variable to this location
</div>
<div>export TNS_ADMIN=/Users/jes/Work/Node/oracle/credentials</div>
<div>
We can update our node script to reference the Exdata Express Instance
</div>
<div>
<div>
<div>var oracledb = require('oracledb');</div>
<div>oracledb.getConnection({</div>
<div>user: 'PDB_ADMIN',</div>
<div>password: '<MY PASSWORD>',</div>
<div>connectString: 'dbaccess'</div>
<div>},</div>
<div>function(err, connection) {</div>
<div>if (err) {</div>
<div>console.error(err.message);</div>
<div>return;</div>
<div>}</div>
<div>connection.execute(</div>
<div>// The statement to execute</div>
<div>"SELECT * "+</div>
<div>"FROM emp "+</div>
<div>"WHERE ename = 'SCOTT'",</div>
<div>[],</div>
<div>function(err, result) {</div>
<div>if (err) {</div>
<div>console.error(err.message);</div>
<div>doRelease(connection);</div>
<div>return;</div>
<div>}</div>
<div>console.log(result.metaData); //</div>
<div>console.log(result.rows); //</div>
<div>doRelease(connection);</div>
<div>});</div>
<div>});</div>
<div>function doRelease(connection) {</div>
<div>connection.close(</div>
<div>function(err) {</div>
<div>if (err) {</div>
<div>console.error(err.message);</div>
<div>}</div>
<div>});</div>
<div>}</div>
<div>
Note the only difference here is that I’ve changed the username to be PDB_ADMIN (together with the correct password) and also changed the connectString to be dbaccess (which is referenced from the client_credentials.zip file).
Now let’s try our script again –
bash-3.2$ node index.js
[ { name: 'EMPNO' },
{ name: 'ENAME' },
{ name: 'JOB' },
{ name: 'MGR' },
{ name: 'HIREDATE' },
{ name: 'SAL' },
{ name: 'COMM' },
{ name: 'DEPTNO' } ]
[ [ 7788,
'SCOTT',
null,
7566,
1982-12-08T23:00:00.000Z,
null,
null,
20 ] ]
bash-3.2$
Success!
Now I’m sure you might say – that’s just the same output, let’s verify by changing the query in our script to –
SELECT host_name from v$instance
now we get
bash-3.2$ node index.js
[ { name: 'HOST_NAME' } ]
[ [ '<intentionally obscured>.nldc1.oraclecloud.com' ] ]
bash-3.2$
So we are 100% accessing the Exadata Express instance on Oracle Cloud.
I’m very impressed with how simple Oracle have made it to connect the different client tools to the Oracle Cloud, this is really low barrier to entry and I can see many more client applications moving to using Oracle Cloud as a backend service.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s