The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Node-oracledb 4.2 with Improved Data Type Binding

Christopher Jones
Senior Principal Product Manager

Release announcement: A new release of node-oracledb, the Node.js module for accessing Oracle Database, is available from npm.

Top features: Binding of NCHAR and new types; Finer grained control on bind data types; New result.lastRowid support; Client-initiated CQN connections






Significant changes in node-oracledb 4.2 are:

  • New bind types and improvements.

    First, some background: when executing SQL, binding data values to the statement (instead of using string concatenation) is always recommended for scalability and security. A fundamental design feature (for performance) of Oracle binding is that the client doesn't know the database data type prior to (or as part of) the connection.execute() call: node-oracledb simply says to the database "here is some data, please perform the bind as if it is type X", or "here is an empty buffer, please give me back data as type Y". Internally, node-oracledb passes the numeric value of the bind type from your application code down to the Oracle Client libraries, and they do their magic conversion between the client and database data types when the data is transferred.

    Prior to node-oracledb 4.2, your application calls to connection.execute() used binding data type values that related closely to the JavaScript data types. For example, applications used the type value oracledb.DATE when binding JavaScript Date objects, regardless if Oracle database type was DATE or TIMESTAMP WITH LOCAL TIME ZONE. (I should point out that this still all works in 4.2, and will continue to work. It will likely remain the common usage.)

    In node-oracledb 4.2 we wanted to allow binding of JavaScript Strings to NCHAR, NVARCHAR and NCLOB columns. Simply specifying the bind type as oracledb.STRING isn't sufficient information for Oracle libraries to do proper character set mapping. Also we wanted to add other types and give experts finer grained control over binding, for example when binding JavaScript Date values so that the correct overridden PL/SQL function would be invoked. Another example is to circumvent SQL optimizer quirks where the bind type affects the execution plan. These are rare cases, and NCHAR is not so common, but the new changes have their place.

    Now in node-oracledb 4.2 you can optionally specify the bind type using the database type system, for example you can use oracledb.DB_TYPE_TIMESTAMP when binding a Date to a TIMESTAMP column. The new type constants supported are oracledb.DB_TYPE_DATE, oracledb.DB_TYPE_CHAR, oracledb.DB_TYPE_NCHAR, oracledb.DB_TYPE_NVARCHAR, oracledb.DB_TYPE_NCLOB, oracledb.DB_TYPE_BINARY_DOUBLE, oracledb.DB_TYPE_BINARY_FLOAT, oracledb.DB_TYPE_BINARY_INTEGER, oracledb.DB_TYPE_TIMESTAMP, and oracledb.DB_TYPE_TIMESTAMP_TZ. Conveniently while we were adding these, support for binding oracledb.DB_TYPE_BOOLEAN was made via a contribution from Diego Arce. Thanks Diego - that was good timing!

    Review the new bind type table in the documentation for more detail. To repeat: if your code's bind type values (e.g. oracledb.NUMBER), or the default values chosen by node-oracledb, have been fine, then you don't need to change anything.

  • After INSERT, UPDATE, DELETE or MERGE statement, the result object returned by connection.execute() will now contain a lastRowid property containing the ROWID of the row affected by the operation. If the SQL statement affects more than one row, only the last ROWID is returned.

  • If you are using Oracle Database 19.4 (or later) and node-oracledb is linked with Oracle Client 19.4 (or later) libraries, then you can take advantage of the new Continuous Query Notification (CQN) mode clientInitiated for 'client initiated' connections. In CQN documentation these are referred to as 'secure connections' because the CQN channel gets established without the database needing to be able to initiate connection to node-oracledb.

  • For users who are streaming LOBs using the node-oracledb Lob object, we updated the best practice recommendations and examples to use the Node.js 8 Stream destroy() method in place of the node-oracledb close() method. The latter is now deprecated. To migrate, change the method calls from lob.close() to lob.destroy(), stop passing a callback, and optionally pass an error. Then review your event handling code.

See the CHANGELOG for all changes and bug fixes. Thanks as always to developers, contributors and users who have worked on this release.

One final note: this will be the last node-oracledb release that we'll test with Node.js 8, which has finished its maintenance period and is EOL. If you haven't already upgraded to Node.js 10 or 12, now is the time to do it.

Let us know how you are using node-oracledb, and what other features you'd like in future releases.


Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

Issues and questions about node-oracledb can be posted on GitHub or Slack (link to join Slack).

Follow us on Twitter or Facebook.

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.