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: 14 hours 3 min ago

Node-oracledb 1.13.0 is available on npm

Thu, 2017-03-16 00:18

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

Top node-oracledb feature: a fetchAsBuffer mode for fetching BLOBs.

A side note: Oracle Database 12.2 is also here! And Oracle Instant Client 12.2 is available too. Node-oracledb works with 12.2 client libraries (as well as 11.2 and 12.1). With 12.2 client, you can connect to 11.2, 12.1 and 12.2 databases, using Oracle's cross version interoperability which allows Oracle clients to connect to older (or newer) databases. One feature I like in the 12.2 client is its internal Session Pool connection checking, which transparently improves application connection reliability in unstable network environments.

BLOB Enhancements in node-oracledb

The final big piece of LOB support has been added to node-oracledb 1.13 with the addition of a new fetchAsBuffer option. This allows queries to return small BLOBs directly as Node.js Buffers. The new option is ideal for the many applications where BLOBs are kilobyte-sized or, at most, a few megabytes. Fetching as a Buffer is an alternative to the previous method of streaming which should continue to be used for larger BLOBs that can't (or shouldn't) be one chunk of Node.js memory.

There is an example of fetchAsBuffer in examples/lobselect.js:

oracledb.fetchAsBuffer = [ oracledb.BLOB ];

connection.execute(
  "SELECT b FROM mylobs WHERE id = :idbv",
  [2],
  function(err, result)
  {
    if (err) { . . . }
    if (result.rows.length === 0) { . . . } // no results

    var blob = result.rows[0][0];  // This is a Buffer

    fs.writeFile('output.jpg', blob, "binary", function(err) {
      return cb(err, connection);
    });
  });

An alternative to the global oracledb.fetchAsBuffer is to use a fetchInfo option for the column at execute() time:

  { fetchInfo: {"B": {type: oracledb.BUFFER}} }, [ . . . ]
Pull Requests

A small improvement was made to PL/SQL Index-by array binding error messages in 1.13. This was based on PR #470 submitted by Hariprasad Kulkarni. In an array bind situation, if a value with an unexpected data type is used, then messages NJS-037 and NJS-052 now give the bind variable name (or position), and the index of the unexpected data in the input data array. This makes it easier to find and fix the problem. The PR is much appreciated.

Bug fixes

Some bugs were happily squashed in node-oracledb 1.13:

  • Fixed several crashes and a memory leak using CLOBs with fetchAsString.

  • Fixed several issues including a crash using NULLs and empty strings for LOB BIND_INOUT binds.

  • Automatically clean up sessions in the connection pool when they become unusable after an ORA-56600 occurs.

Overall, this is a good release for users working with CLOBs and BLOBs.

Plans for node-oracledb version 2

We are now going to work on a version 2 branch that incorporates the new ODPI-C layer. The code is mostly ready, though testing will take some time. We'll push a development release to GitHub soonish so you check it out and comment during the stabilization phase. Initially node-oracledb 2.x will have the same functionality as 1.x, with a few small additions made possible by ODPI-C.

The plan is for node-oracledb 1.x to go into maintenance mode. Maintenance of node-oracledb 1.x will end on 1st April 2018, coinciding with the end-of-life of Node 4.

Some more details are in the earlier announcement.

Resources

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.

Python cx_Oracle 5.3 for Oracle Database Released

Thu, 2017-03-09 22:22

Today we are pleased to announce the release of cx_Oracle 5.3, the extremely popular Python interface for Oracle Database. Binary and source bundles can be installed from PyPi, as normal.

cx_Oracle 5.3 works with Python version 2.7, and with versions 3.4 and higher. You can use cx_Oracle with Oracle 11.2, 12.1 and 12.2 client libraries, allowing connection to multiple Oracle Database versions. Oracle's standard client-server version interoperability allows connection to both older and newer databases. For example Oracle 11.2 client libraries can connect to Oracle Database 10.2 or later.

This release marks the move of the source code repository and home page to GitHub. User documentation remains on readthedocs. The cx_Oracle mail list also continues to operate, but you may find it easier to transition to GitHub for questions and issues.

The New cx_Oracle 5.3

Ever since Anthony Tuininga joined Oracle, he has been hard at work adding improvements to cx_Oracle. (He's also been laying the ground work for the next big release - more on that later in this post). I think you'll be impressed with cx_Oracle 5.3. At last year's Oracle OpenWorld, Anthony talked about some of the changes and got great positive feedback. Check out his presentation here.

This is a big update. While a few of the changes are specific to Oracle Database 12c features (see release notes), you will appreciate all the things that will make your applications better even if you have older databases. And you are upgrading to Oracle Database 12.2, right? Don't forget you can get immediate access to Oracle Database 12.2 using the Oracle Database Cloud.

Key New Features
  • Support for Python 3.6. Note Python versions earlier than 2.6 are no longer supported. (You can still get cx_Oracle 5.2 from PyPI, if you really need Python 2.6 support).

  • Support for Oracle client 12.2. Note Oracle clients earlier than 11.2 are no longer supported.

  • Direct binding to SQL objects. One great use case is binding Python objects to Oracle Spatial SDO objects. See InsertGeometry.py

  • PL/SQL records and collections can be bound. Interacting with PL/SQL stored logic has never been easier.

  • Support for scrollable cursors. Go back and forth through your query results. See ScrollableCursors.py

  • Support for Advanced Queuing. Use database notifications to build micro-service applications. See AdvancedQueuing.py

  • Fetch PL/SQL implicit results. Easily return query results from PL/SQL. See ImplicitResults.py

  • Transaction Guard support. Verify transaction status after unplanned server outages. See TransactionGuard.py

  • Support for Edition Based Redefinition. Easily switch applications to use updated PL/SQL logic. See Editioning.py

  • Support for Statement Caching. Reduce costs of frequently executed statements. Now available with all connections.

  • Support for setting application context during the creation of a connection. Make application metadata more accessible to the database, including in LOGON triggers. See AppContext.py

Other New Features
  • Continuous Query Notification use has been simplified. See DatabaseChangeNotification.py and QueryChangeNotification.py

  • Support for pickling/unpickling error objects

  • Support for binding native integers

  • Support setting the internal and external name for use in distributed transactions

  • Set the maximum lifetime of connections in a session pool

  • Larger row counts (greater than 2^32)

  • Increased default query array size to 100

Changes from cx_Oracle 5.2

In line with previous deprecations and announcements, the following items are no longer usable in cx_Oracle 5.3:

  • The variable attribute maxlength has been dropped. Use bufferSize instead.

  • The variable attribute allocelems has been dropped. Use numElements instead.

  • The types UNICODE, FIXED_UNICODE and LONG_UNICODE have been dropped. Use NCHAR, FIXED_NCHAR and LONG_NCHAR instead.

  • Callbacks on OCI functions are no longer supported.

Future Deprecation Announcements

In a future cx_Oracle 6 release, the following items will be removed:

  • The module, action and clientinfo arguments to connection creation are deprecated and will be removed in cx_Oracle 6. Use the new application context instead.

  • The cursor attribute numbersAsStrings is deprecated and will be removed in cx_Oracle 6. An output type handler should be used instead.

Things you could already do in cx_Oracle 5.2

All the new features in cx_Oracle 5.3 listed above are on top of a great history of database support. The high level features that you've already been using in the previous release are:

  • SQL and PL/SQL Execution

  • Extensive data type support

  • Fetching of large result sets

  • REF CURSORs

  • Binding PL/SQL Arrays

  • Large Objects: CLOBs and BLOBs

  • Transaction Management

  • Session Pooling

  • Database Resident Connection Pooling (DRCP)

  • Privileged Connections and Database startup/shutdown

  • External Authentication

  • Continuous Query Notification

  • Row Prefetching

  • Client Result Caching

  • End-to-end tracing

  • Oracle Database High Availability Features

A future cx_Oracle 6

I said you'd be impressed with cx_Oracle 5.3, but Anthony has already looked ahead to cx_Oracle 6, which aims to be even better. Over the past year, Anthony has been working hard on ODPI-C, an open source library of C code that simplifies and standardizes the use of common Oracle Call Interface (OCI) features, such as those used by cx_Oracle. ODPI-C was recently released on Github. As you can see from the cx_Oracle source code, the master branch of cx_Oracle code has been updated to use ODPI-C. If you want to test the current state of this future release, download a bundle from GitHub and build it. The release notes (so far) for user visible changes are here.

Summary

The move of cx_Oracle to under the umbrella of Oracle has given Anthony more time to focus on cx_Oracle and on making database access better for all developers.

Overall cx_Oracle is the most comprehensive and popular scripting language driver for Oracle Database. It takes advantage of the great features built into the Oracle client libraries and into Oracle Database. And, of course, Python cx_Oracle applications can take advantage of Oracle Net features such as network data encyption.

cx_Oracle 5.3 is a big release with support for some great Oracle Database features. Take a look.

cx_Oracle Resources

Home Page

Documentation

Mail List | GitHub Issues

Source Code

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: https://oracle.github.io/odpi/

Code is at https://github.com/oracle/odpi

Documentation is at https://oracle.github.io/odpi/doc/index.html

Issues and comments are be reported at https://github.com/oracle/odpi/issues

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');

  conn.execute(
    "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.error(err.message);
      else
	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 ];

  conn.execute(
    "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];
	console.log(clob);
      }
    });

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) { . . . });
  inStream.pipe(templob);

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)
  conn.execute(
    "BEGIN lobs_in (:id, :c); END",
    { id: 3,
      c: templob }, // type and direction are optional for IN binds
    function(err)
    {
      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)
      . . .
    else
      // 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

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.

Resources

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 npmjs.com.

Resources

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 npmjs.com.

Resources

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;
  exit

Executed traditionally you get formatted output:

  SQL> @t.sql

  DEPARTMENT_ID DEPARTMENT_NAME
  ------------- ------------------------------
	     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

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

Simple!

The full CSV syntax is:

  SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

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

  "DEPARTMENT_ID","DEPARTMENT_NAME"
  10,"Administration"
  20,"Marketing"
  30,"Purchasing"
  40,"Human Resources"
  50,"Shipping"
  60,"IT"
  70,"Public Relations"
  80,"Sales"
  90,"Executive"
  100,"Finance"
(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;

  exit

Running it generates the desired JSON output:

  $ sqlplus -s -m 'csv on quote off' cj@pdb1 @t2.sql
  {"deptId":10,"name":"Administration"}
  {"deptId":20,"name":"Marketing"}
  {"deptId":30,"name":"Purchasing"}
  {"deptId":40,"name":"Human Resources"}
  {"deptId":50,"name":"Shipping"}
  {"deptId":60,"name":"IT"}
  {"deptId":70,"name":"Public Relations"}
  {"deptId":80,"name":"Sales"}
  {"deptId":90,"name":"Executive"}
  {"deptId":100,"name":"Finance"}
Summary

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.

Resources

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.

Resources

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.

Resources

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 yum.oracle.com (formerly known as public-yum.oracle.com) then edit /etc/yum.repos.d/public-yum-ol6.repo and enable the ol6_software_collections channel:

  [ol6_software_collections]
  name=Software Collection Library release 1.2 packages for Oracle Linux 6 (x86_64)
  baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/SoftwareCollections12/x86_64/
  gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
  gpgcheck=1
  enabled=1

If necessary, you can get the latest channel list from http://yum.oracle.com/public-yum-ol6.repo 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 linux.oracle.com.

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=http://my.proxy.example.com:80/

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: 12.1.0.2.0
  oracle database version: 1201000200
  oracle database text format: 12.1.0.2.0

  $ 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.

Resources

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) {
    console.error(error);
    return;
  });
  stream.on('metadata', function (metadata) {
    console.log(metadata);
  });
  stream.on('data', function (data) {
    console.log(data);
  });
  stream.on(end, function () {
    connection.release(
      function(err) {
	if (err) {
	  console.error(err.message);
	}
      });
  });

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"

or

  connectString : "SALES"

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

  SALES=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
    (HOST=mymachine)
    (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydbservice)
    (SERVER=POOLED)))

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.

Resources

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.

Pages