Christopher Jones

Subscribe to Christopher Jones feed
Notes on the Scripting Languages and Oracle Database. I cover Node.js, PHP, Python, Ruby, Perl, Apache, Linux
Updated: 6 hours 21 min ago

node-oracledb Session at DeveloperWeek, San Francisco 2017

Tue, 2017-02-14 17:54

I just finished my session on node-oracledb at DeveloperWeek on Pier 27 in sunny, fantastic San Francisco. I'm in my old neighborhood, enjoying a familar view of the Bay Bridge from the speaker room window. There is a big hum around me from sessions and demo booths as developers are networking and learning.

If you missed my session you can check out the slides. (They should be available for a month or so). I've added some slides that didn't fit into the 40 minute session, but you'll just have to imagine all the cool, extra tips I verbalized during the presentation! Make sure to come and listen to me speak at a future conference.

PS Thanks to the Oracle Apps UX crew for the photographs (and for lunch afterwards). I'm looking forward to visiting their Oracle HQ lab to see all the cool stuff they do.

Christopher Jones presenting on node-oracledb at Developer Week in San Francisco on 14 February 2017 Christopher Jones presenting on node-oracledb at Developer Week in San Francisco on 14 February 2017

New Product Launch: Oracle Database Programming Interface for C (ODPI-C)

Mon, 2017-01-30 20:36

Today Oracle released a great new GitHub project - Oracle Database Programming Interface for C. It sits on top of OCI and offers an alternative programming experience.

ODPI-C is a C library that simplifies the use of common Oracle Call Interface (OCI) features for Oracle Database drivers and user applications. ODPI-C Goal

ODPI-C's goal is to expose common OCI functionality in a readily consumable way to the C or C++ developer. OCI's API is extremely flexible and is highly efficient. It gives a lot of fine-grained control to the developer and has a very wide range of use cases. ODPI-C is also flexible but is aimed primarily at language driver creators. These creators are programming within the confines of a scripting language's type system and semantics. The languages often expose simplified data access to users through cross-platform, 'common-denominator' APIs. Therefore it makes sense for ODPI-C to provide easy to use functionality for common data access, while still allowing the power of Oracle Database to be used.

Of course ODPI-C isn't just restricted to driver usage. If ODPI-C has the functionality you need for accessing Oracle Database, you can add it to your own custom projects.

ODPI-C is a refactored and greatly enhanced version of the "DPI" data access layer used in our very successful node-oracledb driver.

Releasing ODPI-C as a new, standalone project means its code can be consumed and reused more easily. For database drivers it allows Oracle features to be exposed more rapidly and in a consistent way. This will allow greater cross-language driver feature compatibility, which is always useful in today's multi-language world.

ODPI-C Features

Oracle's Anthony Tuininga has been leading the ODPI-C effort, making full use of his extensive driver knowledge as creator and maintainer of the extremely popular, and full featured, Python cx_Oracle driver.

The ODPI-C feature list currently includes all the normal calls you'd expect to manage connections and to execute SQL and PL/SQL efficiently. It also has such gems as SQL and PL/SQL object support, scrollable cursors, Advanced Queuing, and Continuous Query Notification. The full list in this initial Beta release, in no particular order, is:

  • 11.2, 12.1 and 12.2 Oracle Client support

  • SQL and PL/SQL execution

  • REF cursors

  • Large objects (CLOB, NCLOB, BLOB, BFILE)

  • Timestamps (naive, with time zone, with local time zone)

  • JSON objects

  • PL/SQL arrays (index-by integer tables)

  • Objects (types in SQL, records in PL/SQL)

  • Array fetch

  • Array bind/execute

  • Array DML Row Counts

  • Standalone connections

  • Connections via Session pools (homogeneous and non-homogeneous)

  • Session Tagging in session pools

  • Database Resident Connection Pooling (DRCP)

  • Connection Validation (when acquired from session pool or DRCP)

  • Proxy authentication

  • External authentication

  • Statement caching (with tagging)

  • Scrollable cursors

  • DML RETURNING clause

  • Privileged connection support (SYSDBA, SYSOPER, SYSASM, PRELIM_AUTH)

  • Database Startup/Shutdown

  • End-to-end tracing, mid-tier authentication and auditing (action, module, client identifier, client info, database operation)

  • Batch Errors

  • Query Result Caching

  • Application Continuity (with some limitations)

  • Query Metadata

  • Password Change

  • OCI Client Version and Server Version

  • Implicit Result Sets

  • Continuous Query Notification

  • Advanced Queuing

  • Edition Based Redefinition

  • Two Phase Commit

In case you want to access other OCI calls without having to modify ODPI-C code, there is a call to get the underlying OCI service context handle.

ODPI-C applications can make full advantage of OCI features which don't require API access, such as the oraaccess.xml configuration for enabling statement cache auto-tuning. Similarly, Oracle Database features controlled by SQL and PL/SQL, such as partitioning, can be used in applications, as you would expect.

Communication to the database is handled by Oracle Net, so features such as encrypted communication and LDAP can be configured.

ODPI-C's API makes memory and resource management simpler, particularly for 'binding' and 'defining'. A reference counting mechanism adds resiliency by stopping applications destroying in-use OCI resources. To offer an alternative programming experience from OCI, the ODPI-C API uses a multiple getter/setter model for handling attributes.

Using ODPI-C

ODPI-C is released as source-code on GitHub. The code makes OCI calls and so requires an Oracle client, which must be installed separately. Version 11.2 or later of the client is required. This allows applications to connect to Oracle Database 9.2 or later. The free Oracle Instant Client is the standard way to obtain standalone Oracle client libraries and header files.

The project is licensed under the Apache 2.0 and/or the Oracle UPL licenses, so the code is readily available for adoption into your own projects.

ODPI-C code can be included in your C or C++ applications and compiled like any OCI application. Or if you want to use ODPI-C as a shared library, a sample Makefile for building on Linux, OS X and Windows is provided

Support for ODPI-C is via logging GitHub Issues only - but this does have the advantage of giving you direct access to ODPI-C developers. Also remember the underlying OCI libraries (which do all the hard work) are extremely widely used, tested and supported.

If you want to do more than view the code, you can build ODPI-C as a library using the sample Makefile, and then build the current standalone sample programs. These show a number of ODPI-C features.

ODPI-C Plans

The ODPI-C release today is 2.0.0-beta.1, indicating we're happy with the general design but want to get your wider review. We also need to complete some testing and add some polish.

We aim to stabilize ODPI-C relatively quickly and then continue adding functionality, such as support for the new Oracle Database 12.2 Sharding feature.

Future Node.js node-oracledb and Python cx_Oracle drivers will use ODPI-C. There is active work on these updates.

I know Kubo Takehiro, who does a fantastic job maintaining the ruby-oci8 driver, has been keen to see what ODPI-C can do for his driver. I look forward to seeing how he uses it.

I think you'll be pleased with the direction and plans for scripting languages in 2017.

We really welcome your feedback on this big step forward.

ODPI-C References

Home page is:

Code is at

Documentation is at

Issues and comments are be reported at

node-oracledb 1.12: Working with LOBs as String and Buffer. Connection Pinging.

Wed, 2016-12-21 04:30

Node-oracledb 1.12, the Node.js add-on for Oracle Database, is now on NPM.

Top features: LOBs as JavaScript Strings and Buffers. Pool connection 'aliveness' checking.

The two main features of node-oracledb 1.12 add functionality that aids usability. You may not even notice one working in the background, silently re-establishing pooled connections after network outages. The other is a 'no brainer' that makes working with large objects (LOBs) easier. You'll probably use it and think nothing of it.

This release also contains a number of bug fixes and small changes. Check the CHANGELOG for all the details.

LOB Enhancements

Adding to the existing support for using CLOBs and BLOBs as Node.js Streams, now Strings and Buffers can be bound to CLOBs and BLOBs for IN, IN OUT and OUT binds. CLOBs in queries can also be returned directly as Strings. And streaming to and from Oracle's 'temporary LOBs' is supported. Also in this release, Writeable LOB Streams now conclude with a 'close' event.

The node-oracledb LOB documentation is worth reading. There are runnable examples in the examples directory.

To insert large data, just bind a Node.js String or Buffer to a CLOB or BLOB column, respectively. For example:

  var fs = require('fs');
  var str = fs.readFileSync('example.txt', 'utf 8');

    "INSERT INTO mylobs (id, myclobcol) VALUES (:idbv, :cbv)",
    { idbv: 1,
      cbv: str },  // type and direction are optional for IN binds
    function(err, result)
      if (err)
	console.log('CLOB inserted from example.txt');
      . . .

You can also bind Strings and Buffers to PL/SQL LOB parameters as IN, OUT and IN OUT binds.

To fetch CLOBs as Strings from queries, use fetchAsString or fetchInfo:

  // return all CLOBs as Strings
  oracledb.fetchAsString = [ oracledb.CLOB ];

    "SELECT mycol FROM mylobs WHERE id = 1",
    function(err, result) {
      if (err) { console.error(err.message); return; }
      if (result.rows.length === 0)
	console.error("No results");
      else {
	var clob = result.rows[0][0];

Currently BLOBs in queries must be streamed; we didn't want to hold up the 1.12 release.

Use the right tool for the job: use Strings and Buffer for 'small' LOBs, and use Streams for everything else. There is a theoretical limit just under 1 GB in node-oracledb for manipulating LOBs as Strings or Buffers. Luckily you wouldn't want to be allocating that much memory in Node.js applications - it's better to stream. And Node.js itself doesn't cope well with large data. It can get unstable with Strings past the several hundred megabyte mark. (Note that if node-oracledb is linked with Oracle client 11g libraries, than the upper size of Strings and Buffers is just under the 64 KB mark: the moral is use Oracle client 12c libraries - you can still connect to Oracle Database 10.2, 11g and 12c).

For larger data you have multiple options for streaming. To insert into a table column, use the existing RETURNING INTO method where you get an Oracle LOB locator and use Node.js Streams functionality to put data into it. For fetching, use the existing LOB Streaming method and stream data out of the Lob.

If data is too big to bind as a String or Buffer to a PL/SQL parameter, you can create a 'temporary LOB' in node-oracledb, stream into it, and then bind it. This is new in node-oracledb 1.12

First, use the new connection.createLob() method to create a temporary LOB, here of type CLOB:

  conn.createLob(oracledb.CLOB, function(err, templob) {
    if (err) { . . . }
    // ... else use templob

This creates an instance of a node-oracledb Lob class.

Once created, data can be inserted into the Lob. For example to read a text file:

  templob.on('error', function(err) { somecallback(err); });

  // The data was loaded into the temporary LOB, so use it
  templob.on('finish', function() { somecallback(null, templob); });

  // copies the text from 'example.txt' to the temporary LOB
  var inStream = fs.createReadStream('example.txt');
  inStream.on('error', function(err) { . . . });

Now the LOB has been populated, it can be bound in somecallback() to a PL/SQL IN parameter:

  // For PROCEDURE lobs_in(p_id IN NUMBER, c_in IN CLOB)
    "BEGIN lobs_in (:id, :c); END",
    { id: 3,
      c: templob }, // type and direction are optional for IN binds
      if (err) { return cb(err); }
      console.log("Call completed");
      return cb(null, conn, templob);

When the LOB is no longer needed, it must be closed with lob.close():

  templob.close(function (err) {
    if (err)
      . . .
      // success
Connection Pool Enhancements

The other main feature in node-oracledb 1.12 is connection pool session 'aliveness' checking. This is enabled by default. You may never notice it in action but it will improve application behavior where networks are unstable, or database services become temporarily unavailable.

When pooled connections have been established to the database, but are idle in the connection pool not currently being used, there is a chance that a network outage will make those connections unusable. A common scenario is when a developer's laptop is closed for a while. Unless node-oracledb is linked with Oracle 12.2 client libraries, any node-oracledb pooled getConnection() call could return one of the unusable connections. For users of Oracle 11.2 or 12.1 client libraries, a new poolPingInterval setting will do a 'ping' to the database to check the connection is OK before returning that connection to the application. If it isn't OK, then another connection is returned.

There is the overhead of a 'round trip' to the database in doing a ping, so there could be some impact on scalability. But the ping setting is time based so there is no overhead on active pools. The ping will only happen when a connection has been idle in the pool for poolPingInterval seconds. If a connection has been recently used, it will be returned to the application without being checked. The default interval is 60 seconds. The feature can be configured to always-force the ping, to totally disable it, or to be whatever interval meets your performance and quality-of-service requirements. The value can be set when a pool is created, or turned on globally:

  oracledb.poolPingInterval = 60;

Applications should continue to detect and handle execution-time errors, since execution errors may occur, or a network outage may occur between getConnection() and execute() calls, or connections may have been in use and not released back to the pool when the network dropped, or the idle time may have been shorter than the ping interval setting and no ping performed. The new feature is great at improving the reliability of connections in unstable environments but is just one of the options and tools that can be used to provide a highly available application.

The ping feature is a no-op when node-oracledb is linked with Oracle 12.2 client libraries (independent of the database version at the other end of the connection), since those libraries have an always-enabled, lightweight connection check that removes the need for node-oracledb to do its own ping. This adds even more certainty that a connection will be valid at time of first use after a getConnection() call. Oracle 12.2 is currently available in Oracle Cloud.

The pool pinging documentation has more information.

Documentation Updates

If you haven't scanned the documentation for a while, take a look. Each release more and more tips and information gets added.

A couple of community contributions from Leigh Schrandt and Nick Heiner regarding the README were gratefully received. Thank you!


Testing of node-oracle is also something I wanted to mention. The test suite continues to grow, as you can see when you run it. We also have additional stress and loads tests - overall these extra test take days to complete. There are no guarantees, of course, but node-oracledb is more than solid.


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.

Finally, contributions to node-oracledb are more than welcome, see CONTRIBUTING.

node-oracledb 1.12.1-dev can fetch CLOBs as JavaScript String

Mon, 2016-12-19 17:12

A preview of node-oracledb 1.12.1-dev is available on GitHub and can be installed with:

  npm install oracle/node-oracledb.git#v1.12.1-dev

Node-oracledb is the Node.js add-on for Oracle Database.

The 1.12.1-dev release introduces fetchAsString support for CLOBs. Now, when CLOB columns are queried, they can be returned directly as JavaScript Strings, without the need to use Streams. To test this in the dev release make sure node-oracledb is linked with Oracle 12c client libraries.

See the extensive manual for details and examples.

Also in this release is improved support for 'temporary LOBs'. Now they can be bound as IN OUT binds, (as well as IN and OUT!)

See my previous post for a brief intro to earlier changes in this 1.12 series. The CHANGELOG has all the updates. I'll blog the features in more detail when a production bundle is released to


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

Node-oracledb documentation is here.

node-oracledb 1.12.0-dev available for preview

Mon, 2016-12-05 00:57

A preview of node-oracledb 1.12.0-dev is available on GitHub and can be installed with:

  npm install oracle/node-oracledb.git#v1.12.0-dev

Node-oracledb is the Node.js add-on for Oracle Database.

The key things to look at are all the LOB enhancements - we want feedback. There are a couple more LOB enhancements we want to make before we go production, but I know you'll like the direction. Drop us a line via GitHub or email with your comments.

This release also has a connection pool ping feature that will improve application availability when transient network outages occur. It's enabled by default and will be transparent to most users, however it can be tuned or disabled if you have a special situation.

The CHANGELOG has all the other updates in this release. I'll blog all the features in more detail when a production bundle is released to


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

Node-oracledb documentation is here.

Fast Generation of CSV and JSON from Oracle Database

Thu, 2016-11-10 01:38

Want a fast way to spool CSV or JSON from Oracle Database? Check out the new SQL*Plus 12.2 SET MARKUP CSV option and the new Oracle Database 12.2 JSON_OBJECT operator.

You can try Oracle Database 12.2 now using Oracle Cloud Database Service.

Fast, Easy CSV with SQL*Plus and Oracle Database

First, let's see CSV ("comma separated values") output in SQL*Plus 12.2. Start with this script, t.sql:

  set feedback off

  select department_id, department_name from departments where department_id < 110;

Executed traditionally you get formatted output:

  SQL> @t.sql

  ------------- ------------------------------
	     10 Administration
	     20 Marketing
	     30 Purchasing
	     40 Human Resources
	     50 Shipping
	     60 IT
	     70 Public Relations
	     80 Sales
	     90 Executive
	    100 Finance

Running it with the new CSV mode:

  SQL> set markup csv on
  SQL> @t.sql

  40,"Human Resources"
  70,"Public Relations"


The full CSV syntax is:


You can see the delimiter can be changed from a comma, and quoting of fields can be disabled.

The SET MARKUP option can also be enabled from the command line with the -m option:

  $ sqlplus -s -m 'csv on' cj@pdb1 @t.sql

  40,"Human Resources"
  70,"Public Relations"
(Pro tip: the -s silent option may hide the password prompt on some platforms making SQL*Plus appear to hang! Enter the password and all will be well.)

CSV mode bypasses the traditional SQL*Plus pagination routines, making output faster to generate. And using the -m 'csv on' option additionally invokes a faster I/O subsystem, and also automatically sets some SET options to the values of another new option sqlplus -f. Two of the changes are increasing SET ARRAYSIZE to 100 and SET ROWPRETCH to 2.

Increasing SET ARRAYSIZE allows larger amounts of data to be returned in each low level request to the database, thus improving overall efficiency. Having SET ROWPRETCH to 2 reduces the database 'round trips' required between SQL*Plus and the database for queries that return only single rows. You should adjust the values of these, and other, settings to suit your data size and performance goals.

CSV mode can be used when connecting SQL*Plus 12.2 to Oracle Database 12.2 or earlier.

Fast, Easy JSON with SQL*Plus and Oracle Database

The Oracle Database 12.2 JSON_OBJECT function is a great way to convert relational table output into JSON.

Combined with SQL*Plus 12.2's efficient CSV output - and with quoting of columns disabled - you can spool JSON very easily.

Here's a SQL*Plus script t2.sql to return JSON output:

  set heading off
  set feedback off

  select json_object ('deptId' is d.department_id, 'name' is d.department_name) department 
  from departments d 
  where department_id < 110;


Running it generates the desired JSON output:

  $ sqlplus -s -m 'csv on quote off' cj@pdb1 @t2.sql
  {"deptId":40,"name":"Human Resources"}
  {"deptId":70,"name":"Public Relations"}

SQL*Plus 12.2 and Oracle Database 12.2 allow efficient and easy access to CSV and JSON data. If you don't yet have 12.2, or you want to create web services and do more with the data, check out Oracle ORDS or roll your own solution using the node-oracledb driver for Node.js.

Application Development at Oracle OpenWorld, San Francisco, September 2016

Thu, 2016-08-25 03:11

Well, there is certainly a lot going on at Oracle OpenWorld this September. You can browse the session catalog for interesting talks.

Here are a few highlights in my area:

That's some great content there.

The "Meet the Experts" session is the interactive session where you get to hear from, and ask questions to, our key developers and managers in the Scripting Language and .NET area. If you're shy, you don't have to speak - just come and learn.

We'll also have a demo booth open on the exhibition floor so you can come and chat. (Its location is yet to be announced).

I really hope to see you during the 2016 conference.

Node-oracledb 1.11 Released to NPM

Fri, 2016-08-19 02:09

We've pushed out a release of node-oracledb to GitHub and NPM.

Top feature: Connection Pool Cache

The node-oracledb driver connects Node.js to Oracle Database for fast and functional applications.

The changes in node-oracledb 1.11 are:
  • Added a connection pool cache feature allowing pools to be given a string alias at creation. This makes pools easier to use.

    Pools are typically created in one file and later used in other files. Giving a pool a string alias allows these other modules to access the pool by knowing only its alias name.

    When pools are created with oracledb.createPool(), the pool attributes can now optionally contain a new poolAlias string.

    var hrPoolPromise = oracledb.createPool({
      poolAlias: 'pool1',
      users: 'hr',
      password: 'welcome',
      connectString: 'localhost/orcl'

    Pools can be retrieved from the cache using a new oracledb.getPool() method, and then used as before to get connections. Multiple pools, each with a different alias, can be used.

    Interestingly, oracledb.getConnection() has been enhanced to take a pool alias specifying which pool to return a connection from. This bypasses the need to call oracledb.getPool().

    And there's more! The first pool (in a normal code flow) created without a poolAlias attribute is given the alias "default". Connections can be retrieved from this pool by using oracledb.getConnection() without passing an alias at all. This means applications that create only a single pool can simply use oracledb.getConnection() anywhere to get a connection from the pool.

    More information and examples are in the documentation. It is worth checking this since oracledb.getConnection() now has several different behaviors, depending on the use of aliases (and whether callbacks or promises are used).

    We'd recommend using aliases all the time if you create more than one pool - and want to access them via aliases. Using aliases is optional. Don't mix un-aliased and aliased pools unless you want to confuse yourself.

  • Improved the bootstrap error message when the node-oracledb binary cannot be loaded.

  • Fixed memory leaks with DATE and TIMESTAMP bind values.

  • Fixed external authentication which broke in 1.10.

  • Fixed metadata scale and precision values on AIX.

  • A couple of other internal improvements are shown in the CHANGELOG.


Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

PHP OCI8 2.1.2 and OCI8 2.0.12 released on PECL

Thu, 2016-08-18 20:04

PHP OCI8 2.1.2 (for PHP 7) and OCI8 2.0.12 (for PHP 5.2+) have been uploaded to PECL. The OCI8 extension allows PHP applications to connect to Oracle Database.

Use pecl install oci8 to install PHP OCI8 2.1 for PHP 7.

Use pecl install oci8-2.0.12 to install PHP OCI8 2.0 for PHP 5.2 - PHP 5.6. Note: PHP 5.6 is the oldest release which the PHP community is maintaining.

As well as being available from PECL, PHP OCI8 is also included in the base PHP bundles. OCI8 2.0.12 will be included in PHP 5.6.26. OCI8 2.1.2 will be included in PHP 7.0.11 and PHP 7.1.

The changes are:

  • Fixed an invalid handle error with Implicit Result Sets. (OCI8 2.0.12 and 2.1.2)

  • Fixed bug #72524 (Binding null values triggers ORA-24816 error) (OCI8 2.1).

    This necessarily reverts a change in PHP 7 (OCI8 2.1) back to match PHP 5's (OCI8 2.0) behavior. If you had PHP 7 code that was not specifying a size for OUT binds, then you will need to modify your code. Passing a maxlength to oci_bind_by_name() for OUT binds remains the documented and recommended best practice.

  • Several internal code tidy ups (OCI8 2.1).

More about PHP and OCI8 can be found in the The Underground PHP and Oracle Manual and on the Oracle Technology Network.

Node-oracledb 1.10.1 Released to NPM

Wed, 2016-07-20 21:10

We've pushed out a patch release of node-oracledb to GitHub and NPM. It has a bind fix that didn't land in time for 1.10.0. A memory leak regression with REF CURSORs was also fixed, as was a pre-existing memory leak in the unexpected case of a REF CURSOR failure.

The changes in node-oracledb 1.10.1 are:
  • Fixed a bug that prevented a null value being passed from JavaScript into an IN OUT bind.

  • Fixed a memory leak introduced in 1.10 with REF CURSORs.

  • Fixed an existing memory leak problem if an error happens when fetching REF CURSORs.

  • Added a mocha configuration file for the test suite. The test suite README has updated instructions on how to add tests.

    As a side effect, the tests now run in numeric order.

A couple of other internal improvements are shown in the CHANGELOG.


Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

Node-oracledb 1.10 has Enhanced Metadata

Fri, 2016-07-08 03:06

Top feature: Enhanced Metadata

The changes in node-oracledb 1.10 are:
  • Enhanced query metadata thanks to a Pull Request from Leonardo. He kindly allowed us to take over and fine tune the implementation.

    Additional metadata for query and REF CURSOR columns is available in the metaData object when the new boolean oracledb.extendedMetaData attribute or corresponding execute() option attribute extendedMetaData are true.

    For example, if the DEPARTMENTS table is like:

    SQL> desc departments
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DEPARTMENT_ID                             NOT NULL NUMBER(4)
     DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
     MANAGER_ID                                         NUMBER(6)
     LOCATION_ID                                        NUMBER(4)

    Then a query in node-oracledb would give extended metadata:

    [ { name: 'DEPARTMENT_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 4,
           scale: 0,
           nullable: false },
         { name: 'DEPARTMENT_NAME',
           fetchType: 2001,
           dbType: 1,
           byteSize: 30,
           nullable: false },
         { name: 'MANAGER_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 6,
           scale: 0,
           nullable: true },
         { name: 'LOCATION_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 4,
           scale: 0,
           nullable: true } ]

    You can see that the available attributes vary with the database type. The attributes are described in the metaData documentation.

    The commonly used column name is always available in metaData regardless of the value of extendedMetaData. This is consistent with previous versions.

    The metadata dbType and fetchType attributes numbers are described in new DB_TYPE_* constants and the existing node-oracledb type constants, respectively. Your code should use these constants when checking metadata types.

    Why did we make the extra metadata optional and off by default? Why do the types use numbers instead of strings? We had a lot of debate about common use cases, out-of-box experience, performance etc. and this is the way the cookie crumbled.

    I know this enhancement will make your applications easier to maintain and more powerful.

  • Fixed an issue preventing the garbage collector cleaning up when a query with LOBs is executed but LOB data isn't actually streamed.

  • Report an error earlier when a named bind object is used in a bind-by-position context. A new error NJS-044 is returned. Previously errors like ORA-06502 were given since the expected attributes were not found and bind settings ended up as defaults. You can still use unnamed objects for bind-by-position binds like:

    var sql = "begin myproc(:1, :2, :3); end;";
    var binds = [ id, name, { type: oracledb.STRING, dir: oracledb.BIND_OUT } ];

    Here the third array element is an unnamed object.

  • Fixed a bug where an error event could have been emitted on a QueryStream instance prior to the underlying ResultSet having been closed. This would cause problems if the user tried to close the connection in the error event handler as the ResultSet could have prevented it.

  • Fixed a bug where the public close method was invoked on the ResultSet instance that underlies the QueryStream instance if an error occurred during a call to getRows. The public method would have thrown an error had the QueryStream instance been created from a ResultSet instance via the toQueryStream method. Now the call to the C layer close method is invoked directly.

  • Updated Pool._logStats to throw an error instead of printing to the console if the pool is not valid.

  • Added GitHub Issue and Pull Request templates.

  • Updated installation instructions for OS X using the new Instant Client 12.1 release.

  • Added installation instructions for AIX and Solaris x64.

  • Some enhancements were made to the underlying DPI data access layer. These were developed in conjuction with a non- node-oracledb consumer of DPI, but a couple of changes lay groundwork for potential, user-visible, node-oracledb enhancements:

    • Allow SYSDBA connections

    • Allow session tagging

    • Allow the character set and national character set to be specified via parameters to the DPI layer

    • Support heterogeneous pools (in addition to existing homogeneous pools)

    To reiterate, these are not exposed to node-oracledb.


Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Using Oracle on OS X? Instant Client 12.1 is here

Sun, 2016-05-29 07:45

Oracle Instant Client 12.1 for OS X was just released and is now available for free download from OTN for 32-bit and 64-bit applications. Update: the bundles were re-released 14 June 2016 with a connectivity fix.

Instant Client provides libraries and tools for connecting to Oracle Database. Among other uses, languages such as C, Python, PHP, Ruby, Perl and Node.js can use Instant Client for database connectivity.

In addition to having Oracle 12.1 client features like auto-tuning, new in this release is an ODBC driver.

The install instructions have been updated to reflect the resolution of the linking issues caused by the OS X El Capitan changes with SIP to ignore DYLD_LIBRARY_PATH in sub processes. The ~/lib location required for Instant Client 11.2 on El Capitan is no longer needed with Instant Client 12.1. Note if you are creating your own apps, you should link with -rpath.

This release of Instant Client supports Mavericks, Yosemite, and El Capitan. Applications can connect to Oracle Database 10.2 or more recent. You should continue using the older 11.2 client if you need to connect to Oracle Database 9.2.

Update: Official installation doc and release notes are now on the doc portal: Oracle Database Online Documentation 12c Release 1 (12.1).

Questions and comments can be posted to the OTN forum for whichever component or tool you are using. General questions about Instant Client are best posted to the OCI Forum.

If you are interested in running Oracle Database itself on OS X, see my earlier post The Easiest Way to Install Oracle Database on Mac OS X.

Node-oracledb 1.9.1 with Promises and Node 6 Support is on NPM

Wed, 2016-05-18 05:14

Top features: Promise support. Node 6 support.

The new node-oracledb 1.9.1 release adds Promise support, it now supports the new Node 6 (as well as previous releases), and it has some welcome stability fixes. Other nice goodies are mentioned below. Thanks to everyone who gave feedback on our 1.9.0 development branch - we couldn't have done it without you.

node-oracledb 1.9.1 is now available on NPM. Installation instructions are here. Documentation is here.

The changes in node-oracledb 1.9.1 since 1.8
  • Promise support was added. All asynchronous functions can now optionally return Promises. When asynchronous functions are passed with a callback function parameter, the familiar callback flow is used. If the callback parameter is omitted, then a Promise is returned. There are some examples in the examples directory, see promises.js and webapppromises.js.

    Node Promises allow a programming paradigm that many Node users find comfortable. Some users had implemented their own Promise wrappers for node-oracledb; a few had even been published on NPM. Now the official implementation makes Promises available to everyone.

    The default Promise library is used for Node 0.12, 4, 5 and 6. It can be easily overridden if you wish to incorporate your own implementation. This is also useful for Node 0.10 where there is no native library.

  • A new toQueryStream() method was added for ResultSets. It lets REF CURSORS be fetched via Readable Streams. See the example refcursortoquerystream.js

    It can also be used with ResultSets from top-level queries, allowing them to be converted to streams. However the existing connection.queryStream() method will probably be easier to use in this scenario.

  • Aliases pool.close() and connection.close() have been added for pool.terminate() and connection.release() respectively. This should make it easier to remember which method to use for releasing a connection, terminating a connection pool, or closing a ResultSet: you can just use close().

  • An experimental query Stream _close() method was added. It allows query streams to be closed without needing to fetch all the data. It is marked experimental because there isn't good information about how safe interrupting the current Node Streams implementation is.

    Calling _close() invokes pause(), if necessary. On Node 0.10 you may get a pause event even if you had previously paused the stream. This is because Node 0.10 doesn't support the isPaused() call.

  • Upgraded to NAN 2.3 for Node 6. This removes the deprecation warnings that Node 6 was generating with the older NAN 2.2.

    Older versions of Node can still be used, of course.

  • Mitigated some annoying, seemingly random crashes if JavaScript objects were garbage collected too early. While many well programmed apps naturally hold the relevant JavaScript objects until no longer required, other apps don't. Premature garbage collection could affect these latter scripts if they finished quickly or when under load.

    A similar change was made to Lob buffers used during LOB insertion.

  • Memory 'improvements':

    • Fixed some memory leaks when using ResultSets.

    • Fixed a memory leak with the Pool queue timer map.

    • Fixed memory release logic when querying LOBs and an error occurs.

  • Removed an extra call to getRows() made by queryStream() at end-of-fetch.

  • Error changes:

    • Some method parameter validation checks, such as the number or types of parameters, will now throw errors synchronously instead of returning errors via the callback.

    • Improved validation for fetchInfo usage.

    • Increased the internal buffer size for Oracle Database error messages.

    • Altered some node-oracledb NJS-xyz error message text for consistency.

  • The test suite is no longer automatically installed when installing with npm install oracledb from NPM. The test suite was (and will be) getting bigger and bigger. It is not being used by most people, so installing it automatically is wasteful.

    You can still run the tests using a GitHub clone. The updated test README has instructions for this. To allow tests to be moved and run anywhere, we left the tests' require('oracledb') calls without a path prefix so you may need to set NODE_PATH as described.

  • Fixed a symbol name redefinition warning for DATA_BLOB when compiling on Windows.

Overall node-oracledb 1.9.1 is a significant milestone with the addition of Promise support, the support for Node 6, and the general resiliency changes. I'm very happy with this release and would encourage upgrading to it.

What's Next?

What's next? There are still lots of enhancements for node-oracledb on the wish list. Please let us know your priorities.

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Emoji? Look at the Oracle New Gen Developers

Fri, 2016-05-13 05:36

Forget emoji, look at the Oracle New Gen Developers!

Getting a C++11 compiler for Node 4, 5 and 6 on Oracle Linux 6

Sun, 2016-05-01 23:36

A newer compiler is needed on Oracle Linux 6 when you want to use add-ons like node-oracledb with Node 4 or later. This is because add-ons for those versions need to be built with a C++11 compatibile compiler. The default compiler on OL 6 doesn't have this support. OL 7 does have such a compiler, so these instructions are not needed for that version.

For OL 6 the easist way to get a new compiler is from the Software Collection Library (SCL). You enable the software collection yum channel, run a yum install command, and then the compiler is immediately available to use. Detailed installation SCL instructions are in the manual.

The steps below show how to install node-oracledb on Oracle Linux 6 for Node.js 4 or later.

Enabling the Software Collection Library

If you are using (formerly known as then edit /etc/yum.repos.d/public-yum-ol6.repo and enable the ol6_software_collections channel:

  name=Software Collection Library release 1.2 packages for Oracle Linux 6 (x86_64)

If necessary, you can get the latest channel list from and merge any updates from it into your existing /etc/yum.repos.d/public-yum-ol6.repo file.

Alternatively, if your machine has a ULN support subscription, you can subscribe to the Oracle Software Collections 1.2 for Oracle Linux 6 channel in the Manage Subscription page on

Installing the Updated Compiler

Once the channel is enabled, install the updated compiler with:

  yum install scl-utils devtoolset-3

This will install a number of packages that comprise the complete, updated tool set.

Installing node-oracledb

Installing node-oracledb on Node 4 (or later) is the same as in install instuctions, but using the new compiler. The Oracle Linux manual chapter Using the Software Collection Version of a Command shows various ways to enable the dev toolset.

In summary, to install node-oracledb on Node 4 or later using Oracle Linux 6, first install an Oracle client such as Instant Client. If you have anything except the Instant Client RPM packages, tell the installer where the libraries and header files are located, for example:

  export OCI_LIB_DIR=$HOME/instantclient
  export OCI_INC_DIR=$HOME/instantclient/sdk/include

If you are behind a firewall, set your proxy:

  export http_proxy=

In my development environments I often find some cleanup helps:

  which npm && rm -rf $(npm root)/oracledb $(npm root)/nan $HOME/.node-gyp $HOME/.npm \
        && npm cache clean

Now node-oracledb can be installed using the newer compiler:

  scl enable devtoolset-3 -- npm install oracledb
Using Node

Now you can use Node:

  $ node version.js 
  node.js version: v4.4.3
  node-oracledb version: 10900
  node-oracledb text format: 1.9.0
  oracle client library version: 1201000200
  oracle client library text format:
  oracle database version: 1201000200
  oracle database text format:

  $ cat /etc/oracle-release 
  oracle linux server release 6.7

node-oracledb 1.9.0-Dev Branch with Promises is on GitHub

Mon, 2016-04-18 23:22

Top features: Promise support

node-oracledb 1.9.0-Development is now available only as a development-only branch on GitHub. It adds Promise support and some other goodies mentioned below. The plan is to incorporate any user feedback, stabilize the features, improve documentation, improve test covereage, and run stress tests. Once this is all done, then a production release to npm will be made. Personally I think this will take 2-4 weeks, but it is totally dependent on what is uncovered by you, the user.

Since this is a development branch, features are subject to change. Use GitHub Issue #410 to ask design questions and discuss the branch.

Install by setting OCI_LIB_DIR and OCI_INC_DIR as normal, and running npm install oracle/node-oracledb.git#dev-1.9. Or install from GitHub by cloning the repository, checking out the dev-1.9 branch, setting OCI_LIB_DIR and OCI_INC_DIR, and install with npm install

Anyone is welcome to report test results, contribute new tests or update documentation (or code!) to help us get a production release out faster. You do need to have your OCA accepted first before we can look at any pull requests.

As you can see, most of the doc is ready, and there are a solid number of tests for new functionality. You can help us by testing your own favorite Promise library, as we are only planning on testing the default Promise implementation in Node 0.12, 4 and 5. Also check out how connections are handled as you may like a different style - let us know.

In node-oracledb 1.9-dev:

  • Promise support was added. All asynchronous functions can now return promises. The default Promise library is used for Node 0.12, 4 and 5. It can be easily overridden if you wish to incorporate your own implementation.

    The current implemention typically requires two promise chains. Let us know what you think. There are solutions that could be made. What do you want?

  • A new toQueryStream() method was added. It is for ResultSets. It lets REF CURSORS be transformed into Readable Streams. It can also be used to convert ResultSets from top-level queries to streams, however the existing connection.queryStream() method will probably be easier to use for these queries.

  • An experimental query Stream _close() method was added. It allows query streams to be closed without needing to fetch all the data. It is marked experimental because there isn't good information about whether the current Node Streams implementation really allows interruptions.

    Open question: Should _close() invoke pause() internally?

  • Aliases pool.close() and connection.close() have been added for pool.terminate() and connection.release() respectively. This should make it easier to remember which method to use for releasing a connection, terminating a connection pool, or closing a ResultSet: you can just use close().

  • Some method parameter validation checks, such as the number or types of parameters, will now throw errors synchronously instead of returning errors via the callback.

  • Removed an extra call to getRows() made by queryStream() at end-of-fetch.

  • Some annoying, seemingly random crashes caused by connections being garbage collected while still in use should no longer occur. These were more common in smaller scripts that finished quickly, but could be seen in systems under load.


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 OCI8 2.0.11 and 2.1.1 are available on PECL

Sun, 2016-04-17 23:51

I've released PHP OCI8 2.0.11 (for supported PHP 5.x versions) and 2.1.1 (for PHP 7) to PECL. Windows DLLs on PECL been built by the PHP release team. The updated OCI8 code has also been merged to the PHP source branches and should land in the future PHP 5.6.21 and PHP 7.0.7 source bundles, respectively.

PHP OCI8 2.1.1 fixes a bug triggered by using oci_fetch_all() with a query having more than eight columns. To install on PHP 7 via PECL, use pecl install oci8

PHP OCI8 2.0.11 has one fix for a bind regression with 32-bit PHP. To install on PHP 5.x use pecl install oci8-2.0.11

My old Underground PHP and Oracle Manual still contains a lot of useful information about using PHP with Oracle Database. Check it out!

Node-oracledb 1.8 has a streaming mode for queries (Node.js add-on for Oracle Database)

Thu, 2016-03-24 06:17

Node-oracledb 1.8.0, the Node.js add-on for Oracle Database, is on NPM.

Top new changes: New Query Result Streaming. Use DRCP connect strings only with a connection pool.

I want to start by saying thanks to all contributors past and current! It is the community that defines the product.

Query Result Streaming

Node Streams can now be used for queries after a pull request from Sagie Gur-Ari was merged. A new connection.queryStream() method returns a Readable Stream. Of course, if you prefer callbacks, the existing ResultSet feature can continue to be used.

The new query stream feature is implemented as a wrapper around the ResultSet Class. In particular it uses resultset.getRows() to fetch a subset of data, each row of which will generate a data event. The only reason to understand this is that getRows() takes a parameter to specify how many rows to fetch. We had some (well, a lot of) debate about to set this value and keep the API simple. For the moment, the value of oracle.maxRows is used. Note the value does not affect how many rows are returned by streamQuery() events because getRows() will be repeatedly called when more rows are needed. Instead, this parameter is used to tune stream performance. In the near future, when PR 361 is reviewed, we may introduce a specialized tuning parameter.

We also want to look at ways of interrupting the streams early. But, in node-oracledb 1.8, make sure to read to the end of the query to avoid leaking a cursor. The ResultSet close() is executed internally; you do not need to call it to release ResultSet resources.

An example of query streaming is:

  var stream = connection.queryStream(
    'SELECT first_name, last_name FROM employees ORDER BY employee_id'
  stream.on('error', function (error) {
  stream.on('metadata', function (metadata) {
  stream.on('data', function (data) {
  stream.on(end, function () {
      function(err) {
	if (err) {

There is a runnable example in examples/selectstream.js. Other useful examples are in the test file test/stream1.js.

Use DRCP Connect Strings Only With a Connection Pool

In node-oracledb 1.8 you must now use a connection pool if your connect string requests a DRCP connection. Previously this was just a best practice. Now it is enforced.

Connect strings that request DRCP connections look either like:

  connectString : "mymachine/mydbservice:pooled"


  connectString : "SALES"

where the SALES connect identifier maps to a tnsnames.ora entry specifying SERVER=POOLED, for example:


If you try oracledb.getConnection(...) you will get an error ORA-56609: Usage not supported with DRCP.

Instead use a connection pool, see node-oracledb Connection Pooling documentation:

  oracledb.createPool (
      user          : "hr"
      password      : "welcome"
      connectString : "mymachine/mydbservice:pooled"
    function(err, pool)
      pool.getConnection (
	function(err, connection)
	. . .  // use connection

In the unlikely case where the Node process is short-lived and you really, really just want a single connection, create a pool with a single session; the createPool() option attributes can be: poolMax: 1, poolMin: 1, poolIncrement: 0

Millisecond Precisions

A pull request from Antonio Bustos has helped make some tests more portable by removing some time ambiguity. The Oracle DATE data type does not contain milliseconds. If an application inserts a JavaScript date with milliseconds, the DATE tests will now only compare the non-millisecond date components to validate results.

Windows Debug Builds

Kubo Takehiro (who is the maintainer of the popular Ruby ruby-oci8 extension) spotted a hierarchy problem with node-oracledb Windows Debug build options. The binding.gyp entry has now been fixed.

Other Changes

The driver name is now set to "node-oracledb : 1.8.0". This is visible to DBAs, for example in the V$SESSION_CONNECT_INFO view. It lets DBAs see what apps, and what versions, are connecting to the database.

The pool queue stats now show the start time of the pool. A couple of parameter check bugs were also fixed. See CHANGELOG for details.


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.

Using SQL*Plus Instant Client 11.2 on OS X El Capitan

Wed, 2016-03-02 23:43

The installation steps for using SQL*Plus 11.2 (Instant Client) and other OCI-based applications have necessarily changed since OS X El Capitan broke DYLD_LIBRARY_PATH. The updated instructions are given on the Instant Client Downloads for Mac OS X (Intel x86) page.

Update: Instant Client 12.1 is out and installation is easier. Check out the above link for instructions

Here are the 11.2 instructions repeated. The steps also work on earlier versions of OS X:

  1. Download the desired Instant Client ZIP files from OTN. All installations require the Basic or Basic Lite package.

  2. Open a terminal and unzip the packages into a single directory such as "~/instantclient_11_2". For example, to use SQL*Plus:

    cd ~
  3. Create the appropriate libclntsh.dylib link for the version of Instant Client. For example:

    cd ~/instantclient_11_2
    ln -s libclntsh.dylib.11.1 libclntsh.dylib

    Note: OCCI programs will additionally need:

    ln -s libocci.dylib.11.1 libocci.dylib
  4. Add links to "~/lib" for required Basic package libraries. For example, to use OCI programs (including SQL*Plus, Python's cx_Oracle, PHP's OCI8, Node.js's node-oracledb, and Ruby's ruby-oci8 driver):

    mkdir ~/lib
    ln -s ~/instantclient_11_2/libclntsh.dylib.11.1 ~/lib/
    ln -s ~/instantclient_11_2/{libnnz11.dylib,libociei.dylib} ~/lib/
  5. To run SQL*Plus, add its libraries to "~/lib", and update PATH. For example:

    ln -s ~/instantclient_11_2/{libsqlplus.dylib,libsqlplusic.dylib} ~/lib/
    export PATH=~/instantclient_11_2:$PATH

node-oracledb 1.7.0 has a connection pool queue (Node.js add-on for Oracle Database)

Mon, 2016-02-29 07:56

Node-oracledb 1.7.0, the Node.js add-on for Oracle Database, is on NPM.

Top features: a new connection pool queue to make apps more resilient, and "Bind by position" syntax for PL/SQL Index-by array binds.

This release has a couple of interesting changes as well as some small bind fixes. A few reported build warnings with some compilers were also squashed.

Extended PL/SQL Index-by Array Bind Syntax

To start with, a followup PR from @doberkofler completes his PL/SQL Index-by array binding support project. In node-oracledb 1.7 he has added "bind by position" syntax to the already existing "bind by name" support. Thanks Dieter! The "bind by position" syntax looks like:

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

Personally I'd recommend using bind by name for clarity, but this PR makes the feature congruent with binding scalar values, which is always a good thing.

Documentation is at PL/SQL Collection Associative Array (Index-by) Bind Parameters.

New Transparent JavaScript Wrapper for Existing Classes

The other major change in 1.7 is a new JavaScript wrapper over the current node-oracledb C++ API implementation, courtesy of some community discussion and the direction that users seemed to have been heading in: creating similar wrappers. It was also the result of some 'above and beyond' overtime from Dan McGhan who did the project. This wrapper should be transparent to most users. It gives a framework that will make it easier to extend node-oracledb in a consistent way and also let developers who know JavaScript better than C++ contribute to node-oracledb.

New Connection Pool Queue Enabled by Default

The layer has let Dan add his first new user feature: a request queue for connection pooling. It is enabled by a new Boolean pool attribute queueRequests. If a pool.getConnection() request is made but there are no free connections (aka sessions) in the pool, the request will now be queued until an in-use connection is released back to the pool. At this time the first request in the queue will be dequeued, and the underlying C++ implementation of pool.getConnection() will be called to return the now available connection to the waiting requester.

A second new pool attribute queueTimeout uses setTimeout to automatically dequeue and return an error for any request that has been waiting in the queue too long. The default value is 60000 milliseconds, i.e. 60 seconds. In normal cases, when requests are dequeued because a connection does become available, the timer is stopped before the underlying C++ layer gets called to return the connection.

The pool queue is enabled by default. If it is turned off, you get pre-1.7 behavior. For example if more requests are concurrently thrown at an app than the poolMax value, then some of the pool.getConnection() calls would likely return an error ORA-24418: Cannot open further sessions. When enabled, the new queue nicely stops this error occurring and lets apps be more resilient.

The pool option attribute _enableStats turns on lightweight gathering of basic pool and queue statistics. It is false by default. If it is enabled, applications can output stats to the console by calling pool._logStats() whenever needed. I think it will be wise to monitor the queue statistics to make sure your pool configuration is suitable for the load. You don't want the queue to be an invisible bottle neck when too many pool.getConnection() requests end up in the queue for too long. Statistics and the API may change in future, so the attribute and method have an underscore prefix to indicate they are internal.

Connection Queue Example

To look at an example, I used ab to throw some load at an app based on examples/webapp.js I used a load concurrency of 25 parallel requests. The pool had a maximum of 20 sessions in its pool. The extra load was nicely handled by the connection queue without the application experiencing any connection failures.

I'd modified the app to check for a particular URL and dump statistics on request:

    . . .
    var hs = http.createServer (
      function(request, response)
        var urlparts = request.url.split("/");
        var arg = urlparts[1];
        if (arg === 'stats') {
    . . .

Here is snapshot of the output from _logStats() at one point during the test:

Pool statistics: connection requests: 26624147 requests enqueued: 5821874 requests dequeued: 5821874 requests failed: 0 request timeouts: 0
...max queue length: 6
...sum of time in queue (milliseconds): 13920717
...min time in queue (milliseconds): 0
...max time in queue (milliseconds): 1506
...avg time in queue (milliseconds): 2
...pool connections in use: 12
...pool connections open: 20
Related pool attributes:
...queueRequests: true
...queueTimeout (milliseconds): 0
...poolMin: 10
...poolMax: 20
...poolIncrement: 10
...poolTimeout: 0
...stmtCacheSize: 30
Related environment variables:
...process.env.UV_THREADPOOL_SIZE: undefined

The connection pool was semi-arbitrarily configured for testing. It started out with 10 sessions open (poolMin) and as soon as they were in use, the pool would have grown by another 10 sessions (poolIncrement) to the maximum of 20 (poolMax).

What the stats show is that not all pool.getConnection() requests could get a pooled connection immediately. About 20% of requests ended up waiting in the queue. The connection pool poolMax is smaller than optimal for this load.

The queue was never large; it never had more than 6 requests in it. This is within expectations since there are at least 5 more concurrent requests at a time than there are connections available in the pool.

If this were a real app, I might decide to increase poolMax so no pool.getConnection() call ever waited. (I might also want to set poolTimeout so that when the pool was quiet, it could shrink, freeing up DB resources.) However the average wait time of 2 milliseconds is small. If I don't have DB resources to handle the extra sessions from a bigger pool, I might decide that a 2 millisecond wait is OK and that the pool size is fine.

At least one connection spent 1.5 seconds in the queue. Since I know my test infrastructure I'm guessing this was when the pool ramped up in size and my small, loaded DB took some time to create the second set of 10 sessions. Maybe I should experiment with a smaller poolIncrement or bigger poolMin?

Another important variable shown in the stats is UV_THREADPOOL_SIZE. I'd not set it so there were the default four worker threads in the Node process. Blindly increasing poolMax may not always help throughput. If DB operations take some time, you might find all threads get blocked waiting for their respective DB response. Increasing UV_THREADPOOL_SIZE may help improve application throughput.

The best settings for pool configuration, UV_THREADPOOL_SIZE, and any DRCP pool size will depend on your application and environment.

Connection Pool Queue Statistics

The table below shows the node-oracledb 1.7 pool statistics descriptions. These stats and the APIs to enable and log them may change in future versions of node-oracledb. I look forward to getting some PRs, for example to add a standard logging capability which the stats generation can be part of.

Connection Pool MetricDescription

total connection requests

Number of pool.getConnection() calls made for this pool

total requests enqueued

Number of connections that couldn't be satisfied because every session in the the pool was already being used, and so they had to be queued waiting for a session to be returned to the pool

total requests dequeued

Number of connection requests that were removed from the queue when a free connection has become available. This is triggered when the app has finished with a connection and calls release() to return it to the queue

total requests failed

Number of connection calls that invoked the underlying C++ pool.getConnection() callback with an error state. Does not include queue request timeout errors.

total request timeouts

Number of connection requests that were waiting in the queue but exceeded the queueTimeout setting. The timeout is triggered with a JavaScript setTimeout call

max queue length

Maximum number of connection requests that were ever waiting at one time

sum of time in queue

Total sum of time that connection requests have been waiting in the queue

min time in queue

Smallest amount of time that any request waited in the queue

max time in queue

Longest amount of time that any request waited in the queue

avg time in queue

Derived from the sum of time value divided by the number of requests enqueued

pool connections in use

A metric returned by the underlying Oracle C client session pool implementation. It is the number of currently active connections in the connection pool pool connections open

Also returned by the underlying library. It shows the number of currently open sessions in the underlying connection pool

Note that the sum of time in queue, the min time in queue and the max time in queue values are calculated when queued requests are removed from the queue, so they don't record the amount of time for requests still waiting in the queue.


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.