Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 12 hours 57 min ago

Node-oracledb 2.0.13-Development is now on GitHub

Mon, 2017-06-19 06:19

Node-oracledb 2.0.13-Development is now on GitHub. Node-oracledb is the Node.js interface for Oracle Database.

Top features: Version 2 is based on the new ODPI-C abstraction layer. Additional data type support.

The full Changelog is here. The node-oracledb 2.0.13-Development documentation is here.

I'd recommend testing and reporting any issues as early as possible during the 2.0 Development release cycle. This is a development release so we are aware of some rough edges. I'll start a GitHub issue to track them.

Full installation instructions are here but note that node-oracledb 2.0 is not yet on npm so you need to install from GitHub with:

npm install oracle/node-oracledb.git#dev-2.0

All you then need are Oracle client 12.2, 12.1 or 11.2 libraries (e.g. the Oracle Instant Client 'Basic' or 'Basic Light' package) in your PATH or LD_LIBRARY_PATH or equivalent. Users of macOS must put the Oracle client libraries in ~/lib or /usr/local/lib. The use of ODPI-C makes installation a bit easier. Oracle header files are no longer needed. The OCI_LIB_DIR and OCI_INC_DIR environment variables are not needed. A compiler with C++11 support, and Python 2.7 are still needed, but a single node-oracledb binary now works with any of the Oracle client 11.2, 12.1 or 12.2 releases, improving portability when node-oracledb builds are copied between machines. You will get run time errors if you try to use a new Oracle Database feature that isn't supported by older client libraries, so make sure you test in an environment that resembles your deployment one.

Other changes in this release are:

  • Lob.close() now marks LOBs invalid immediately rather than during the asynchronous portion of the close() method, so that all other attempts are no-ops.

  • Incorrect application logic in version 1 that attempted to close a connection while certain LOB, ResultSet or other database operations were still occurring gave an NJS-030, NJS-031 or NJS-032 "connection cannot be released" error. Now in version 2 the connection will be closed but any operation that relied on the connection being open will fail.

  • Some NJS and DPI error messages and numbers have changed. This is particularly true of DPI errors due to the use of ODPI-C.

  • Stated compatibility is now for Node.js 4, 6 and 8.

  • Added support for fetching columns types LONG (as String) and LONG RAW (as Buffer). There is no support for streaming these types, so the value stored in the DB may not be able to be completely fetched if Node.js and V8 memory limits are reached. You should convert applications to use LOBs, which can be streamed.

  • Added support for TIMESTAMP WITH TIME ZONE date type. These are mapped to a Date object in node-oracledb using LOCAL TIME ZONE. The TIME ZONE component is not available in Node.js's Date object.

  • Added support for ROWID without needing an explicit fetchAsString. Data is now fetched as a String by default.

  • Added support for UROWID. Data is fetched as a String.

  • Added query support for NCHAR and NVARCHAR2 columns. Binding for DML may not insert data correctly, depending on the database character set and the database national character set.

  • Added query support for NCLOB columns. NCLOB data can be streamed or fetched as String. Binding for DML may not insert data correctly, depending on the database character set and the database national character set.

  • Removed node-oracledb size restrictions on LOB fetchAsString and fetchAsBuffer queries, and also on LOB binds. Node.js and V8 memory restrictions will still prevent large LOBs being manipulated in single chunks. The v1 limits really only affected users who linked node-oracledb with 11.2 client libraries.

  • Statements that generate errors are now dropped from the statement cache. Applications running while table definitions change will no longer end up with unusable SQL statements due to stale cache entries. Applications may still get one error, but that will trigger the now invalid cache entry to be dropped so subsequent executions will succeed. ODPI-C has some extra smarts in there to make it even better than I describe. I can bore you with them if you ask - or you can check the ODPI-C source code. Note that Oracle best-practice is never to change table definitions while applications are executing. I know some test frameworks do it, but ....

All these improvements are courtesy of ODPI-C's underlying handling. The use of ODPI-C is a great springboard for future features since it already has support for a number of things we can expose to Node.js. The ODPI-C project was an expansion of the previous DPI layer used solely by node-oracledb. Now ODPI-C is in use in Python cx_Oracle 6, and is being used in various other projects. For example Tamás Gulácsi has been working on a Go driver using ODPI-C. (Check out his branch here). Kubo Takehiro started an Oracle Rust driver too - before he decided that he preferred programming languages other than Rust!

Our stated plan for node-oracledb is that maintenance of node-oracledb 1.x will end on 1st April 2018, coinciding with the end-of-life of Node 4, so start testing node-oracledb 2.0.13-Development now.

Python cx_Oracle 6.0 RC 1 is now on PyPI

Sun, 2017-06-18 22:06

Release Candidate 1 of Python cx_Oracle 6.0 in now on PyPI. Test now and report any feedback.

Python cx_Oracle is the Python Instant for Oracle Database. Version 6 is based on the new ODPI-C abstraction layer, which is now also in Release Candidate phase. This layer has allowed cx_Oracle code itself to be greatly simplified.

There are a few small tweaks in cx_Oracle RC1 since the final Beta. Read about them in the Release Notes. A couple of the changes are that the method Cursor.setoutputsize() is now a no-op, since ODPI-C automatically manages buffer sizes of LONG and LONG RAW columns. Also unicode can be used (in addition to string) for creating session pools and for changing passwords in Python 2.7.

The use of ODPI-C has allowed Python Wheels to be created, making installation easier.

Install cx_Oracle 6.0 RC 1 from PyPI with:

python -m pip install cx_Oracle --pre

All you then need are Oracle client 12.2, 12.1 or 11.2 libraries (e.g. the Oracle Instant Client 'Basic' package) in your path at runtime.

cx_Oracle Documentation is here.

Happy Pythoning!

ODPI-C RC1 released on GitHub

Sun, 2017-06-18 19:33

The ODPI-C abstraction layer for Oracle Database applications has just entered Release Candidate phase. You know what this means - it's seriously time you should do some testing and report any issues.

What is it?

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.

Download ODPI-C from here.

Documentation is here.

The release notes are here. Along with some small bug fixes, this release has a memory optimization to reduce memory usage when the client character set is the same as the database character set - thus no unnecessary memory is allocated to cater for what otherwise is the potential expansion when converting between character sets.

In conjunction with this release, the Python cx_Oracle 6 API also went into Release Candidate phase. Later today the first Development release of node-oracledb v2 will be pushed to GitHub. Both these updated APIs use ODPI-C, so give some bigger usage examples you can follow.

Also recently Tamás Gulácsi has been working on a Go driver using ODPI-C. Check out his branch too.

Python cx_Oracle 6.0b2 is available

Wed, 2017-05-24 18:11

Python cx_Oracle 6.0b2 has been released to GitHub and PyPI.

Anthony Tuininga has released Python cx_Oracle 6.0b2 with (surprise, surprise) some improvements and fixes.

You can install it from PyPI by using:

python -m pip install cx_Oracle --pre

Without the "--pre" option you will get the current production version cx_Oracle 5.3 instead.

If you are upgrading a previous cx_Oracle installation use:

python -m pip install cx_Oracle --upgrade --pre

The full Release Notes list all the details in this release. What is not so obvious is that cx_Oracle benefits from improvements to the underlying ODPI-C abstraction layer, for example cx_Oracle 6.0b2 now has caching enabled for temporary LOBs, and now has 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.

Anthony is hoping to head to the Release Candidate phase soon, so please continue to give us feedback.

Don't forget that with cx_Oracle 6, you no longer need an Oracle client during installation - you only need it at runtime.

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

Pages