Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 24 min 18 sec ago

Node-oracledb v2 Query Methods and Fetch Tuning

7 hours 40 min ago
Computer screen showing random javascript code (Photo by Markus Spiske on unsplash.com)

 

For our Node.js node-oracledb v2 add-on for Oracle Database we revamped its lower data access layer and made it into a standalone project ODPI-C, which is being reused by several other language APIs. ODPI-C gave us a reason and opportunity to simplify some of internal query handling code in node-oracledb.

To recap, node-oracledb has four ways to execute queries against an Oracle Database. These are the same in version 1 and version 2:

  • Direct Fetches - these are non-ResultSet, non- queryStream() fetches. All rows are returned in one big array, limited to maxRows (v2 allows an unlimited array size).

  • ResultSet getRow() - return one row on each call until all rows are returned.

  • ResultSet getRows(numRows) - return batches of rows in each call until all rows are returned.

  • queryStream() - stream rows until all rows are returned.

The changes in node-oracledb v2 are:

  • Enhanced direct fetches to allow an unlimited number of rows to be fetched, and made this the default. This occurs when maxRows = 0

  • Replaced prefetchRows (previously used for internal fetch buffering and tuning) with a new property fetchArraySize; the default size is 100.

    fetchArraySize affects direct fetches, ResultSet getRow() and queryStream().

  • getRows(numRows,...) internal fetch buffering is now only tuned by the numRows value. Previously prefetchRows could also affect internal buffering.

  • queryStream() now use fetchArraySize for internal buffer sizing.

  • Implemented getRow() in JavaScript for better performance and use fetchArraySize as the way to tune internal buffer sizes.

The change in v2 that I want to discuss is how a 'direct fetch' does its internal buffering.

To make fetching an unlimited number of rows feasible for direct fetches, data is now internally fetched from Oracle Database in batches of size fetchArraySize and then concatenated to form one big array of results that is returned in the callback. This lets you use fetchArraySize to tune fetch performance. In node-oracledb v1, one big array of size maxRows was allocated before the data was fetched from the database. (With node-oracledb v2 you can have the same behavior, if you really want it, by setting fetchArraySize = maxRows, where maxRows > 0).

Let's look at two different scenarios that fetch 50000 rows with fetchArraySize of 100 (the default) and 1000. The code is at the end of the post.

Direct fetch: rows: 50000, batch size: 100, seconds: 4.054 Direct fetch: rows: 50000, batch size: 1000, seconds: 1.643

You can see in this case (with a local database) that increasing fetchArraySize improved performance. There may be various factors for this, but the common one is reduced network cost and reduced load on the database because there were fewer 'round trips' to get batches of records. Each query and environment will be different, and require its own tuning.

The benefits of using fetchArraySize for direct fetches are:

  • Performance of batching and network transfer of data can be tuned.

  • Memory can incrementally grow when the number of query rows is unknown, or varies from execution to execution. A single large chunk of memory (based on maxRows in v1) doesn't need to pre-allocated to handle the 'worst case' of a large number of rows.

There are two drawbacks with direct fetches:

  • One big array of results is needed. This is the same in v1 and v2.

  • The concatenation of batches of records can use more memory than the final array requires, and can cause fragmentation.

Let's look at timings for all query methods. This is one run; there was expected variability each time I ran the scripts. The 'batch size' number is numRows for getRows(numRows) calls, or fetchArraySize for the other fetch methods.

Direct fetch: rows: 50000, batch size: 100, seconds: 4.054 ResultSet getRow(): rows: 50000, batch size: 100, seconds: 1.625 ResultSet getRows(): rows: 50000, batch size: 100, seconds: 1.586 queryStream(): rows: 50000, batch size: 100, seconds: 1.691 Direct fetch: rows: 50000, batch size: 1000, seconds: 1.643 ResultSet getRow(): rows: 50000, batch size: 1000, seconds: 1.471 ResultSet getRows(): rows: 50000, batch size: 1000, seconds: 1.327 queryStream(): rows: 50000, batch size: 1000, seconds: 1.415

The ResultSet and queryStream() methods don't have to store all rows in memory at once so there is less memory management involved. The outlier is obviously the first result: the memory management of concatenating small chunks of memory together is big. We have some ideas on what we can do inside node-oracledb to improve this a bit, but that is a future project to be investigated, and can't solve the first drawback that all rows eventually have to be held in memory at the same time.

The conclusion is to use ResultSets or streaming for large numbers of rows. This is the same recommendation we gave for v1.

For small numbers of rows, the various query methods perform pretty much the same. The timings are so short that you can treat the differences in the one run shown below as noise. Here each query only returned 1 row:

Direct fetch: rows: 1, batch size: 100, seconds: 0.011 ResultSet getRow(): rows: 1, batch size: 100, seconds: 0.012 ResultSet getRows(): rows: 1, batch size: 100, seconds: 0.013 queryStream(): rows: 1, batch size: 100, seconds: 0.013 Direct fetch: rows: 1, batch size: 1, seconds: 0.012 ResultSet getRow(): rows: 1, batch size: 1, seconds: 0.012 ResultSet getRows(): rows: 1, batch size: 1, seconds: 0.013 queryStream(): rows: 1, batch size: 1, seconds: 0.013

Although the timings are small, I suggest using a small fetchArraySize or numRows if you are querying a small handful of rows, particularly if the number of rows is known (such as 1). This reduces the amount of memory that needs to be allocated throughout node-oracledb, the Oracle client libraries, and also in the database.

References

Node.oracledb documentation is here.

If you are currently using node-oracledb v1, you may be interested in the documentation on Migrating from node-oracledb 1.13 to node-oracledb 2.0.

Code

Here are the rudimentary test scripts I used. The ResultSet code originated in the v1 examples from https://jsao.io/2015/07/an-overview-of-result-sets-in-the-nodejs-driver/

The config.js file is at the end. The dbconfig.js file is the same as in the examples.

The timings include statement execution in the DB, though this is not controlled by node-oracledb. With direct fetches there isn't a way in JavaScript to distinguish the query execute cost from the data fetch costs that fetchArraySize and numRows control.

Direct Fetch

// direct fetch var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); connection.execute( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { fetchArraySize: config.batchSize }, function(err, results) { if (err) throw err; rowsProcessed = results.rows.length; // do work on the rows here var t = ((Date.now() - startTime)/1000); console.log('Direct fetch: rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); connection.release(function(err) { if (err) console.error(err.message); }); }); });

ResultSet getRow()

// ResultSet getRow() var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); connection.execute( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { resultSet: true, fetchArraySize: config.batchSize }, function(err, results) { if (err) throw err; function processResultSet() { results.resultSet.getRow(function(err, row) { if (err) throw err; if (row) { rowsProcessed++; // do work on the row here processResultSet(); // try to get another row from the result set return; // exit recursive function prior to closing result set } var t = ((Date.now() - startTime)/1000); console.log('ResultSet getRow(): rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); results.resultSet.close(function(err) { if (err) console.error(err.message); connection.release(function(err) { if (err) console.error(err.message); }); }); }); } processResultSet(); } ); } );

ResultSet getRows()

// ResultSet getRows() var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); oracledb.fetchArraySize = 1; connection.execute( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { resultSet: true }, function(err, results) { var rowsProcessed = 0; if (err) throw err; function processResultSet() { results.resultSet.getRows(config.batchSize, function(err, rows) { if (err) throw err; if (rows.length) { rows.forEach(function(row) { rowsProcessed++; // do work on the row here }); processResultSet(); // try to get more rows from the result set return; // exit recursive function prior to closing result set } var t = ((Date.now() - startTime)/1000); console.log('ResultSet getRows(): rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); results.resultSet.close(function(err) { if (err) console.error(err.message); connection.release(function(err) { if (err) console.error(err.message); }); }); }); } processResultSet(); }); });

queryStream()

// queryStream() var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); var stream = connection.queryStream( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { fetchArraySize: config.batchSize } ); stream.on('data', function (data) { // do work on the row here rowsProcessed++; }); stream.on('end', function () { var t = ((Date.now() - startTime)/1000); console.log('queryStream(): rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); connection.close( function(err) { if (err) { console.error(err.message); } }); }); });

The Configuration File

// config.js var maxRows; // number of rows to query var batchSize; // batch size for fetching rows maxRows = 50000; batchSize = 1000 exports.maxRows = maxRows; exports.batchSize = batchSize;

node-oracledb 2.0 with pre-built binaries is on npm

7 hours 56 min ago

Release announcement: Node-oracledb 2.0.15, the Node.js add-on for Oracle Database, is now on npm for general use.

Top features: Pre-built binaries, Query fetch improvements

It's time to shake things up. Node-oracledb version 1 has been stable for a while. Our tight, hard working team is now proud to deliver node-oracledb 2 to you. With improvements throughout the code and documentation, this release is looking great. There are now over 3000 functional tests, as well as solid stress tests we run in various environments under Oracle's internal testing infrastructure.

Review the CHANGELOG for all changes. For information on migrating see Migrating from node-oracledb 1.13 to node-oracledb 2.0.

node-oracledb v2 highlights
  • node-oracledb 2.0 is the first release to have pre-built binaries. These are provided for convenience and will make life a lot easier, particularly for Windows users.

    Binaries for Node 4, 6, 8 and 9 are available for Windows 64-bit, macOS 64-bit, and Linux 64-bit (built on Oracle Linux 6).

    Simply add oracledb to your package.json dependencies or manually install with:

    npm install oracledb

    (Yes, Oracle Client libraries are still required separately - these do all the heavy lifting.)

    We've got great error messages when a binary isn't available, and improved the messages when require('oracledb') fails, however Linux users with older glibc libraries may get Linux runtime errors - you should continue building node-oracledb from source in this case, see below.

    There is support for hosting binary packages on your own internal web server, which will be great for users with a large number of deployments. See package/README.

    This is the first release to use the ODPI-C abstraction layer which is also used by Python's cx_Oracle 6.x API, as well as 3rd party APIs for other languages. Using ODPI is the the main change that allowed node-oracledb 2.0 binaries to be distributed. As another consequence of ODPI-C, any node-oracledb 2 binary will run with any of the Oracle client 11.2, 12.1 or 12.2 libraries without needing recompilation. This improves portability when node-oracledb builds are copied between machines. Since the available Oracle functionality will vary with different Oracle Client (and Oracle Database!) versions, it's important to test your applications using your intended deployment environment.

  • The driver can still be built from source code if you need, or want, to do so. Compiling is now simpler than in version 1, since you no longer need Oracle header files, and no longer need OCI_*_DIR environment variables.

    To build from source you need to pull the code from a GitHub branch or tag - generally the most recent release tag is what you want. Make sure you have Python 2.7, the 'git' utility, and a compiler, and add oracle/node-oracledb.git#v2.0.15 to your package.json dependencies. Or manually run the install:

    npm install oracle/node-oracledb.git#v2.0.15

    Users without 'git', or with older versions of Node that won't pull the ODPI submodule code, can use the source package:

    npm install https://github.com/oracle/node-oracledb/releases/download/v2.0.15/oracledb-src-2.0.15.tgz

    I've noticed GitHub can be a little slow to download the source before compilation can begin, so bear with it.

  • Improved query handling:

    • Enhanced direct fetches to allow an unlimited number of rows to be fetched and changed the default number of rows fetched by this default method to be unlimited. The already existing ResultSet and Streaming methods are still recommended for large numbers of rows.

    • Since ODPI-C internally uses 'array fetches' instead of 'prefetching' (both are underlying methods for buffering/tuning differing only in where the buffering takes place - both are transparent to applications), we've replaced prefetchRows with a new, almost equivalent property fetchArraySize..

    • We've moved the buffering or rows for getRow() into JavaScript for better performance. It no longer needs to call down into lower layers as often.

  • We tightened up some resource handling to make sure applications don't leak resources. If you inadvertently try to close a connection when a LOB or ResultSet is open, you will see a new error DPI-1054.

  • The node-oracledb size restrictions on LOB fetchAsString and fetchAsBuffer queries, and also on LOB binds. In node-oracledb 1 these were particularly low when Oracle 11gR2 client libraries were used, so this should be welcome for people who have not updated the Oracle client. Node.js and V8 will still limit sizes, so continue to use the Stream interface for large LOBs.

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

  • Added support for fetching columns types LONG (as String) and LONG RAW (as Buffer).

  • 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 the Date object.

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

Plans for Version 1

Our stated plan was to cease formal support for version 1 when Node 4 LTS maintenance ended in April 2018. We're pleased 1.13.1 has been stable for some time, and we are not anticipating needing any further node-oracledb 1 releases, unless exceptional circumstances occur.

Plans for Version 2

We are now testing with Node 4, 6, 8 and 9. This list will, of course, change as new Node versions are released. The pre-built binaries will also change and availability is not guaranteed.

ODPI-C forms a solid base to extend node-oracledb. Users of Python cx_Oracle 6, which is also based on ODPI-C, are appreciating all the advanced Oracle features that are available. Many of these features have also been requested by node-oracledb users. As with any open source project, there are no hard and fast guarantees for node-oracledb, but you can see the general direction we'd like to head in. Pull Requests are welcome.

One little thing you might be unaware of is that along the way we have been testing (and helping create) the next major release of Oracle Database, so sometimes we've had to take detours from direct node-oracledb work order to move the whole of Oracle along. Whether we work on the "client" or the "server", we look forward to bringing you great things in future releases.

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

For information on migrating see Migrating from node-oracledb 1.13 to node-oracledb 2.0.

Issues and questions about node-oracledb can be posted on GitHub.

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

cx_Oracle 6.1 with Oracle Database Sharding Support is now Production on PyPI

Tue, 2017-12-12 14:45

cx_Oracle 6.1, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle logo

cx_Oracle is an open source package that covers the Python Database API specification with many additions to support Oracle advanced features.

In the words of the creator and maintainer, Anthony Tuininga: The cx_Oracle 6.1 release has a number of enhancements building upon the release of 6.0 made a few months ago. Topping the list is support for accessing sharded databases via new shardingkey and supershardingkey parameters for connections and session pools. Support for creating connections using the SYSBACKUP, SYSDG, SYDKM and SYSRAC roles was also added, as was support for identifying the id of the transaction which spawned a subscription message. For those on Windows, improved error messages were created for when the wrong architecture Oracle Client is in the PATH environment variable. Improvements were also made to the debugging infrastructure and a number of bugs were squashed. The test suite has also been expanded. See the full release notes for more information.

cx_Oracle References

Home page: https://oracle.github.io/python-cx_Oracle/index.html

Installation instructions: http://cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: http://cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: http://cx-oracle.readthedocs.io/en/latest/releasenotes.html

Source Code Repository: https://github.com/oracle/python-cx_Oracle

ODPI-C 2.1 is now available for all your Oracle Database Access Needs

Tue, 2017-12-12 13:56
ODPI-C logo ODPI-C

Anthony Tuininga has just released version 2.1 of the Oracle Database Programming Interface for C (ODPI-C) on GitHub.

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.

Key new feature: support for accessing sharded databases.

 

In the words of Anthony: This release has a number of small enhancements intended to build upon the release of 2.0 made a few months ago. Topping the list is support for accessing sharded databases, a new feature in Oracle Database 12.2. Support for creating connections using the SYSBACKUP, SYSDG, SYDKM and SYSRAC roles was also added, as was support for identifying the id of the transaction which spawned a subscription message. For those on Windows, improved error messages were created for when the wrong architecture Oracle Client is in the PATH environment variable. Improvements were also made to the debugging infrastructure. The test suite has also been expanded considerably. A number of bugs were squashed to improve ODPI-C and in preparation for the upcoming releases of cx_Oracle 6.1 and node-oracledb 2.0, both of which use ODPI-C. See the full release notes for more information.

ODPI-C References

Home page: https://oracle.github.io/odpi/

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

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

Release Notes: https://oracle.github.io/odpi/doc/releasenotes.html

Report issues and discuss: https://github.com/oracle/odpi/issues

Oracle Instant Client 12.2 for macOS is Available

Thu, 2017-10-26 01:12

Oracle Instant Client 12.2 for macOS can now be downloaded for free from OTN.

This release is for 64 bit only. It supports:

  • MAC OS X 10.13, High Sierra
  • MAC OS X 10.12, Sierra
  • MAC OS X 10.11, El-Capitan

Install instructions are at the end of the download page.

Instant Client contains libraries and tools allowing applications to connect to a local or remote Oracle Database for development and production deployment.

And if you haven't caught up with the news, Instant Client 12.2 now includes tools like SQL*Loader and Data Pump.

Python cx_Oracle RPMs for Oracle Linux 6 and Oracle Linux 7

Fri, 2017-09-29 17:35

The Oracle Linux group is starting to roll out RPMs for language interfaces like Python cx_Oracle.  Check out their blog posts on the available cx_Oracle RPMs: 

cx_Oracle RPMs have landed on Oracle Linux Yum Server

Using cx_Oracle With Software Collection Library and What Those Different Versions Are For

Ruby-oci8 is also available for OL6 and OL7; more on that later.

 

What's on at Oracle OpenWorld for Developers using Python, Node.js, PHP, R, C and C++

Tue, 2017-09-26 18:08

Oracle Open World Conference Logo

The Oracle OpenWorld in San Francisco is approaching and it's time to plan your schedule.  The sessions that I'm tracking are all listed on the official schedule page Application Development with Node.js, Python, PHP, R, C and C++. Bookmark that link!

  Conference Sessions Hands-on Lab Session DevLive Interviews

Come and hear these informal chats at the DevLive Stage, Moscone West Level 1. The discussion is bound to be broad reaching and lively.

  • Node.js: JavaScript Application Development for Oracle Database
    Monday, Oct 02, 3:00 p.m. - 3:30 p.m. | Developer Lounge, Moscone West

  • Building Python Messaging Apps with Oracle DB12c
    Tuesday, Oct 03, 3:00 p.m. - 3:30 p.m. | Developer Lounge, Moscone West

Ruby and Ruby on Rails Recognized by Oracle Technology Network

Wed, 2017-08-30 01:28

I'm really pleased that two key contributors to the Ruby and Ruby on Rails communities for Oracle Database have been honored.

Takehiro Kubo and Yasuo Honda have both become Oracle ACEs. The Oracle Technology Network's Oracle ACE program describes itself as recognizing Oracle evangelists and enthusiasts. Both Takehiro and Yasuo fit this description exactly.

To me, Takehiro Kubo is most visible in his work on the ruby-oci8 gem. Ruby-oci8 is a Ruby interface for Oracle Database. Takehiro created ruby-oci8 and is actively maintaining it and helping users. He also willingly contributes his knowledge to Oracle Database interfaces for other programming languages, helping both maintainers and users. An eager adopter of new technology, he is currently developing ruby-odpi, a rewrite of the interface that is based on the strengths of Oracle's new ODPI-C wrapper.

Most Oracle Ruby and JRuby developers use the Ruby on Rails web application framework. Here Yasuo Honda is the key person. He has been the lead maintainer of the Ruby on Rails Oracle ActiveRecord Adapter for some years now. He has nurtured an active community of users and contributors, keeping up with both framework and library improvements. He has contributed enormously to its status as a very popular development environment. He freely contributes his knowledge and expertise.

Both Takehiro and Yasuo personify the ideal open source maintainers. They are able to create useful, beautiful software components that other people want to use. They take their roles seriously, and have shown long term commitment to their projects' successes.

Congratulations!

Oracle Instant Client 12.2 now has SQL*Loader and Data Pump

Mon, 2017-08-21 19:17

Oracle Database 12c iconThis is a guest post by Jim Stenoish, Senior Director of Software Development, whose products include all tools used for the high speed movement, transformation, and loading of data and metadata for Oracle Database.

Oracle Instant Client 12.2 now includes SQL*Loader as well as the Data Pump command line utilities expdp and impdp, and the traditional Export/Import utilities exp and imp. Previously, these tools were only available with a 'full' client installation, or in a database installation. Being in Instant Client allows users to access these tools with a smaller footprint on disk and with a much quicker install than for a full client. The OTN license is also more convenient for many users.

Now these tools are part of Instant Client, it's easy to load data from a file on a system the doesn't already have Oracle software installed.

Installing the Tools

You can install the tools by unzipping the Oracle Instant Client 'Basic' (or 'Basic Light') package and the 'Tools' package to the same location. See the Instant Client home page for links to download for each platform.

Follow the platform-specific instructions given on the download page. For example, on Linux set LD_LIBRARY_PATH and PATH to the Instant Client directory. On Windows set PATH to the Instant Client directory and also install the Microsoft Visual Studio 2013 Redistributable.

Using the Tools

SQL*Loader allows you to load data from files, such as comma-separated value files (CSV), into the tables.

The expdp and impdp utility allows you to move metadata and data between Oracle databases. The expdp utility unload metadata and data from the database into Data Pump files on the database server. The impdp utility recreates metadata defined in a Data Pump file and loads table data stored in the files on the database server. It also provides a way to extract metadata and data over a DB Link (no files involved) from one database to another database.

Documentation and examples for the tools are in the Database Utilities manual.

If you have questions about SQL*Load or Data Pump you can ask them on OTN.

PHP OCI8 2.1.7 for Oracle Database is now on PECL

Mon, 2017-08-14 20:51

PHP 7 compatible OCI8 2.1.7 for Oracle Database is now on PECL.

This version of OCI8 will also be bundled in PHP 7.0.23 and PHP 7.1.9 (and is already in PHP 7.2.0beta2). Older PHP 7 installations can be brought up to date using the PECL bundle.

The highlight of this release is Transparent Application Failover (TAF) callback support:

In a configured Oracle Database system, TAF occurs when the PHP application detects that the database instance is down or unreachable. It establishes a connection to another node in an Oracle RAC configuration, a hot standby database, or the same database instance itself.

The new OCI8 TAF callback feature allows a PHP function to be invoked during failover, allowing applications to decide how to handle the change. For example the callback function can fixup any application session state and continue processing so users are unaware of the database change.

The TAF callback feature originated from a Pull Request by KoenigsKind.

This release also fixes a potential integer overflow in oci_bind_array_by_name(), with a patch by Ingmar Runge.

Our grateful thanks for both these submissions to OCI8.

Python cx_Oracle 6 Production Release: Hitting A Six

Mon, 2017-08-14 17:27

cx_Oracle 6.0, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle logo

cx_Oracle is an open source package that covers the Python Database API specification with many additions to support Oracle advanced features. It was begun by Anthony Tuininga in 1998 in the era of Oracle Database 8i and Python 1.5. Since then there have been over 30 releases covering up to Oracle Database 12c and Python 3.

Anthony joined Oracle relatively recently. In his time with us, he has been hard at work in the language API space, leading cx_Oracle changes as well as creating the ODPI-C interface, and also making contributions to node-oracledb.

cx_Oracle 6.0 is the latest and greatest release from our group. It can connect to both on-premise databases and to Oracle Database Cloud Services. It can also be run in the cloud itself.

The use cases for cx_Oracle are endless. Both small and big projects make use of cx_Oracle for all kinds of data manipulation and application development projects.

cx_Oracle has extensive API documentation, solid samples, and a clean test suite. (We also run stress tests for cx_Oracle in the Oracle Database development environment). To complete the link dump, the home page is here and the source code is hosted on GitHub.

cx_Oracle 6 Features

cx_Oracle 6's support for Oracle Database is excellent.

  • Easily installed from PyPI with Wheels.

  • Support for Python 2 and 3.

  • Support for Oracle Client 11.2, 12.1 and 12.2. Oracle's standard cross-version interoperability allows easy upgrades and connectivity to different Oracle Database versions.

  • Connect to Oracle Database 9.2, 10, 11 or 12 (depending on the Oracle Client version used).

  • SQL and PL/SQL Execution. The underlying Oracle Client libraries have significant optimizations including compressed fetch, pre-fetching, client and server result set caching, and statement caching with auto-tuning.

  • Full use of Oracle Network Service infrastructure, including encrypted network traffic, authentication, and security features.

  • Extensive Oracle data type support, including large object support (CLOB and BLOB).

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

  • Array operations for efficient INSERT and UPDATEs.

  • Array row counts and batch error handling for array operations.

  • Fetching of large result sets.

  • REF CURSOR support.

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

  • Fetch PL/SQL Implicit Results. Easily return query results from PL/SQL.

  • Row Prefetching. Efficient use of the network.

  • Client Result Caching. Improve performance of frequently executed look-up statements.

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

  • Continuous Query Notification. Get notified when data changes.

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

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

  • End-to-end monitoring and tracing.

  • Transaction Management.

  • Session Pooling.

  • Database Resident Connection Pooling (DRCP).

  • Privileged Connections.

  • External Authentication.

  • Database startup and shutdown.

  • Oracle Database High Availability Features, such as FAN notifications and Transaction Guard support.

Changes since cx_Oracle 5.3

The main changes in cx_Oracle 6 are:

  • Re-implemented to use our new ODPI-C abstraction layer for Oracle Database. The cx_Oracle API is unchanged. The cx_Oracle design, build and linking process has improved because of ODPI-C.

  • Python Wheels are now available for install. This is made possible by the ODPI-C architecture.

  • Less code in Python's Global Interpreter Lock, giving better scalability.

  • Support for DML RETURNING of multiple rows.

  • Support for Universal ROWIDs.

  • LOB locators are now associated to LOB objects so they are not overwritten on database round trips.

As you can see, the use of ODPI-C was a significant change, leading to code refactoring and simplification. It gives us a springboard to make future improvements to cx_Oracle using a code base that has multiple consumers (and hence testers) including node-oracledb 2.

With the release of cx_Oracle 6, no new features are planned for cx_Oracle 5.

Installing cx_Oracle

With the new cx_Oracle 6 Wheels, installing is straightforward as:

python -m pip install cx_Oracle --upgrade

If a binary is not available, the cx_Oracle source will be compiled.

Oracle Client 11.2, 12.1 or 12.2 libraries are needed in your system library path (such as PATH on Windows, or LD_LIBRARY_PATH on Linux). These libraries provide a lot of features such as connection management, query and statement cache management, as well as high availability features. Libraries can be easily installed from the free Oracle Instant Client, an Oracle Database installation, or a full Oracle Client installation. Oracle's standard client-server interoperability applies, e.g. Oracle Client 12.2 can connect to Oracle Database 11.2 onward.

Thanks to ODPI-C, you do not need to update cx_Oracle if you decide to upgrade your Oracle Client from 11.2 or 12.1 to a newer version. You can simply change your library loading path, e.g. PATH or LD_LIBRARY_PATH, to the new version of the Oracle Client libraries.

Once you have cx_Oracle installed, you can connect to your database. For example:

from __future__ import print_function import cx_Oracle connection = cx_Oracle.connect("hr", "welcome", "localhost/orclpdb") cursor = connection.cursor() cursor.execute(""" SELECT first_name, last_name FROM employees WHERE department_id = :did AND employee_id > :eid""", did = 50, eid = 190) for fname, lname in cursor: print("Values:", fname, lname) cx_Oracle References

Home page: https://oracle.github.io/python-cx_Oracle/index.html

Installation instructions: http://cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: http://cx-oracle.readthedocs.io/en/latest/index.html

Source Code Repository: https://github.com/oracle/python-cx_Oracle

Last Words

The cx_Oracle interface is the most popular scripting language for Oracle Database. It has a long history, is widely used, and exposes features for building all kinds of database applications. The release of cx_Oracle 6 brings a refactored implementation that is more scalable and makes it easier to bring you great, future Oracle functionality.

Thank you to all our users and testers who are part of a very big & happy cx_Oracle community.

Keep in touch!

Open Source Library Drives Powerful Oracle Database Applications

Mon, 2017-08-14 17:14
ODPI-C logo ODPI-C

The production release of Oracle Database Programming Interface for C (ODPI-C) is now available on GitHub

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.

 

ODPI-C:

  • exposes a C API in a readily consumable way to C and C++ developers writing applications for Oracle Database.
  • is aimed at language interfaces and applications where speed of development is important, and where special-case Oracle features are not needed.
  • is already being used for Python, Node.js, Go and Rust interfaces, as well as for custom applications.
  • allows faster application implementation with less code.
  • makes memory and resource management simpler, particularly for 'binding' and 'defining' data. A reference counting mechanism adds resiliency by stopping applications destroying in-use resources.
  • is available as source code on GitHub under the Apache 2.0 and/or the Oracle UPL licenses, for direct inclusion into your own code bases.

ODPI-C is a wrapper over Oracle Call Interface. OCI is Oracle Database's main C API and is widely used by our products and user applications so it is stable, fast, scalable and supported. OCI's API is extremely flexible and gives fine-grained control to the developer for a very wide range of use cases.

ODPI-C is also flexible but is aimed primarily at language interface 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 language interface usage. Since it provides a simple programming experience, if ODPI-C has the functionality you need for accessing Oracle Database, then you can add it to your own projects.

ODPI-C is developed by the Oracle Database Data Access team, who also look after OCI and other language APIs for Oracle Database. Anthony Tuininga has been leading the ODPI-C effort. He has made full use of his extensive knowledge as creator and maintainer of the extremely popular, and full featured, Python cx_Oracle interface.

A big thank you to all the users who have given feedback on ODPI-C pre-releases.

ODPI-C Features

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. ODPI-C does extra validation and has a reference counting mechanism for resiliency to help stop applications destroying in-use OCI resources. Writing threaded mode applications is easier.

The Oracle feature list in this initial release, in no particular order, is:

  • 11.2, 12.1 and 12.2 Oracle Client support
  • 9.2 and higher Oracle Database support (depending on Oracle Client version)
  • SQL and PL/SQL execution
  • Character data types (CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, LONG)
  • Numeric data types (NUMBER, BINARY_FLOAT, BINARY_DOUBLE)
  • Dates, Timestamps, Intervals
  • Binary types (BLOB, BFILE, RAW, LONG RAW)
  • PL/SQL data types (PLS_INTEGER, BOOLEAN, Collections, Records)
  • JSON
  • User Defined Types
  • REF CURSOR, Nested cursors, Implicit Result Sets
  • Array fetch
  • Array bind/execute
  • Session pools, with connection validation
  • Standalone connections
  • Database Resident Connection Pooling (DRCP)
  • External authentication
  • Statement caching
  • End-to-end tracing, mid-tier authentication and auditing
  • Edition Based Redefinition
  • Scrollable cursors
  • DML RETURNING
  • Privileged connection support (SYSDBA, SYSOPER, SYSASM, PRELIM_AUTH)
  • Database Startup/Shutdown
  • Session Tagging
  • Proxy authentication
  • Batch Errors
  • Array DML Row Counts
  • Query Result Caching
  • Application Continuity
  • Query Metadata
  • Password Change
  • Two Phase Commit
  • Continuous Query Notification
  • Advanced Queuing

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

ODPI-C applications can take 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.

Since communication to the database is handled by Oracle's Network Services, features such as encrypted communication and LDAP can easily be configured.

Using ODPI-C

See ODPI-C Installation for detailed instructions.

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, use the provided example Makefile. 

To try out ODPI-C, build it as a shared library and then compile the sample programs. These show a number of ODPI-C features. You can also view the test suite.

ODPI-C makes it easy to build and distribute application code or binaries because it is open source, and OCI headers and libraries are not needed at compilation. Applications need standard Oracle client libraries only at run time.

Oracle client libraries must be installed separately. Version 11.2, 12.1 or 12.2 are required. These allow applications to connect to Oracle Database 9.2 or later (depending on the client version).

ODPI-C uses the shared library loading mechanism available on each supported platform to load the Oracle Client library at run time. This allows code using ODPI-C to be built only once, and then run using available Oracle Client libraries.

Oracle client libraries are available in the free, easily installed Oracle Instant Client "Basic" and "Basic Light" packages. Client libraries are also available in any Oracle Database installation, or in the full Oracle Client installation.

ODPI-C has been tested on Windows, macOS and Linux. Compilation on other platforms such as AIX and Solaris x64 is possible, but these have not been fully tested.

Wrap Up

ODPI-C provides an API to Oracle Database that enables functional, flexible application and scripting language API development.

ODPI-C is being used by us for the Python cx_Oracle 6 interface, and in node-oracledb 2 for Node.js. Third party developers have also started Go and Rust interfaces too. These code bases are handy, bigger examples of how to use ODPI-C.

It's also pleasing to have gotten positive feedback from internal Oracle projects that have adopted ODPI-C for a variety of other application development purposes.

ODPI-C will continue to be enhanced, bringing great Oracle Database technologies to developers. Scripting language drivers will similarly see enhancements and get benefits of the common ODPI-C code base.

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

ODPI-C References

Home page: https://oracle.github.io/odpi/

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

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

Report issues and discuss: https://github.com/oracle/odpi/issues

Footnote Trivia

Why is this first production release called version 2?  Because ODPI-C is a significantly updated, refactored, and standalone release of code which had origins in a node-oracledb 1.x. abstraction layer.

Python cx_Oracle 6.0 RC 2 is on PyPI

Mon, 2017-07-24 18:16

Python cx_Oracle is the Python interface for Oracle Database

Anthony Tuininga has released the second (and probably final) Release Candidate of Python cx_Oracle 6.0 on PyPI. It's now full steam ahead towards the production release, so keep on testing. Issues can be reported on GitHub or the mailing list.

To take look, use the '--pre' option to pip to install this pre-release:

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

You will also need Oracle client libraries, such as from the free Oracle Instant Client.

I want to highlight a few of changes, focusing, as readers of my blog will know I favor, on usability.

  • This release picks up the latest ODPI-C Oracle DB abstraction layer, which provides some nice fixes. In particular one fix resolved a confusing Windows system message about a 'UnicodeDecodeError' displaying when cx_Oracle was imported. Now the actual Windows error message is displayed, allowing you to see what root problem is.

  • The installation notes have been tidied up and made into a new Installation Chapter of the documentation, complete with troubleshooting tips.

  • Some more introductory samples have been added, and the sample and test schema creation scripts improved. The scripts now reference a common file to set credentials, making it easier to play with them without needing to edit every single one.

The full cx_Oracle release notes are here. Let us know what you find.

ODPI-C 2.0.0 RC2 is released on GitHub

Thu, 2017-07-20 17:12

ODPI-C 2.0.0 Release Candidate 2 is now available on GitHub.

What is ODPI-C? It 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 2.0.0 RC2 release notes are here. Along with bug fixes, you can see some defensive coding improvements, and some error message tweaks to help improve the user experience. These may look small but I know that by adding resilience, and by giving more information to users when things go wrong, the nicer user experience will help users solve problems faster.

I want to give a special shout out to all our testers. Testing is a never ending task, of course! Along with new ODPI-C functional tests in RC2, our Node.js node-oracledb and Python cx_Oracle testers have been busy creating and running functional and stress tests. (The latest version of node-oracledb & cx_Oracle use ODPI-C!) It's all looking good.

ODPI-C is heading fast towards a first 'Production' release, so follow our lead and test, test, test your own applications!

ODPI-C issues can be reported here.

Ever Evolving SQL*Plus 12.2.0.1 Adds New Performance Features

Wed, 2017-07-12 03:40

This is a guest post by Luan Nim, Senior Development Manager at Oracle.

SQL*Plus 12.2.0.1 has introduced a number of features to improve the performance and ease of use in general. These features can be enabled with SET commands, or via the command line.

New Oracle SQL*Plus 12.2.0.1 features include:

  • SET MARKUP CSV

    This option lets you generate output in CSV format. It also lets you choose the delimiter character to use and enable quotes ON or OFF around data. The benefit of using CSV format is that it is fast. This option improves the performance for querying large amount of data where formatted output is not needed.

    Syntax:

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

    Example:

    SQL> set markup csv on SQL> select * from emp; "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" 7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20 7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30 7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30 7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20 7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30 7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30 7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10 7788,"SCOTT","ANALYST",7566,"19-APR-87",3000,,20 7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10 7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,0,30 7876,"ADAMS","CLERK",7788,"23-MAY-87",1100,,20 7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30 7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20 7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10 14 rows selected.

    This option is also available from command line with the "-m csv" argument.

    $ sqlplus –m “csv on” scott/tiger @emp.sql SQL*Plus: Release 12.2.0.2.0 Development on Wed Jul 5 23:12:14 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Wed Jul 05 2017 23:11:46 -07:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 - 64bit Development "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" 7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20 7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30 7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30 7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20 7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30 7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30 7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10 7788,"SCOTT","ANALYST",7566,"19-APR-87",3000,,20 7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10 7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,0,30 7876,"ADAMS","CLERK",7788,"23-MAY-87",1100,,20 7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30 7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20 7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10 14 rows selected.
  • SET FEEDBACK ONLY

    The new ONLY option to SET FEEDBACK is to display the number of rows selected without displaying data. This is useful for users who are interested in measuring the time taken to fetch data from the database, without actually displaying that data.

    Example:

    SQL> set feedback only SQL> select * from emp; 14 rows selected.
  • SET STATEMENTCACHE

    This option is to cache executed statements in the current session. The benefit of this setting is that it reduces unnecessary parsing time for the same query. Therefore it improves performance when repeatedly executing a query in a session.

    Example:

    SQL> set statementcache 20 SQL> select * from emp; SQL> select * from emp;
  • SET LOBPREFETCH

    This option is to improve access of smaller LOBs where LOB data is prefetched and cached. The benefit of this setting is to reduce the number of network round trips to the server, allowing LOB data to be fetched in one round trip when LOB data is within the LOBPREFETCH size defined.

    Example:

    SQL> set lobprefetch 2000 SQL> select * from lob_tab;
  • SET ROWPREFETCH

    This option is to minimize server round trips in a query. The data is prefetched in a result set rows when executing a query. The number of rows to prefetch can be set using this SET ROWPREFETCH

    This option can reduce round trips by allowing Oracle to transfer query results on return from its internal OCI execute call, removing the need for the subsequent internal OCI fetch call to make another round trip to the DB.

    Example:

    SQL> set rowprefetch 20 SQL> Select * from emp;

    If, for example, you expect only a single row returned, set ROWPREFETCH to 2, which allows Oracle to get the row efficiently and to confirm no other rows need fetching.

  • Command line –FAST option.

    This command line option improves performance in general. When this option is used, it changes the following SET options to new values:

    • ARRAYSZE 100

    • LOBPREFETCH 16384

    • PAGESIZE 50000

    • ROWPREFETCH 2

    • STATEMENTCACHE 20

    Once logged in, these setting can also be changed manually.

    Syntax:

    $ sqlplus –f @emp.sql

I hope the new features described above are helpful to you. For more information, please refer to the SQL*Plus Users Guide and Reference.

If you have questions about SQL, PL/SQL or SQL*Plus, post them in the appropriate OTN space.

Updated PHP 7.2 PDO_OCI install 'configure' syntax

Sun, 2017-07-02 21:10

Frank Yang at Oracle has updated the PHP 7.2 'configure' option for PDO_OCI and added some of the heuristics previously only in the PHP OCI8 extension configure option. This has allowed the two extension option syntaxes to be aligned.

PDO_OCI is PHP's PDO driver for Oracle Database. PHP 7.2 is in Alpha status. In common with most other database PDO drivers, the PDO_OCI driver is part of the PHP bundle and is not on PECL.

The new PHP 7.2 PDO_OCI 'configure' syntax is like:

--with-pdo-oci[=DIR] PDO: Oracle OCI support. DIR defaults to $ORACLE_HOME. Use --with-pdo-oci=instantclient,/path/to/instant/client/lib for an Oracle Instant Client installation.

So now, for example, you could use:

./configure --with-pdo-oci=instantclient,/usr/lib/oracle/12.2/client64/lib \ --with-oci8=instantclient,/usr/lib/oracle/12.2/client64/lib

Prior to PHP 7.2, configuring PDO_OCI with the Oracle Instant Client libraries required something like:

./configure --with-pdo-oci=instantclient,/usr,12.1

The version number on the end of the option was only partially validated. In many cases it was possible to use an 'incorrect' number and still get the desired libraries to be used. (Also each new Oracle Database version required PDO_OCI code changes to update a white list).

When building with PHP 7.1 (and earlier) and using Instant Client, this old syntax is still needed.

If you know PHP OCI8 'configure' options well, you may wonder why the install simplification heuristic on Linux to automatically use the highest installed version of Instant Client RPMs wasn't ported to PDO_OCI's 'configure'. Well, I still dream that bigger improvements to PDO_OCI will occur. The current incarnation of this dream is that PDO_OCI could one day be rewritten to use ODPI-C, like cx_Oracle 6 and node-oracledb 2. If this dream ever became reality, the configure option would become simply '--with-pdo-oci' since ODPI-C doesn't need Oracle headers or libraries at build time. ODPI-C only needs Oracle client libraries in LD_LIBRARY_PATH (or PATH on Windows) at run time. But I've had the dream of various PDO_OCI improvements for at least 10 years....

For the moment Oracle still recommends using PHP OCI8 in preference to PDO_OCI because OCI8 has superior features and can also take advantage of various Oracle client library connection and caching functionality.

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.

Pages