Sunday May 29, 2016

Using Oracle on OS X? Instant Client 12.1 is here

Oracle Instant Client 12.1 for OS X was just released and is now available for free download from OTN for 32-bit and 64-bit applications.

Instant Client provides libraries and tools for connecting to Oracle Database. Among other uses, languages such as C, Python, PHP, Ruby, Perl and Node.js can use Instant Client for database connectivity.

In addition to having Oracle 12.1 client features like auto-tuning, new in this release is an ODBC driver.

The install instructions have been updated to reflect the resolution of the linking issues caused by the OS X El Capitan changes with SIP to ignore DYLD_LIBRARY_PATH in sub processes. The ~/lib location required for Instant Client 11.2 on El Capitan is no longer needed with Instant Client 12.1. Note if you are creating your own apps, you should link with -rpath.

This release of Instant Client supports Mavericks, Yosemite, and El Capitan. Applications can connect to Oracle Database 10.2 or more recent. You should continue using the older 11.2 client if you need to connect to Oracle Database 9.2.

If you are interested in running Oracle Database itself on OS X, see my earlier post The Easiest Way to Install Oracle Database on Mac OS X.

Wednesday Mar 02, 2016

Using SQL*Plus Instant Client 11.2 on OS X El Capitan

The installation steps for using SQL*Plus 11.2 (Instant Client) and other OCI-based applications have necessarily changed since OS X El Capitan broke DYLD_LIBRARY_PATH. The updated instructions are given on the Instant Client Downloads for Mac OS X (Intel x86) page.

Update: Instant Client 12.1 is out and installation is easier. Check out the above link for instructions

Here are the 11.2 instructions repeated. The steps also work on earlier versions of OS X:

  1. Download the desired Instant Client ZIP files from OTN. All installations require the Basic or Basic Lite package.

  2. Open a terminal and unzip the packages into a single directory such as "~/instantclient_11_2". For example, to use SQL*Plus:

    cd ~
  3. Create the appropriate libclntsh.dylib link for the version of Instant Client. For example:

    cd ~/instantclient_11_2
    ln -s libclntsh.dylib.11.1 libclntsh.dylib

    Note: OCCI programs will additionally need:

    ln -s libocci.dylib.11.1 libocci.dylib
  4. Add links to "~/lib" for required Basic package libraries. For example, to use OCI programs (including SQL*Plus, Python's cx_Oracle, PHP's OCI8, Node.js's node-oracledb, and Ruby's ruby-oci8 driver):

    mkdir ~/lib
    ln -s ~/instantclient_11_2/libclntsh.dylib.11.1 ~/lib/
    ln -s ~/instantclient_11_2/{libnnz11.dylib,libociei.dylib} ~/lib/
  5. To run SQL*Plus, add its libraries to "~/lib", and update PATH. For example:

    ln -s ~/instantclient_11_2/{libsqlplus.dylib,libsqlplusic.dylib} ~/lib/
    export PATH=~/instantclient_11_2:$PATH

Thursday Jan 22, 2015

A Personal Victory: Oracle Database Sample Schemas are on GitHub

For anyone who ever deleted a row from a table in Oracle's Sample HR schema and wanted it back, help is nearby. You no longer have to download the full "Oracle Database 12c Release 1 Examples" zip (499,228,127 bytes worth for the Linux bundle) and run the Oracle installer. Now you can clone our GitHub db-sample-schema repository and run the creation SQL scripts in SQL*Plus.

This new repository installs these six sample schemas:

  • HR: Human Resources
  • OE: Order Entry
  • PM: Product Media
  • IX: Information Exchange
  • SH: Sales History
  • BI: Business Intelligence

Because of the widespread use of these schemas, we did minimal changes to the bundle. The install, as it is given, installs all schemas and needs to be done on a database server since file system access is needed from the database.

But now, if you want, you can fork the repo and modify it to install just the HR schema from a client machine. Or change your fork to install the HR schema into an arbitrary user name of your choice so multiple people can test the same data set. And what about modifying the script to do DROP TRIGGER SECURE_EMPLOYEES getting rid of that annoying time-based trigger which yells 'You may only make changes during normal office hours' if you try to make changes after 6pm or on weekends? This may be a great teaching tool about triggers but not useful when you are configuring demonstrations for big conferences late into the night!

And why is this a personal victory? Because as a client tool person, how to find these schema creation scripts has irked me in the past. The HR schema replaced SCOTT/TIGER in the Oracle documentation a long time ago but was not easily available to use. I've written a lot of examples using HR but never had a good way to instruct how to install the schema. I'm glad to have helped (being partially modest here about the legal and administrative things it required) getting this small set of scripts out on GitHub. If it makes it easier for someone to talk about features or issues by reference to a common data set, then my job is done. Having the scripts readily available is also a reminder to the Oracle community to share information and knowledge efficiently. Even as we head to a world of cloneable databases and snapshots, sometimes it is just easier to run a SQL script.

This repo is a piece of a jigsaw, and it can be used where it fits. The schemas could be now considered "traditional". In future, Oracle Database teams will continue to create fresh data sets to show off newer and upcoming database features, such as these analytical-sql-examples that you might be interested in.

Tuesday Jan 20, 2015

Introducing node-oracledb - a Node.js driver for Oracle Database

When we announced we were working on a Node.js driver for Oracle Database, it was to a large, enthusiastic audience at Oracle OpenWorld. I'm excited to let you know we have just pushed node-oracledb to GitHub.

Node.js is an upcoming, open source, cross-platform runtime environment for writing server-side and networking applications in JavaScript.

What is node-oracledb?

The node-oracledb driver connects to Oracle Database to make Node.js applications fast and functional. The node-oracledb 0.2 driver preview release already supports:

Node-oracledb is an open source project maintained by Oracle under the Apache 2.0 license. Contributions can be made under The Oracle Contributor Agreement

Where can I get node-oracledb?

To install node-oracledb, clone the repo, install the free Oracle Instant Client (or use a local Oracle Database such as the free Oracle Express Edition), and use npm to install from the cloned directory. The full instructions show how to create a local Node.js instance and covers more details. The driver is currently only on GitHub.

The driver uses Oracle client libraries - the same way Oracle Database drivers for other C based scripting languages do. This means it can take advantage of the significant feature set, engineering, and testing invested in those libraries. It also allows Node.js applications to talk to multiple different versions of the database with Oracle's standard client-to-server version compatibility.

The node-oracledb driver will build on Oracle Linux, OS X and Solaris. Our current testing has focused on Oracle Linux 6. Windows support shouldn't be difficult to add - sorry it's not there yet.

How do I use node-oracledb?

A simple query example is:

var oracledb = require('oracledb');

    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  function(err, connection)
    if (err) { console.error(err); return; }
      "SELECT department_id, department_name "
    + "FROM departments "
    + "WHERE department_id < 70 "
    + "ORDER BY department_id",
      function(err, result)
        if (err) { console.error(err); return; }

The output, with Oracle's HR schema, is:

$ node select.js
[ [ 10, 'Administration' ],
  [ 20, 'Marketing' ],
  [ 30, 'Purchasing' ],
  [ 40, 'Human Resources' ],
  [ 50, 'Shipping' ],
  [ 60, 'IT' ] ]

The examples directory and API documentation contain more examples.

What is the future for node-oracledb?

We are actively working on adding features to node-oracledb including Windows platform support, LOB support, batch fetching / streaming of large query result sets, and DRCP support. We are also working on things like making it available on for standard npm installation, and making our test suite publishable.

We look forward to your constructive feedback so we can incorporate any fixes and "must-haves" into a 1.0 release soon.

I'm looking forward to sharing more with you as the driver progresses. My colleague Dan McGhan will also be evangelizing JavaScript and Oracle at

node-oracledb Links

Monday Jan 19, 2015

The Easiest Way to Install Oracle Database on Mac OS X

tl;dr Your OS X applications can connect to Oracle Database as if it was running natively on OS X if you simply run Oracle Database in a VirtualBox VM with port forwarding enabled (easy).

To work backwards through the installation process: in the Network window of the VirtualBox GUI, I enable a NAT Network adapter.

Then, under Networking -> Advanced -> Port Forwarding, I create a TCP rule with Host IP and both the Host and Guest Port fields set to the port number that the Oracle network listener in the VM is using: the Oracle default is 1521. I leave the Guest IP field blank.

I click OK twice, dismissing the "The current port forwarding rules are not valid. None of the host or guest port values may be set to zero." dialog

Start the VM. If the DB is set to start during OS boot, you don't even need to log in.

Update: If you have Oracle Database 12c you can use the new EM Express console for DB management and monitoring. Just enable port forwarding for port 5500 and then browse (from OS X) to https://localhost:5500/em. See this article by Gerald Venzl.

Update: If you want to access the database from a second, NAT-configured VM, simply configure the DB VM's port forwarding as described in this article. In your second VM, use an Oracle connect string like "". The IP address to use is the VirtualBox gateway, see Configuring the address of a NAT network interface in the VIrtualBox documentation.

The Client

Connecting to the database from OS X tools and clients uses the same forms of Oracle connect string as if the database was native on OS X. For example, using the "Easy Connect" hostname/service_name form, I can simply use 'localhost' as if the database was on OS X:

cjones@mac:~$ sqlplus cj/welcome@localhost/orcl

SQL*Plus: Release Production on Mon Jan 19 09:20:38 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Here I was using SQL*Plus from the free, easy to install Instant Client bundle. Download the Oracle Instant Client Basic, SDK and SQL*Plus ZIP files. Unzip them, create the two symbolic links given in the install instructions, and set DYLD_LIBRARY_PATH to the Instant Client directory.

The Instant Client can be used, among other things, for building PHP, Python, Node.js and other language drivers. With each of these you would use exactly the same connect string to connect to the database.

Prebuilt VMs

There are prebuilt VMs with Oracle Database already installed, such as the Database App Development VM. Download and import it into VirtualBox.

The Database

It's easy to install your own Oracle Database "XE" Express Edition database for development. After the XE RPM is installed on Oracle Linux 5 and 6, run its simple configuration script to set up the administration passwords, the listener port (default is 1521), and whether to autostart during boot. Install XE by following the ten steps in Chapter 4 of The Underground PHP and Oracle Manual or check the official instructions.

Alternately you could install the Enterprise Edition for free ("only for the purpose of developing, testing, prototyping, and demonstrating" - read the click-through license). There are various scripts on the web to autostart the DB at VM machine boot.

The OS

If you need an OS, Oracle Linux is free from our yum server. Download the ISO, create a new VirtualBox VM and tell it where to find the ISO. Boot and follow the install prompts.


VirtualBox is free from and runs on OS X, Windows, Linux and Solaris.

The only "trick" to using VirtualBox is to remember the magic cursor-releasing key, e.g. the Left Command Key on OS X. This key is useful if/when you haven't installed the VirtualBox "Guest Additions" into the VM and clicking into the VM window captures the cursor. The specific key combination is shown as a reminder on the bottom right of the containing VirtualBox VM window. Pressing it returns cursor control to the host OS. Luckily, once you install the "Guest Additions" the cursor is automatically released when you mouse out of the VM window.

Thursday Jan 31, 2013

OS X Users! 11gR2 Oracle Instant Client 32 & 64-bit is now available

The Oracle 11g Release 2 ( Database Instant Client for Apple OS X on Intel x86-64 is now available for download from OTN. It is supported on the two latest OS X releases: Lion (10.7) and Mountain Lion (10.8). It provides both 32-bit and 64-bit client support.

Oracle Instant Client is a simple bundle of libraries that client tools and programs (like PHP and Ruby) can link with. This allows those tools to access Oracle Databases.

Any issues with Instant Client can be posted to the OTN Instant Client Forum.

Monday Oct 24, 2011

PHP oci_password_change() ORA-1017 gotcha with Oracle Database

The Oracle Instant Client for Linux is now available on OTN. I know many users will upgrade sooner rather than later to get the "latest and greatest" release.

However, if your PHP application uses oci_password_change (or its old alias ocipasswordchange), you should upgrade the client libraries (Instant or normal "full" client) and the Oracle Database version to at the same time. Otherwise oci_password_change will fail with the error "ORA-1017: invalid username/password". If you're not using oci_password_change then this won't impact you.

Since the protocol change is in the Oracle libraries, the change is not just limited PHP. Any C program using Oracle's Call Interface "OCIPasswordChange" API to change passwords will also have the same restrictions.

Thursday Mar 03, 2011

More on PHP and Oracle 11gR2 Improvements to Client Result Caching

[Read More]

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Twitter: @ghrd
OTN: Scripting Languages
Book: Free PHP Oracle book

Blaine Carter
Dan McGhan


« May 2016