Installing and using cx_Oracle on Unix

Here's a mini-HOWTO on installing the cx_Oracle Python module on Unix systems (tested on Linux and Solaris.) I always forget one of these steps every time I install this module on one of my systems, so I thought I'd write them down. Other people might find this useful, so here goes:

1. Download cx_Oracle-4.1.tar.gz from here.
2. Become root on the target system.
3. Make sure you have the ORACLE_HOME environment variable defined for user root and pointing to the correct Oracle installation directory on your server. This step is necessary because the cx_Oracle installation process looks for headers and libraries under ORACLE_HOME.
4. Install cx_Oracle by running 'python setup.py install'.
5. Become user oracle, since this is the user you'll most likely want to run python scripts as when interacting with Oracle databases. Make sure you have the following line in oracle user's .bash_profile (or similar for other shells):

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

If you don't add ORACLE_HOME/lib to your LD_LIBRARY_PATH, you'll get an error such as:

>>> import cx_Oracle
Traceback (most recent call last):
File "", line 1, in ?
ImportError: ld.so.1: python: fatal: libclntsh.so.9.0: open failed: No such file or directory

Now you're ready to use the cx_Oracle module. Here's a bit of code that shows how to connect to an Oracle database and run a SELECT query:

import cx_Oracle
from pprint import pprint

connection = cx_Oracle.Connection("%s/%s@%s" % (dbuser, dbpasswd, oracle_sid))
cursor = cx_Oracle.Cursor(connection)
sql = "SELECT * FROM your_table"
cursor.execute(sql)
data = cursor.fetchall()
print "(name, type_code, display_size, internal_size, precision, scale, null_ok)"
pprint(cursor.description)
pprint(data)
cursor.close()
connection.close()