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
  • REF CURSORs
  • 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.
    https://github.com/oracle/node-oracledb/tree/master/examples
    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 += cipher.final('hex');
    return crypted;
    }
    function decrypt(text){
    var decipher = crypto.createDecipher(algorithm,password)
    var dec = decipher.update(text,'hex','utf8')
    dec += decipher.final('utf8');
    return dec;
    }
    // Start Database Connections
    oracledb.getConnection(
    {
    user : dbConfig.user,
    password : decrypt(dbConfig.password),
    connectString : dbConfig.connectString
    },
    function(err, connection)
    {
    if (err) {
    console.error(err.message);
    return;
    }
    connection.execute(
    // 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
    [151],
    function(err, result)
    {
    var invdata = '';
    var inventory = {};
    if (err) {
    console.error(err.message);
    doRelease(connection);
    return;
    }
    else {
    //console.log(result.metaData);
    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 + ']';
    doRelease(connection);
    res.send(invdata);
    });
    });
    // Note: connections should always be released when not needed
    function doRelease(connection)
    {
    connection.close(
    function(err) {
    if (err) {
    console.error(err.message);
    }
    });
    }
    });
    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:
    // https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connectionstrings
    connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || 'dbshotname:tnsport/dbsid',
    // Setting externalAuth is optional. It defaults to false. See: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#extauth
    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