Skip navigation.

Christopher Jones

Syndicate content
Notes on the Scripting Languages and Oracle Database. I cover Node.js, PHP, Python, Ruby, Perl, Apache, Linux
Updated: 13 hours 26 min ago

node-oracledb 1.4.0 supports Node 0.10, 0.12, 4.2 and 5 (Node.js add-on for Oracle Database)

Mon, 2015-11-16 23:35

Version 1.4 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM.

Since the recent releases of Node 4 LTS and Node 5, after the reconciliation and merge of the io.js and Node.js forks, there has been strong interest in a compatible node-oracledb driver. This is now possible. Node-oracledb 1.4 works with Node.js 0.10, 0.12, 4.2, and 5.0. Thanks to everyone for their perseverance.

The code change making this possible was a patch contributed by Richard Natal bumping the dependency on NAN from v1 to v2. Note: a compiler with support for C++11 is required to build with Node 4.2 and 5. (Oracle Linux 6 users will need to move to Oracle Linux 7 or install a newer compiler, such as from the Software Collection Library for Oracle Linux).

Other changes in this release are:

  • Fixed a statement cursor leak occuring when statements failed.

  • Fixed a crash accessing Pool properties on Windows.

  • A new testWindows target to help run the tests on Windows was added to package.json. Instructions on how to run tests are in test/

  • Fixed compilation warnings seen on some platforms with newer compilers.

Issues and questions about node-oracledb can be posted on GitHub.

node-oracledb installation instructions are here.

node-oracledb documentation is here.

Application Development with Node.js, Python, PHP, R, C, and C++ at OOW

Thu, 2015-10-22 00:18
The huge Oracle OpenWorld conference in San Francisco starts in a few days. We've put together some lists of sessions that we think app developers will be interested in.

These lists should help you work out a great schedule!

Look out for the talks Enterprise App Development with Node.js, Python, and PHP for Oracle Database 12c (Monday), Oracle Net Services 12c: Best Practices for Database Performance and Scalability (Tuesday), Meet the Oracle Programming and Scripting Experts (Tuesday), Best Practices for Application Performance and Scalability (Wednesday), and Best Practices for Application High Availability on Thursday.

If you want to explore what else is happening, look at the OOW Content Catalog

Article on installing node-oracledb on Windows

Wed, 2015-10-21 23:14
OTN has posted a detailed article on Installing node-oracledb on Microsoft Windows by Bill Christo (@bchr02). Check it out!

You may also be interested in the research Bill is doing into pre-creating binaries so you don't need to compile yourself:

node-oracledb 1.3.0 is on NPM (Node.js add-on for Oracle Database)

Thu, 2015-10-15 00:24

Version 1.3 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM

We kept the changes minimal in this release for several reasons. It has two small enhancements and a few bug fixes.

  • A new oracledb.oracleClientVersion attributes gives the version of the Oracle client libraries that node-oracledb is linked with.

    A connection.oracleServerVersion attribute gives the Oracle Database version used by the connection.

    These attributes are handy for code that needs to run in multiple environments. See examples/version.js for usage.

  • The major bug fix resolves some corruption with result.outBinds when calling PL/SQL blocks. This was sometimes causing a crash.

See CHANGELOG for the other changes.

Issues and questions about node-oracledb can be posted on GitHub.

node-oracledb installation instructions are here.

node-oracledb documentation is here.

node-oracledb 1.2.0 is on NPM (Node.js add-on for Oracle Database)

Fri, 2015-09-25 08:28

Version 1.2 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM

A lot of good changes have been made.

Our thanks to Bruno Jouhier from Sage for his work on adding RAW support and for fixes for LOB stability. Thanks also go to Bill Christo for pushing us on some Installation topics - look out for his full article on Windows Installation that OTN will be publishing soon.

An annotated list of the changes in this releases are:

  • Added support for RAW data type.

    Bruno contributed a patch to add support for the Oracle RAW datatype. This data type maps to a JavaScript Buffer for inserts, queries and for binding to PL/SQL. Binding RAW for DML RETURNING is not supported. There is an example showing inserting and querying in examples/raw1.js

  • Added a type property to the Lob class to distinguish CLOB and BLOB types.

    This small change will allow introspection on Lob instances so applications can more easily decide how to handle the data.

  • Changed write-only attributes of Connection objects to work with console.log().

    The Connection object had three write-only attributes (action, module, clientId) used for end-to-end tracing and mid-tier authentication. Because they were write-only, anyone doing a simple console.log() on the connection object got a confusing message often leading to the impression that connection had failed. The attributes are write-only for the reasons described in the documentation. With the change in v1.2, a Connection object can now be displayed. The three attributes will show as null (see the doc) while the non- write-only attribute stmtCacheSize will show an actual value. With hindsight the three attributes should have be set via a setter, but they aren't.

  • Added a check to make sure maxRows is greater than zero for non-ResultSet queries.

    If you want to get metaData for a query without getting rows, specify resultSet:true and prefetchRows:0 in the query options (and remember to close the ResultSet).

  • Improved installer messages for Oracle client header and library detection on Linux, OS X and Solaris.

    Some upfront checks now aid detection of invalid environments earlier.

  • Optimized CLOB memory allocation to account for different database-to-client character set expansions.

    In line with the optimization for string buffers in v1.1, users of AL32UTF8 databases will see reduced memory consumption when fetching CLOBs.

  • Fixed a crash while reading a LOB from a closed connection

  • Fixed a crash when selecting multiple rows with LOB values.

    Another fix by Bruno.

  • Corrected the order of Stream 'end' and 'close' events when reading a LOB.

    Bruno was busy this release and sent in a pull request for this too.

  • Fixed AIX-specific REF CURSOR related failures.

  • Fixed intermittent crash while setting fetchAsString, and incorrect output while reading the value.

  • Added a check to return an NJS error when an invalid DML RETURN statement does not give an ORA error.

  • Removed non-portable memory allocation for queries that return NULL.

  • Fixed encoding issues with several files that caused compilation warnings in some Windows environments.

  • Made installation halt sooner for Node.js versions currently known to be unusable.

  • Fixed typo in examples/dbmsoutputgetline.js

Issues and questions about node-oracledb can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

Installation instructions are here.

Node-oracledb documentation is here.

node-oracledb 1.1.0 is on NPM (Node.js add-on for Oracle Database)

Thu, 2015-09-03 16:24

Version 1.1 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM

This is a stabilization release, with one improvement to the behavior of the local connection pool. The add-on now checks whether pool.release() should automatically drop sessions from the connection pool. This is triggered by conditions where the connection is deemed to have become unusable. A subsequent pool.getConnection() will, of course, create a new, replacement session if the pool needs to grow.

Immediately as we were about to release, we identified an issue with lobPrefetchSize. Instead of delaying the release, we have temporarily made setting this attribute a no-op.

The changes in this release are:

  • Enhanced pool.release() to drop the session if it is known to be unusable, allowing a new session to be created.

  • Optimized query memory allocation to account for different database-to-client character set expansions.

  • Fixed build warnings on Windows with VS 2015.

  • Fixed truncation issue while fetching numbers as strings.

  • Fixed AIX-specific failures with queries and RETURNING INTO clauses.

  • Fixed a crash with NULL or uninitialized REF CURSOR OUT bind variables.

  • Fixed potential memory leak when connecting throws an error.

  • Added a check to throw an error sooner when a CURSOR type is used for IN or IN OUT binds. (Support is pending).

  • Temporarily disabled setting lobPrefetchSize

Issues and questions about node-oracledb can be posted on GitHub or OTN. We need your input to help us prioritize work on the add-on. Drop us a line!

Installation instructions are here.

Using DBMS_OUTPUT with Node.js and node-oracledb

Sun, 2015-08-30 19:20

The DBMS_OUTPUT package is the standard way to "print" output from PL/SQL. The way DBMS_OUTPUT works is like a buffer. Your Node.js application code turns on DBMS_OUTPUT buffering, calls some PL/SQL code that puts text into the buffer, and then later fetches from that buffer. Note: any PL/SQL code that calls DBMS_OUTPUT runs to completion before any output is available to the user. Also, other database connections cannot access your buffer.

A basic way to fetch DBMS_OUTPUT with node-oracledb is to bind an output string when calling the PL/SQL dbms_output.get_line() procedure, print the string, and then repeat until there is no more output. Another way that I like is to wrap the dbms_output.get_line() call into a pipelined function and fetch the DBMS_OUTPUT using a SQL query.

The following code shows both methods.


    Shows two methods of displaying PL/SQL DBMS_OUTPUT in node-oracledb.
    The second method depends on these PL/SQL objects:

      create or replace type dorow as table of varchar2(32767);
      show errors

      create or replace function mydofetch return dorow pipelined is
        line varchar2(32767);
        status integer;
        begin loop
          dbms_output.get_line(line, status); 
          exit when status = 1;
          pipe row (line);
        end loop;
      return; end;
      show errors


'use strict';

var async = require('async');
var oracledb = require('oracledb');
var dbconfig = require('./dbconfig.js');

  function(err, pool) {
    if (err)

var doit = function(pool) {
      function(cb) {

      // Tell the DB to buffer DBMS_OUTPUT

      // Method 1: Fetch a line of DBMS_OUTPUT at a time

      // Method 2: Use a pipelined query to get DBMS_OUTPUT 
      function(conn, cb) {
          "select * from table(mydofetch())", [], { resultSet: true}, cb);
    function (err, conn) {
      if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
      conn.release(function (err) { if (err) console.error(err.message); });

var enableDbmsOutput = function (conn, cb) {
    "begin dbms_output.enable(null); end;",
    function(err) { return cb(err, conn) });

var createDbmsOutput = function (conn, cb) {
    "begin "
     + "dbms_output.put_line('Hello, Oracle!');"
     + "dbms_output.put_line('Hello, Node!');"
     + "end;",
    function(err) { return cb(err, conn) });

var fetchDbmsOutputLine = function (conn, cb) {
    "begin dbms_output.get_line(:ln, :st); end;",
    { ln: { dir: oracledb.BIND_OUT, type:oracledb.STRING, maxSize: 32767 },
      st: { dir: oracledb.BIND_OUT, type:oracledb.NUMBER } },
    function(err, result) {
      if (err) {
        return cb(err, conn);
      } else if ( == 1) {
        return cb(null, conn);  // no more output
      } else {
        return fetchDbmsOutputLine(conn, cb);
var executeSql = function (conn, sql, binds, options, cb) {
    sql, binds, options,
    function (err, result) {
      if (err)
        cb(err, conn)
        cb(null, conn, result);

var printQueryResults = function(conn, result, cb) {
  if (result.resultSet) {
    fetchOneRowFromRS(conn, result.resultSet, cb);
  } else if (result.rows && result.rows.length > 0) {
    return cb(null, conn);
  } else {
    console.log("No results");
    return cb(null, conn);

function fetchOneRowFromRS(conn, resultSet, cb) {
  resultSet.getRow(  // note: getRows would be more efficient
    function (err, row) {
      if (err) {
        cb(err, conn);
      } else if (row) {
        fetchOneRowFromRS(conn, resultSet, cb);
      } else {
        cb(null, conn);

The output is:

Hello, Oracle!
Hello, Node!
[ 'Hello, Oracle!' ]
[ 'Hello, Node!' ]

I used resultSet.getrow() for simplicity, but you will probably want to use resultSet.getRows() for efficiency. If you want to buffer all the output in the Node.js application, Bruno Jouhier has a nice implementation to build up an array of query output in his GitHub gist query-all.js.

How to install node-oracledb on Windows

Mon, 2015-08-17 02:19

Bill Christo, one of our valued community members, has created a great YouTube video showing how to install node-oracledb on Windows.

The official installation manual is also handy. See Node-oracledb Installation on Windows.

Update: also see Bill's article on Installing node-oracledb on Microsoft Windows on OTN.

Node-oracledb goes 1.0: The Node.js add-on for Oracle Database

Mon, 2015-08-17 01:12

Today Oracle released node-oracledb 1.0, the Node.js add-on to enable high performance Oracle Database applications.

Node-oracledb is available from and GitHub.

Each month or so, since our first code bundle was pushed to GitHub earlier this year, we released a node-oracledb update with new functionality. The adoption has been exciting, with important applications already in production. This is our eighth release of node-oracledb and promises to be our best received so far.

The node-oracledb 1.0 add-on for Node.js supports standard and advanced features:

Oracle enhances, maintains and supports node-oracledb via open source channels (i.e. GitHub), similar to Oracle Database drivers for other open source languages. The add-on is under the Apache 2.0 license.

Where to get node-oracledb

The Oracle Technology Network Node.js Developer Center has all the links and information you need to start using node-oracledb.

To jump start, follow these instructions to install node-oracledb.

Changes since the previous release

The major changes in node-oracledb 1.0 since the previous release are:

  • The Stream interface for CLOB and BLOB types was implemented, adding support for LOB queries, inserts, and PL/SQL LOB bind variables. As well as being needed for working with many legacy schemas, having LOB support lets application developers use Oracle Database's JSON data type without running into the length limitation of VARCHAR2 storage.

    Customers have been contacting me what seems like every day, asking when LOB support would be available, and pleading for early access. Here it is, and it looks great. We'll be continuing to run load tests, benchmark it, and to enhance it.

    To see how to use LOBs with node-oracledb, checkout the node-oracledb Lob documentation and LOB examples

    General information about Oracle Database JSON support can be found in the documentation or on the JSON team blog.

  • Added Oracledb.fetchAsString and a new execute() property fetchInfo to allow numbers, dates, and ROWIDs to be fetched as strings. These features, available at the application level (for dates and numbers), and per-statement level (for dates, numbers and ROWIDs), can help overcome JavaScript limitations of representation and conversion.

  • Added support for binding DATE, TIMESTAMP, and TIMESTAMP WITH LOCAL TIME ZONE as DATE to DML RETURNING (aka RETURNING INTO) type. You can also bind these types as STRING.

  • The internal Oracle client character set is now always set to AL32UTF8. There's no longer a need to set it externally via NLS_LANG. A related bug with multibyte data reported by users was fixed by correcting the allocation of some internal buffers. Overall the NLS experience is much more consistent.

  • The test suite's and example database credentials can now be set via environment variables. A small change to help testing in automatically provisioned environments. Our test suite already has great coverage numbers, and will continue to be enhanced in future releases.

  • Bug fixes to node-oracledb. These are listed in the CHANGELOG.

What next?

Being an open source project in a dynamically changing environment, our statement of direction has been a brief, flexible goal: We are actively working on supporting Oracle Database features, and on functionality requests from users involved in the project. Our priority list is re-evaluated for each point release.

So now we have version 1.0, what next? This is just the start. There are plenty of important and interesting tasks in front of us. We will begin with a review of the project, from our development processes, the driver functionality, right through to distribution. This review will determine our next tasks. Hearing from users is crucial for prioritization, so don't hesitate to comment at GitHub.

Node.js is undergoing a surge of change at the moment, with the io.js re-merger, and the formation of the Node.js Foundation. As the merged Node.js code base stabilizes and the Foundation's LTS plans solidify, we will be able to be more formal about node-oracledb's schedule. We will work with Node.js and with partners to bring you the best experience. (On a technical note, the V2 release of the compatibility layer NAN was made in the last few days, too late for us to incorporate in node-oracledb 1.0. So, support of the latest, bleeding edge io.js will be in a future node-oracledb version.)

Let me wrap up this announcement by appreciating the growing node-oracledb community, particularly those who have contributed to node-oracledb with code, suggestions and discussions.