Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 2 weeks 1 day ago

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.


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:

Installation instructions:


Source Code Repository:

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

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



  • 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
  • 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:



Report issues and discuss:

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 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 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 features include:


    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.




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

    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.


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

    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.


    SQL> set statementcache 20 SQL> select * from emp; SQL> select * from emp;

    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.


    SQL> set lobprefetch 2000 SQL> select * from lob_tab;

    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.


    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



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


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

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

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: