Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 59 min 31 sec ago

Node-oracledb: Avoiding "ORA-01000: maximum open cursors exceeded"

Fri, 2016-02-05 05:45

Developers starting out with Node have to get to grips with the 'different' programming style of JavaScript that seems to cause methods to be called when least expected! While you are still in the initial hacking-around-with-node-oracledb phase you may sometimes encounter the error ORA-01000: maximum open cursors exceeded. A cursor is "a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information"

Here are things to do when you see an ORA-1000:

  • Avoid having too many incompletely processed statements open at one time:

    • Close ResultSets before releasing the connection.

    • If cursors are opened with dbms_sql.open_cursor() in a PL/SQL block, close them before the block returns - except for REF CURSORS being passed back to node-oracledb. (And if a future node-oracledb version supports Oracle Database 12c Implicit Result Sets, these cursors should likewise not be closed in the PL/SQL block)

    • Make sure your application is handling connections and statements in the order you expect.

  • Choose the appropriate Statement Cache size. Node-oracledb has a statement cache per connection. When node-oracledb internally releases a statement it will be put into the statement cache of that connection, but its cursor will remain open. This makes statement re-execution very efficient.

    The cache size is settable with the stmtCacheSize attribute. The appropriate statement cache size you choose will depend on your knowledge of the locality of the statements, and of the resources available to the application: are statements re-executed; will they still be in the cache when they get executed; how many statements do you want to be cached? In rare cases when statements are not re-executed, or are likely not to be in the cache, you might even want to disable the cache to eliminate its management overheads.

    Incorrectly sizing the statement cache will reduce application efficiency. Luckily with Oracle 12.1, the cache can be automatically tuned using an oraaccess.xml file.

    More information on node-oracledb statement caching is here.

  • Don't forget to use bind variables otherwise each variant of the statement will have its own statement cache entry and cursor. With appropriate binding, only one entry and cursor will be needed.

  • Set the database's open_cursors parameter appropriately. This parameter specifies the maximum number of cursors that each "session" (i.e each node-oracle connection) can use. When a connection exceeds the value, the ORA-1000 error is thrown. Documentation on open_cursors is here.

    Along with a cursor per entry in the connection's statement cache, any new statements that a connection is currently executing, or ResultSets that haven't been released (in neither situation are these yet cached), will also consume a cursor. Make sure that open_cursors is large enough to accommodate the maximum open cursors any connection may have. The upper bound required is stmtCacheSize + the maximum number of executing statements in a connection.

    Remember this is all per connection. Also cache management happens when statements are internally released. The majority of your connections may use less than open_cursors cursors, but if one connection is at the limit and it then tries to execute a new statement, that connection will get ORA-1000: maximum open cursors exceeded.

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

Sat, 2016-01-30 06:07
Node-oracledb 1.6.0, the Node.js add-on for Oracle Database, is on NPM.

In this release a comprehensive pull request by Dieter Oberkofler adds support for binding PL/SQL Collection Associative Array (Index-by) types. Strings and numbers can now be bound and passed to and from PL/SQL blocks. Dieter tells us that nowadays he only gets to code for a hobby - keep it up Dieter!

Using PL/SQL Associative Arrays can be a very efficient way of transferring database between an application and the database because it can reduce the number of 'round trips' between the two.

As an example, consider this table and PL/SQL package:

  CREATE TABLE mytab (numcol NUMBER);

    PROCEDURE myinproc(p IN numtype);

    PROCEDURE myinproc(p IN numtype) IS
	INSERT INTO mytab (numcol) VALUES (p(i));

With this schema, the following JavaScript will result in mytab containing five rows:

    "BEGIN mypkg.myinproc(:bv); END;",
      bv: { type : oracledb.NUMBER,
	    dir: oracledb.BIND_IN,
	    val: [1, 2, 23, 4, 10]
    function (err) { . . . });

There is a fuller example in examples/plsqlarray.sql and check out the documentation.

Other changes in node-oracledb 1.6 are

  • @KevinSheedy sent a GitHub Pull Request for the README to help the first time reader have the right pre-requisites and avoid the resulting pitfalls.

  • Fixed a LOB problem causing an uncaught error to be generated.

  • Removed the 'close' event that was being generated for LOB Writables Streams. The Node.js Streams doc specifies it only for Readable Streams.
  • Updated the LOB examples to show connection release.

  • Extended the OS X install section with a way to install on El Capitan that doesn't need root access for Instant Client 11.2. Thanks to @raymondfeng for pointing this out.

  • Added RPATH to the link line when building on OS X in preparation for future client.

TypeScript users will be happy to hear Richard Natal recently had a node-oracledb TypeScript type definition file added to the DefinitelyTyped project. This is not part of node-oracledb itself but Richard later mentioned he found a way it could be incorporated. Hopefully he will submit a pull request and it will make it directly to the project so it can be kept in sync.

Thanks to everyone who has worked on this release and kept the momentum going.

What's coming up for the next release? There is discussion about adding a JavaScript layer. This was kicked off by a pull request from Sagie Gur-Ari which has lead to some work by Oracle's Dan McGhan. See the discussion and let us know what you think. Having this layer could make it quicker and easier for JavaScript coders to contribute node-oracledb and do things like reduce API inconsistency, make it easier to add a promise API in future, and of course provide a place to directly add Sagie's Streaming query result suggestion that started the whole thing.

I know a few contributors have recently submitted the Oracle Contributor Agreement ready to do big and small things - every bit counts. I look forward to being able to incorporate your work.

I've heard a couple of reports that Node LTS 4.2.6 on Windows is having some issues building native add-ons. 0.10, 0.12, 5.x and 4.2.5 don't have issues. Drop me a line if you encounter a problem.

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!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

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

Mon, 2015-12-21 06:56
Node-oracledb 1.5.0, the Node.js add-on for Oracle Database, is on NPM.

A number of bugs have been squashed in this release.

  • We now treat Oracle Database 'Success With Info' warnings as success.

    Thanks to Francisco Trevino for his pull request. After investigating and discussing, we decided for 1.5 to pick up the straightforward fix proposed. In a future release we will revisit allowing these warnings to be caught and handled.

  • Extended rollback-on-connection-release with 11g Oracle Clients to occur for all non-query executions.

    The natural behavior of OCI is to commit when a connection is released. This is the opposite of node-oracledb, which therefore has to determine whether to rollback or not.

    When node-oracledb is linked with 11g client a heuristic is used to guess whether to rollback when a connection is released. This heuristic needed to be changed to cover more cases. The result is that there will be sometimes be some unnecessary rollbacks issued.

    The bug didn't occur node-oracledb was linked with 12c client libraries due to this code that uses a new API available in 12c to indicate whether a connection has a transaction open.

    Bottom line: use Oracle 12c client libraries if possible to get optimal behavior.

  • Updated OS X install instructions to work on El Capitan.

    The instructions now use symbolic links in /usr/local/lib for the Oracle client libraries. This removes the need to set DYLD_LIBRARY_PATH, which has some restrictions on it introduced in El Capitan.

  • Display an error and prevent connection release while database calls are in progress.

    This was a bigger transaction, that 'fixed' a number of seemingly random crashes which were occurring when applications released connections that were in fact still in use. Node-oracledb will now print an error and not release the connection, thus preventing a crash. Note that since the release fails, connection pools can max out in this scenario. If you experience the errors NJS-030, NJS-031 or NJS-032 you should fix your app so the connection release occurs after all database operations have concluded.

    The new messages are:

    "NJS-030: Connection cannot be released because Lob operations are in  progress"
    "NJS-031: Connection cannot be released because ResultSet operations are in progress"
    "NJS-032: Connection cannot be released because a database call is in progress"
  • Fixed an intermittent crash while selecting data from CLOB column.

    We had an incorrect buffer expansion ratio in use. This has been fixed.

  • Fixed crash when trying to set invalid values for connection properties.

    Enough said.

Work for node-oracledb 1.6 will begin. We are trying to reproduce and understand some reported LOB issues and memory growth reports. We're also looking forward to evaluating a big pull request from Dieter Oberkofler that adds PL/SQL bind support.

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!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

PHP 7 OCI8 2.1.0 available on PECL

Fri, 2015-12-11 23:47

I've released PHP 7 OCI8 2.1 on PECL and simultaneously made a patch release OCI8 2.0.10 which is compatible with PHP 5.2 - PHP 5.6.

To install OCI8 for PHP 7 use:

pecl install oci8

This installs OCI8 2.1 which, as I'm sure you can guess, had a lot of internal changes to make it compatible with the vastly changed internals of PHP 7.

If you want to install OCI8 for PHP 5.2, 5.3, 5.4, or 5.6 use:

pecl install oci8-2.0.10

Functionality in 2.0.10 and 2.1.0 is equivalent. They both contain the fix for bug 68298, an overflow when binding 64bit numbers.

[Update: Windows DLLs have been built.] At time of writing, Windows DLLs were not yet built on PECL. If you need them, you can grab them from the full PHP Windows bundle.

Major news: PHP 7.0.0 has been released

Thu, 2015-12-03 22:58

Congratulations to the PHP community - the whole community - on the release of PHP 7.0.0. Thanks also to the Oracle staff who have worked on the internal rewrite necessary to make the OCI8 and PDO_OCI extensions work with PHP 7's completely revamped Extension API.

The Oracle Database OCI8 and PDO_OCI extensions are included in the PHP source distribution. The feature sets are unchanged.

The equivalent standalone OCI8 package compatible with PHP 7 will be released as version 2.1 on PECL soon. PDO_OCI will remain solely part of the core PHP source distribution.

For those interested in performance, Zend have put some benchmark figures here showing the significant improvements, which were a key feature of this release.

Other features are listed in the release announcement:

  • Significantly reduced memory usage
  • Abstract Syntax Tree
  • Consistent 64-bit support
  • Improved Exception hierarchy
  • Many fatal errors converted to Exceptions
  • Secure random number generator
  • Removed old and unsupported SAPIs and extensions
  • The null coalescing operator (??)
  • Return and Scalar Type Declarations
  • Anonymous Classes
  • Zero cost asserts

See the migration documentation for all the fine details.

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 01: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

Thu, 2015-10-22 00: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 01: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 09: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 17: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 20: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 03: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 02: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.

Installing node-oracledb on OS X with Oracle Instant Client

Sun, 2015-07-26 23:17

----> This note and script is obsolete. The current node-oracledb OS X install instructions contain nicer solutions <----- .

I've been hacking an Apple OS X shell script to install node-oracledb. You tell it where your Instant Client libraries and headers ZIP packages are. It then installs node-oracledb, resulting in an instantclient directory and a node_modules directory. This automates the instructions Node-oracledb Installation on OS X with Instant Client.

My script can be seen here. [Now removed]

I was investigating how to avoid needing to set DYLD_LIBRARY_PATH. I wanted to find how to replicate the use of rpath, which is available for node-oracledb on Linux. A standard install on OS X needs DYLD_LIBRARY_PATH set, otherwise Node.js will fail with the error:

   cjones@cjones-mac:~/n$ node select1.js

       throw err;
   Error: dlopen(/Users/cjones/n/node_modules/oracledb/build/Release/oracledb.node, 1):
           Library not loaded: /ade/b/3071542110/oracle/rdbms/lib/libclntsh.dylib.11.1
     Referenced from: /Users/cjones/n/node_modules/oracledb/build/Release/oracledb.node
     Reason: image not found
       at Module.load (module.js:356:32)
       at Function.Module._load (module.js:312:12)
       at Module.require (module.js:364:17)
       at require (module.js:380:17)
       at Object.<anonymous> (/Users/cjones/n/node_modules/oracledb/lib/oracledb.js:23:15)
       at Module._compile (module.js:456:26)
       at Object.Module._extensions..js (module.js:474:10)
       at Module.load (module.js:356:32)
       at Function.Module._load (module.js:312:12)
       at Module.require (module.js:364:17)

So, I was playing with to see how to circumvent this. Before running, edit it and set the paths to where the Instant Client 'basic' and 'sdk' ZIP files are located on your filesystem, see IC_BASIC_ZIP and IC_SDK_ZIP. (You can download Instant Client from OTN. Use the 64-bit packages). You also specify the target application directory you are using, see TARGET_DIR. This is where the components are installed into. Update https_proxy if you are behind a firewall, otherwise comment it out.

If you have various node_modules directories around, then npm might end up installing oracledb in an unexpected place and the script will error.

The key bit of that I was interested in is:

    # For Oracle Instant Client these are the default paths we will change

    . . .

    # Warning: work in progress - may not be optimal
    chmod 755 $OCI_LIB_DIR/*dylib $OCI_LIB_DIR/*dylib.11.1
    install_name_tool -id libclntsh.dylib.11.1 $OCI_LIB_DIR/libclntsh.dylib.11.1
    install_name_tool -change $IC_DEF2/libnnz11.dylib $OCI_LIB_DIR/libnnz11.dylib \
    install_name_tool -id libnnz11.dylib $OCI_LIB_DIR/libnnz11.dylib
    install_name_tool -change $IC_DEF1/libclntsh.dylib.11.1 \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1 $OCI_LIB_DIR/libociei.dylib
    install_name_tool -change $IC_DEF1/libclntsh.dylib.11.1 \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1 $NODE_ORACLEDB_LIB
    chmod 555 $OCI_LIB_DIR/*dylib $OCI_LIB_DIR/*dylib.11.1

This changes the library install and identification names using install_name_tool. Note this tool cannot allocate more space for path names than currently exists. My code is a work in progress; I may work out a better way, perhaps using libtool. Comments & suggestions welcome.

The script does more than most people probably need. In future even I might only run parts extracted from it.

If you are new to node-oracledb, check out its install and API documentation on GitHub. You may also be interested in reading The Easiest Way to Install Oracle Database on Mac OS X.

node-oracledb 0.7.0 now supports Result Sets and REF CURSORS

Mon, 2015-07-20 17:58

A new release of the Node.js driver for Oracle Database is now on and GitHub.

node-oracledb 0.7 connects Node.js 0.10, Node.js 0.12, and io.js to Oracle Database. It runs on a number of platforms. For more information about node-oracledb see the node-oracledb GitHub page.

The changes in 0.7 are:

  • Added result set support for fetching large data sets. Rows from queries can now be fetched in batches using a ResultSet class. This allows large query results to be fetched without requiring all values to be in memory at once. New getRow() and getRows() methods can be called repeatedly to scroll through the query results.

    The original node-oracledb behavior of returning all rows at once remains the default. To return a resultSet, use the new execute() option { resultSet: true }. For example:

    //  (See the full code in examples/resultset2.js)
    . . .
    var numRows = 10;  // number of rows to return from each call to getRows()
      "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
      [], // no bind variables
      { resultSet: true }, // return a result set.  Default is false
      function(err, result)
        if (err) { . . . }
        fetchRowsFromRS(connection, result.resultSet, numRows);
    . . .
    function fetchRowsFromRS(connection, resultSet, numRows)
      resultSet.getRows( // get numRows rows
        function (err, rows)
          if (err) {
             . . .                        // close the result set and release the connection
          } else if (rows.length == 0) {  // no rows, or no more rows
            . . .                         // close the result set and release the connection
          } else if (rows.length > 0) {
            fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows

    It's important to use the new resultSet close() method to close the result set when no more data is available or required.

    There is more information on Result Sets in the manual.

  • Added REF CURSOR support for returning query results from PL/SQL. PL/SQL code that returns REFCURSOR results via bind parameters can now bind a new node-oracledb type Oracledb.CURSOR and fetch the results using the new ResultSet class.

    //  (See the full code in examples/refcursor.js)
    var oracledb = require('oracledb');
    . . .
    var numRows = 10;  // number of rows to return from each call to getRows()
    var bindvars = {
      sal:  6000,
      cursor:  { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
      "BEGIN get_emp_rs(:sal, :cursor); END;",  // The PL/SQL has an OUT bind of type SYS_REFCURSOR
      function(err, result)
        if (err) { . . . }
        fetchRowsFromRS(connection, result.outBinds.cursor, numRows);
    . . .
    function fetchRowsFromRS(connection, resultSet, numRows)
      resultSet.getRows( // get numRows rows
        function (err, rows)
          if (err) {
             . . .                        // close the result set and release the connection
          } else if (rows.length == 0) {  // no rows, or no more rows
            . . .                         // close the result set and release the connection
          } else if (rows.length > 0) {
            fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows

    There is more information on using REF CURSORS in the manual.

  • Added row prefetching support. The new ResultSet class supports prefetching via a new attribute oracledb.prefetchRows and a new execute() option prefetchRows. Each time the application fetches query or REF CURSOR rows in a ResultSet from Oracle Database, prefetching allows the underlying Oracle libraries to transfer extra rows. This allows better use of database and network resources, improving performance and scalability. Regardless of the prefetch size, the number of rows returned to the application does not change. Buffering is handled by the underlying Oracle client library.

    The default prefetch size is 100 extra rows. Applications should tune the prefetch size used by each execute() for desired performance and/or to avoid allocating and initializing unused memory. There are some more tips in the manual.

    With node-oracledb 0.7.0, non-ResultSet queries now use prefetching with a fixed size of 2. This should reduce the number of round trips required for these queries.

  • Added a test suite. Yay! See the README in the tests directory for how to run the tests. When you run the test suite, you'll notice each test has a unique number for ease of identification. The numbers are not necessarily sequential.

    We do most testing on Linux and Windows. If you see test output differences due to environment or version differences, please sign the OCA and submit a pull request with the fix and an explanation of why it is needed. See CONTRIBUTING.

    If you submit new tests (after signing the OCA), assign each one a unique number in the documented range that applies to the area being tested.

  • Fixed error handling for SQL statements using RETURNING INTO. A bug causing all errors with DML RETURNING statements to report the same error message was fixed.

  • Fixed INSERT of a date when the SQL has a RETURNING INTO clause. When using an INSERT to insert a date or timestamp and the SQL clause had a RETURNING INTO clause for character or number columns, then an error was being thrown. This has been fixed.

  • Renumbered the values used by the Oracledb Constants. If your application uses constant names such as Oracledb.OBJECT or Oracledb.BIND_INOUT then you won't notice the change. However if, for some reason, code has hardcoded numbers like 2, then you will have to update to use the new numbers, see lib/oracledb.js. Or, better, change the code to use the constants' names.

Python cx_Oracle 5.2 driver for Oracle Database has been released

Mon, 2015-06-22 18:03

Anthony Tuininga just released an updated Python cx_Oracle 5.2 driver for Oracle Database. This release brings a number of enhancements, many of them for Oracle Database 12c features such as longer VARCHARS.

cx_Oracle 5.2 is the first release Oracle has contributed code for (thanks Avinash!) so we're pretty happy all around. And a big thank you to all the other contributors and users who have made this release possible.

The new code features are:

  • Support for Oracle Database 12c strings up to 32k characters.
  • Support for LOB values larger than 4 GB.
  • Support for Oracle Database 12c array DML row counts.
  • Support for fetching batch errors.
  • Support for connections as SYSASM.
  • Added types NCHAR, FIXED_NCHAR and LONG_NCHAR to replace the types UNICODE, FIXED_UNICODE and LONG_UNICODE (which are now deprecated). These types are available in Python 3 as well so they can be used to specify the use of NCHAR type fields when binding or using setinputsizes().
  • Support for building without any configuration changes to the machine when using instant client RPMs on Linux.
  • Fixed session releasing to the pool when calling connection.close() (Issue #2)
  • Fixed binding of booleans in Python 3.x.
  • Added __version__ attribute to conform with PEP 396.
  • Fixed handling of datetime intervals (Issue #7)

The complete release notes are here.

My favorite feature is the installation improvement. (Disclaimer: I contributed the initial implementation!) With this change, Instant Client RPMS on Linux can now be used. The best bit is cx_Oracle will automatically locate Instant Client and will then also automatically build using rpath. The installation of cx_Oracle on Linux is now as simple as installing the Instant Client Basic & SDK RPMs, and running 'pip install cx_Oracle'. No need to set ORACLE_HOME during installation. No need to set LD_LIBRARY_PATH at runtime. If you have a Linux ULN support subscription you can install Instant Client via yum, which makes it even simpler.

Users of Database Resident Connection Pooling will like the connection.close() fix since it allows DRCP to be used effectively without requiring a cx_Oracle session pool.

In summary the cx_Oracle 5.2 release adds great features to the already impressive Oracle Database support available to Python applications. Application development and deployment just got better.


Thanks again to Anthony, Avinash and all the contributors who have made cx_Oracle so good.

Scripting Languages & Oracle: Blogs to Follow

Wed, 2015-06-17 14:58
If you haven't come across them, keep an eye on the blogs of Blaine Carter and Dan McGhan. They will be posting mostly on developing with Python and Javascript, respectively. Blaine & Dan work at Oracle under the leadership of Steven Feuerstein who is well known in the Oracle PL/SQL world for his enthusiasm for application development.

node-oracledb 0.6.0 is on NPM (Node.js driver for Oracle Database)

Tue, 2015-05-26 17:31

Node-oracledb 0.6.0 is now out on NPM. The Oracle Database Node.js driver powers high performance Node.js applications.

There is one feature change in this release: node-oracledb now builds with Node.js 0.10, 0.12 and with io.js. Huge thanks to Richard Natal for his GitHub pull request that added support.

For more information about node-oracledb see the node-oracledb GitHub page.