oracledb - Oracle's NodeJs Library

December 6,2016

In my last blog , I have shared how to develop Oracle Database REST web service using ORDS & APEX, and use it in Oracle JET. Now in this blog, I will share another alternative approach, where you can connect Oracle database from Oracle Jet without using ORDS and APEX, leveraging another Node.Js open source JavaScript library called oracledb. The node-oracledb add-on for Node.js powers high performance Oracle Database applications. Use node-oracledb to connect Node.js 0.10, 0.12, 4, and 6 to Oracle Database. The add-on is stable, well documented, and has a comprehensive test suite.The node-oracledb project is open source and maintained by Oracle Corp.Oracledb Java script library supports
  • Oracle Database connectivity using both internal & external Authentication, also supports Oracle wallet & SSL integration
  • SQL and PL/SQL execution
  • Promises, Callbacks and Streams
  • Large Objects: CLOBs and BLOBs
  • Oracle Database 12.1 JSON datatype
  • Query results as JavaScript objects or arrays
  • Smart mapping between JavaScript and Oracle types with manual override available
  • Data binding using JavaScript objects or arrays
  • Transaction Management
  • Inbuilt Connection Pool with Queuing
  • Database Resident Connection Pooling (DRCP)
  • Row Pre-fetching
  • Statement Caching
  • Client Result Caching
  • End-to-end Tracing, Mid-tier Authentication, and Auditing
  • Oracle Database High Availability Features
  • This is a good alternative for Oracle Developers working on Node Js platform.
  • Installation steps For both windows and Unix prerequisites are:
  • Python 2.7
  • C Compiler with support for C++ 11 (Xcode, gcc, Visual Studio or similar) and
  • After downloading Oracle Instant Client basic and sdk zips , extract them under same directory.
  • Set following environment variables after installation
    OCI_LIB_DIR = Oracle Instant Client root folder
    OCI_INC_DIR = Oracle Instant Client root folder\sdk\include
    Then run npm command to install Oracledb library
    $ npm install oracledb -g
    You are now all set to use this library. Many Javascript code examples are listed in following git page.
    Please find my sample code to invoke Item onhand data from Oracle E-business schema and use it in Oracle Jet (this code is for express js router, so add it in router/index.js file.
    var express = require('express');
    var router = express.Router();
    router.get('/invdata', function(req, res) {
    var oracledb = require('oracledb');
    var dbConfig = require('./dbconfig.js');\DBConfig File
    var crypto = require('crypto'), algorithm = 'aes-256-ctr', password = 'YOUR CIPHER KEY';
    function encrypt(text){
    var cipher = crypto.createCipher(algorithm,password)
    var crypted = cipher.update(text,'utf8','hex')
    crypted +='hex');
    return crypted;
    function decrypt(text){
    var decipher = crypto.createDecipher(algorithm,password)
    var dec = decipher.update(text,'hex','utf8')
    dec +='utf8');
    return dec;
    // Start Database Connections
    user : dbConfig.user,
    password : decrypt(dbConfig.password),
    connectString : dbConfig.connectString
    function(err, connection)
    if (err) {
    // The statement to execute
    'SELECT a.organization_id invorg, c.ORGANIZATION_CODE ou, a.subinventory_code subinv, a.inventory_item_id item, SUM (a.primary_transaction_quantity) onhand' +
    ',b.segment1 intemdesc FROM mtl_onhand_quantities_detail a, mtl_system_items_kfv b, org_organization_definitions c' +
    ' WHERE a.inventory_item_id = b.inventory_item_id AND a.organization_id = b.organization_id AND a.organization_id = c.organization_id' +
    ' AND a.inventory_item_id = :id GROUP BY a.organization_id,c.ORGANIZATION_CODE, b.segment1, a.subinventory_code, a.inventory_item_id',
    // The 'bind value' 0 for the 'bind variable' :id
    function(err, result)
    var invdata = '';
    var inventory = {};
    if (err) {
    else {
    for (var i = 0; i < result.rows.length; i++)
    inventory.invorg = result.rows[i][0];
    inventory.ou = result.rows[i][1];
    inventory.subinv = result.rows[i][2];
    inventory.item = result.rows[i][3];
    inventory.onhand = result.rows[i][4];
    inventory.itemdesc = result.rows[i][5];
    if (i == 1) {
    invdata = '[' + JSON.stringify(inventory);
    } else
    invdata = invdata + ',' + JSON.stringify(inventory);
    invdata = invdata + ']';
    // Note: connections should always be released when not needed
    function doRelease(connection)
    function(err) {
    if (err) {
    module.exports = router;
    The DB config file name is here dbconfig.js
    module.exports = {
    user : process.env.NODE_ORACLEDB_USER || 'your schema name - example: apps',
    // Instead of hard coding the password, consider prompting for it,
    // passing it in an environment variable via process.env, or using External Authentication.
    password : process.env.NODE_ORACLEDB_PASSWORD || 'your schema encrypted password using crypto cypher',
    // For information on connection strings see:
    connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || 'dbshotname:tnsport/dbsid',
    // Setting externalAuth is optional. It defaults to false. See:
    externalAuth : process.env.NODE_ORACLEDB_EXTERNALAUTH ? true : false
    You are now all set for calling the oracledb generated route REST webservice inside OracleJet view model code. I have earlier shared sample viewmodel js and view HTML code my last blog. In that example code change the calling function to execute this line
    var collection = new oj.Collection(null, {url: 'http://webhost:port/invdata/'})
    and create Oracle Jet View HTML accordingly. Now you are ready to view the data in Oracle Jet , invoke the view page and you will see following data

  • #REST,#oracledb,#NodeJS