The following is my attempt to document a remarkably painful process that got even more remarkably painful under El Capitan than it was under earlier versions of Mac OS. It concerns querying an Oracle database from within a Python script via the cx_Oracle library. If you don’t query Oracle databases from Python scripts via the cx_Oracle library, you are hereby advised to do literally anything else.

The general process of the steps below is:

  1. Get Oracle Instant Client (aka sqlplus) running. This does a couple things. First, it lets you confirm that you can run Oracle queries at all, outside of Python. Second, it installs some libraries and executables that cx_Oracle uses.
  2. Muck around in the libraries that sqlplus installed, to change internal references from /ade/... to @loader_path/....
  3. Install cx_Oracle.
  4. Muck around in the cx_Oracle shared-object (.so) files to change their internal references from relative paths to absolute ones.

Here goes, start to finish:

  1. Download Oracle Instant Client. You want to download “Instant Client Package – Basic”, “Instant Client Package – JDBC Supplement”, “Instant Client Package – SQL*Plus”, “Instant Client Package – SDK”, and “Instant Client Package – WRC: Workload Replay Client”, because why download one file when you can download five? Additional notes about downloading these files:
    1. You need to accept the license-agreement radio button at the top of the page first.
    2. You likely want the 64-bit files, unless your machine is ancient.
    3. You need to log in to download these files, which means you need to create an Oracle login first. Because why make downloading easy when you can make it annoying?
  2. Unzip all the files you just downloaded. For the purposes of this exercise, I assume you downloaded these files to ${HOME}/Downloads.
    mkdir oracle
    cp *.zip !$
    for i in *.zip; do unzip $i; done
  3. Make a system-level directory that will house sqlplus. Since El Cap, you can’t touch anything under /usr, so you’ll want to put it in /usr/local or /opt. For reasons that now elude me, I put it in /opt/oracle.
  4. Change the ownership on /opt/oracle so that you, rather than root, own all the files therein.
    cp -r ~/Downloads/oracle/instantclient_11_2 /opt/oracle
    sudo chown -R $(whoami): !$

  5. In pre-El Cap versions of OS X, you’d set the ${DYLD_LIBRARY_PATH} environment variable to point to where your libraries lived. In the sqlplus case, you’d add /directory/where/sqplus/lives to ${DYLD_LIBRARY_PATH} by adding a line like

    export DYLD_LIBRARY_PATH=${DYLD_LIBRARY_PATH}:/directory/where/sqplus/lives

    to your ~/.bashrc file.

    El Cap seems to have changed the treatment of DYLD_LIBRARY_PATH as part of System Integrity Protection. Maybe DYLD_LIBRARY_PATH stops working? That’s a problem, because all the pre-El Cap steps to configure cx_Oracle that you’ll find on the Internet involve setting that DYLD_LIBRARY_PATH to override the library paths inside the sqlplus executable. The library paths that sqlplus comes with are all absolute paths that seem to refer to Oracle’s own internal build system — like so:

    (16:40 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L sqlplus
    sqlplus:
    /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

    All those need to change to use the path to your sqlplus install.

  6. So if the $DYLD_LIBRARY_PATH option is out, one option is to muck around in the binary and change the library paths from /ade/... to some sort of variable that means “whatever path the executable is in”. Various places on the web suggested using @executable_path as that variable, but — again — that seems to work only under pre-El Cap versions of OS X. Now you need to use @loader_path instead (for reasons that I’ve not bothered to investigate). The method is like so:

    (16:43 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo install_name_tool -change /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib @loader_path/libsqlplus.dylib sqlplus
    (16:43 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L !$
    otool -L sqlplus
    sqlplus:
    @loader_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

    Note that the linked library is now @loader_path/.... Repeat this for all of the Oracle Instant Client files, and for all of the libraries linked within them. There’s a tool that does this. We end up, e.g., with

(16:45 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L sqlplus
sqlplus:
    @loader_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    @loader_path/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    @loader_path/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

Okay, so we fix up all the libraries. On my end, this allowed me to get a sqlplus console and connect to our Oracle server. Nice.

Next up: make the Python cx_Oracle library work with it. First, you need to create a couple of symlinks:

(16:49 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo ln -s libclntsh.dylib{.11.1,}
(16:50 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo ln -s libocci.dylib{.11.1,}

The reason here is apparently that cx_Oracle is trying to link in those libraries without referencing their version numbers; if you try to install cx_Oracle without those symlinks, you’ll get make(1) puke that contains this telling line:

    ld: library not found for -lclntsh

So you create the symlinks, then you do your classic pip install cx_oracle. Various places on the web suggested, more fully,

pip install --no-cache-dir --allow-external --allow-unverified cx_oracle

, and by this point I was past the point of arguing. That command worked, though it did warn me that --allow-external was deprecated and would eventually stop working.

Sure it’s installed, but that doesn’t mean it’ll actually work. The cx_Oracle.so file now links to libclntsh.dylib.11.1 — a relative path with no /path/to/libraries/... prefix. You need to find the .egg file that Python actually loads when you do import cx_Oracle; in my case, that path is /Library/Python/2.7/site-packages/cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg . That’s just a zip file, so

  1. copy it to some new empty directory under ${HOME}
  2. unzip it
  3. change the .so path from relative to absolute, via
    install_name_tool -change {,/opt/oracle/instantclient_11_2/}libclntsh.dylib.11.1 ./cx_Oracle.so
  4. zip the directory back up into the .egg file and copy it from your ${HOME} subdirectory back into the global library path:
sudo mv cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg /Library/Python/2.7/site-packages/cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg
  1. Does it work?
(17:19 -0500) slaniel@work_laptop:~$ python -c 'import cx_Oracle' && echo 'worked'
worked

Yes! It worked! (Contrast with what happens if you try ‘import cx_Oracl’ with no trailing ‘e’.)

And finally — finally — I can run queries against