Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 13 hours 54 min ago

ODPI-C 2.0.0-beta.4 released: More & Better-er

Wed, 2017-05-24 17:22

ODPI-C 2.0.0-beta.4 has been released to GitHub.

ODPI-C is an abstraction layer for Oracle Call Interface OCI.

This release has incremental improvements, based on user feedback and as a result of continued testing. There was some discussion around best practice number handling, and how to make use of ODPI-C 'variable' structures without requiring a DB connection. These have resulted in a few tweaks. See the release notes for all changes. The highlights in this release are:

  • Added support for getting/setting attributes of objects or element values in collections that contain LOBs, BINARY_FLOAT values, BINARY_DOUBLE values and NCHAR and NVARCHAR2 values

  • Enabled temporary LOB caching in order to avoid disk I/O.

  • Changed default native type to DPI_ORACLE_TYPE_INT64 if the column metadata indicates that the values are able to fit inside a 64-bit integer.

  • Added function dpiStmt_defineValue(), which gives the application the opportunity to specify the data type to use for fetching without having to create a variable.

In addition, a new macro DPI_DEBUG_LEVEL can be enabled during compilation to display logging and trace information. Personally I'd set it to DPI_DEBUG_LEVEL_FREES in this initial Beta phase to check whether we've made any mistakes with resource handling.

We'd like to wrap up the Beta phase soon, so please continue pounding on ODPI-C and send us feedback.

If you want to see ODPI-C in a bigger project, check out Python cx_Oracle 6.0b2, which has been updated to use this new release of ODPI-C.

Heading to Tokyo

Sun, 2017-05-14 19:29

It's all set and I'm ready to go.  I'll be speaking on Python and Oracle Database at the Oracle Code Conference in Tokyo this week. 

My abstract is "The past year has been big for the Python cx_Oracle driver. In this session see how existing and new features of the cx_Oracle API for Oracle Database can be used to build functional, high-performance apps that make best use of database functionality and resources."

After a proposed vacation to Japan this year had to be postponed, it's going to be great to spend even a few days visiting. If you're in town, let me know.

Whoa! Release News for PHP OCI8, node-oracledb, Python cx_Oracle, Instant Client and ODPI-C

Wed, 2017-05-10 07:57

I'm just catching up on some releases that happened prior/during my vacation. Here are some things you may have missed tweets on:

  • PHP OCI8 2.1.4 for PHP 7 was released to PECL. This version corresponds to the OCI8 code in PHP 7.0.18 and 7.1.4. If you are on earlier versions, you should upgrade to avoid an uninitialized memory issue, which is bound to cause random things when you least want them.

  • Node-oracledb 1.13.1 was released, fixing a regression binding NULL to PL/SQL procedures.

  • An Instant Client Docker image was released on the Docker Store and on the Oracle Container Registry. This image has the Basic, SDK and SQL*Plus packages, making it ideal for deploying your favorite scripting language.

  • Python cx_Oracle 6.0 Beta 1 was released to PyPI. More on this in a future dedicated post. In brief, there was an underlying re-architecture. The full release notes are here. Install it with python -m pip install cx_Oracle --pre and let us know what you find.

  • ODPI-C 2.0 Beta 3 was released on GitHub. There were a few tweaks that you can read about here.

Yes, it has been busy.

Scripting Languages and Oracle Database Cloud: How To Do it

Fri, 2017-05-05 00:50
Logo

Oracle Cloud is a great fit for your application deployment.  Say no more! I've just published some how-to's on using scripting languages like Node.js, Python, PHP, R and Ruby with Oracle Database Cloud and with the PaaS "Oracle Database Express Cloud Service".  You can use any of the mentioned languages, use other OCI or OCCI-based languages, or take advantage of the Application Container Cloud Service to deploy of Node.js and PHP applications:

 

Welcome to the new OPAL look & feel

Tue, 2017-05-02 20:21

Welcome to the new look & feel of blogs.oracle.com/opal

As you can probably guess, this is really just a test post!

Oracle blogging platform infrastructure change; normal transmission will resume shortly

Thu, 2017-04-13 16:14
The software driving blogs.oracle.com is undergoing a huge upgrade. There are spreadsheets of detailed plans and dates and training videos floating around. Yay!

I'll refrain from posting over the next couple of weeks until the content freeze is finished and my cutover is complete. My 'opal' URL will remain the same. New followers may not know the history behind the name. This blog was started when I was in the Linux group working solely on PHP. The OPAL acronym stood for Oracle PHP Apache Linux, i.e. a play on 'LAMP'. The little image I've used in my 'About' box is one I took in Coober Pedy, an Opal mining town in Australia.

Oracle blogging platform infrastructure change; normal transmission will resume shortly

Thu, 2017-04-13 16:14
The software driving blogs.oracle.com is undergoing a huge upgrade. There are spreadsheets of detailed plans and dates and training videos floating around. Yay!

I'll refrain from posting over the next couple of weeks until the content freeze is finished and my cutover is complete. My 'opal' URL will remain the same. New followers may not know the history behind the name. This blog was started when I was in the Linux group working solely on PHP. The OPAL acronym stood for Oracle PHP Apache Linux, i.e. a play on 'LAMP'. The little image I've used in my 'About' box is one I took in Coober Pedy, an Opal mining town in Australia.

ODPI-C 2.0.0 Beta 2 is out with runtime client linking

Tue, 2017-03-28 17:04

Anthony Tuiningajust released ODPI-C2.0.0-beta.2 on GitHub. The release notes list all changes.

ODPI-C is an open source library of C code that simplifies the useof common Oracle Call Interface (OCI) features for Oracle Databasedrivers and user applications. It sits on top of OCI and requiresOracle client libraries.

The big change in this release is the addition of runtime dynamiclinking of the Oracle client libraries. It was initiated by a commentfrom Vincent Rogier, authorof OCILIB when hetook a look at ODPI-C.

One you have downloadedODPI-C source (and written something using its API, or copied thesamples) you can compile your code without needing the Oracleheader files (e.g. you don't need the Oracle Instant Client SDK). Allyou need to have are the Oracle client libraries from Oracle Database11.2, 12.1 or 12.2 in your path when you run your application. Theseclient libraries can be found in the Instant Client Basic or Basic Light packages, in a databaseORACLE_HOME, or in a full Oracle Client install. Your ODPI-C -basedapplication will use the first Oracle client libraries in your loadpath. More details about configuring the Oracle client are in the documentation. In summary you only need to build your applicationonce and it will run using Oracle 11.2, 12.1 or 12.2 clientlibraries.

If you want to see ODPI-C in action in a bigger 'example', look atthe masterbranch of cx_Oracle.

ODPI-C 2.0.0 Beta 2 is out with runtime client linking

Tue, 2017-03-28 17:04

Anthony Tuininga just released ODPI-C 2.0.0-beta.2 on GitHub. The release notes list all changes.

ODPI-C is an open source library of C code that simplifies the use of common Oracle Call Interface (OCI) features for Oracle Database drivers and user applications. It sits on top of OCI and requires Oracle client libraries.

The big change in this release is the addition of runtime dynamic linking of the Oracle client libraries. It was initiated by comment from Vincent Rogier, author of OCILIB when he took a look at ODPI-C.

One you have downloaded ODPI-C source (and written something using its API, or copied the samples) you can compile your code without needing the Oracle header files (e.g. you don't need the Oracle Instant Client SDK). All you need to have are the Oracle client libraries from Oracle Database 11.2, 12.1 or 12.2 in your path when you run your application. These client libraries can be found in the Instant Client Basic or Basic Light packages, in a database ORACLE_HOME, or in a full Oracle Client install. Your ODPI-C -based application will use the first Oracle client libraries in your load path. More details about configuring the Oracle client are in the documentation. In summary you only need to build your application once and it will run using Oracle 11.2, 12.1 or 12.2 client libraries.

If you want to see ODPI-C in action in a bigger 'example', look at the master branch of cx_Oracle.

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

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 workswith 12.2 client libraries (as well as 11.2 and 12.1). With 12.2client, you can connect to 11.2, 12.1 and 12.2 databases, usingOracle's cross version interoperability which allows Oracle clients toconnect to older (or newer) databases. One feature I like in the 12.2client is its internal Session Pool connection checking, whichtransparently improves application connection reliability in unstablenetwork environments.

BLOB Enhancements in node-oracledb

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

There is an example of fetchAsBuffer inexamples/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 resultsvar 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 isto use a fetchInfo option for the column atexecute() 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 wasbased on PR #470 submitted by Hariprasad Kulkarni. In an array bindsituation, if a value with an unexpected data type is used, thenmessages NJS-037 and NJS-052 now give the bind variable name (orposition), and the index of the unexpected data in the input dataarray. This makes it easier to find and fix the problem. The PR ismuch 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 andBLOBs.

Plans for node-oracledb version 2

We are now going to work on a version 2 branch that incorporatesthe new ODPI-C layer. The code is mostly ready, though testing will takesome time. We'll push a development release to GitHub soonish so youcheck it out and comment during the stabilization phase. Initiallynode-oracledb 2.x will have the same functionality as 1.x, with a fewsmall 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 earlierannouncement.

Resources

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

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.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, theextremely popular Python interface for Oracle Database. Binary andsource bundles can be installed from PyPi, as normal.

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

This release marks the move of the source coderepository and homepage to GitHub. User documentation remains on readthedocs. The cx_Oracle mail list also continues to operate, but you may find it easierto transition to GitHubfor questions and issues.

The New cx_Oracle 5.3

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

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

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 agreat history of database support. The high level features thatyou'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 hasalready 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 opensource library of C code that simplifies and standardizes the use ofcommon Oracle Call Interface (OCI) features, such as those used bycx_Oracle. ODPI-C was recently released on Github. As you can seefrom the cx_Oracle source code, the master branch of cx_Oracle code hasbeen updated to use ODPI-C. If you want to test the current state ofthis future release, download a bundle from GitHub and build it. Therelease notes (so far) for user visible changes are here.

Summary

The move of cx_Oracle to under the umbrella of Oracle has givenAnthony more time to focus on cx_Oracle and on making database accessbetter for all developers.

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

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

cx_Oracle Resources

Home Page

Documentation

Mail List | GitHub Issues

Source Code

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 insunny, fantastic San Francisco. I'm in my old neighborhood, enjoyinga familar view of the Bay Bridge from the speaker room window. Thereis a big hum around me from sessions and demo booths as developers arenetworking and learning.

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

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

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

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 DatabaseProgramming Interface for C. It sits on top of OCI and offers analternative programming experience.

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

ODPI-C's goal is to expose common OCI functionality in a readilyconsumable way to the C or C++ developer. OCI's API is extremelyflexible and is highly efficient. It gives a lot of fine-grainedcontrol to the developer and has a very wide range of use cases.ODPI-C is also flexible but is aimed primarily at language drivercreators. These creators are programming within the confines of ascripting language's type system and semantics. The languages oftenexpose simplified data access to users through cross-platform,'common-denominator' APIs. Therefore it makes sense for ODPI-C toprovide easy to use functionality for common data access, while stillallowing the power of Oracle Database to be used.

Of course ODPI-C isn't just restricted to driver usage. If ODPI-C hasthe functionality you need for accessing Oracle Database, you can addit 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-oracledbdriver.

Releasing ODPI-C as a new, standalone project means its code can beconsumed and reused more easily. For database drivers it allowsOracle 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 AnthonyTuininga has been leading the ODPI-C effort, making full use ofhis extensive driver knowledge as creator and maintainer of theextremely popular, and full featured, Python cx_Oracledriver.

The ODPI-C feature list currently includes all the normal callsyou'd expect to manage connections and to execute SQL and PL/SQLefficiently. It also has such gems as SQL and PL/SQL object support,scrollable cursors, Advanced Queuing, and Continuous QueryNotification. The full list in this initial Beta release, in noparticular 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 tomodify ODPI-C code, there is a call to get the underlying OCI servicecontext handle.

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

Communication to the database is handled by OracleNet, so features such as encrypted communication and LDAP can beconfigured.

ODPI-C's API makes memory and resource management simpler,particularly for 'binding' and 'defining'. A reference countingmechanism adds resiliency by stopping applications destroying in-useOCI resources. To offer an alternative programming experience fromOCI, the ODPI-C API uses a multiple getter/setter model for handlingattributes.

Using ODPI-C

ODPI-C is released as source-code on GitHub. The code makes OCIcalls and so requires an Oracle client, which must be installedseparately. Version 11.2 or later of the client is required. Thisallows applications to connect to Oracle Database 9.2 or later. Thefree Oracle Instant Client is the standard way to obtain standaloneOracle client libraries and header files.

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

ODPI-C code can be included in your C or C++ applications andcompiled like any OCI application. Or if you want to use ODPI-C as ashared 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-Cdevelopers. Also remember the underlying OCI libraries (which do allthe 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 alibrary using the sample Makefile, and then build the currentstandalone sampleprograms. 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 withthe general design but want to get your wider review. We also need tocomplete some testing and add some polish.

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

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

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

I think you'll be pleased with the direction and plans for scriptinglanguages 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

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

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

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

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.jsStreams, now Strings and Buffers can be bound to CLOBs and BLOBs forIN, IN OUT and OUT binds. CLOBs in queries can also be returneddirectly as Strings. And streaming to and from Oracle's 'temporaryLOBs' is supported. Also in this release, Writeable LOB Streams nowconclude with a 'close' event.

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

To insert large data, just bind a Node.js String or Buffer to aCLOB 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 asIN, OUT and IN OUT binds.

To fetch CLOBs as Strings from queries, usefetchAsString 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 holdup 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 theoreticallimit just under 1 GB in node-oracledb for manipulating LOBs asStrings or Buffers. Luckily you wouldn't want to be allocating thatmuch memory in Node.js applications - it's better to stream. AndNode.js itself doesn't cope well with large data. It can get unstablewith Strings past the several hundred megabyte mark. (Note that ifnode-oracledb is linked with Oracle client 11g libraries, than theupper size of Strings and Buffers is just under the 64 KB mark: themoral is use Oracle client 12c libraries - you can still connect toOracle Database 10.2, 11g and 12c).

For larger data you have multiple options for streaming. To insertinto a table column, use the existing RETURNING INTO method where you get an Oracle LOB locator and useNode.js Streamsfunctionality 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/SQLparameter, you can create a 'temporary LOB' in node-oracledb, streaminto it, and then bind it. This is new in node-oracledb 1.12

First, use the new connection.createLob() method to create a temporaryLOB, 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 toread 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 poolsession 'aliveness' checking. This is enabled by default. You maynever notice it in action but it will improve application behaviorwhere networks are unstable, or database services become temporarilyunavailable.

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

There is the overhead of a 'round trip' to the database in doing aping, so there could be some impact on scalability. But the pingsetting is time based so there is no overhead on active pools. Theping will only happen when a connection has been idle in the pool forpoolPingInterval seconds. If a connection has beenrecently used, it will be returned to the application without beingchecked. The default interval is 60 seconds. The feature can beconfigured to always-force the ping, to totally disable it, or to bewhatever interval meets your performance and quality-of-servicerequirements. The value can be set when a pool is created, or turnedon globally:

oracledb.poolPingInterval = 60;

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

The ping feature is a no-op when node-oracledb is linked withOracle 12.2 client libraries (independent of the database version atthe other end of the connection), since those libraries have analways-enabled, lightweight connection check that removes the need fornode-oracledb to do its own ping. This adds even more certainty thata connection will be valid at time of first use after agetConnection() call. Oracle 12.2 is currently availablein 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 andmore 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. Thetest suite continues to grow, as you can see when you run it. Wealso have additional stress and loads tests - overall these extra testtake days to complete. There are no guarantees, of course, butnode-oracledb is more than solid.

Resources

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

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

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

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 fetchAsStringsupport for CLOBs. Now, when CLOB columns are queried, they can bereturned directly as JavaScript Strings, without the need to useStreams. To test this in the dev release make sure node-oracledb islinked with Oracle 12c client libraries.

See the extensive manual for details and examples.

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

See my previous post for a brief intro to earlierchanges in this 1.12 series. The CHANGELOGhas all the updates. I'll blog the features in more detail when aproduction bundle is released to npmjs.com.

Resources

Issuesand questions about it can be posted on GitHub. Wevalue your input to help prioritize work on the add-on. Drop us aline!

Node-oracledb documentation is here.

Pages