Wednesday 20 November 2013

Python cx_Oracle on Ubuntu 12.04

There are some older articles on getting cx_Oracle working using RPMs and alien but it seems Oracle are now providing non-RPM downloads. Below are the (quick and dirty) steps I followed to get it installed on Ubuntu 12.04 x64.

  1. Download both the "Instant Client Package - Basic Lite" and "Instant Client Package - SDK" ZIP files from the Oracle Instant Client download page (taking note of the version of Oracle you are connecting to)
  2. Unzip both the files, they will create a directory correponding to the Oracle version, instantclient_11_2 for example
  3. Change to the instantclient directory created in the previous step and:
    • Create symbolic links to the version specific files: 
      • libclntsh.so -> libclntsh.so.11.1 
      • libocci.so -> libocci.so.11.1
    • Create a lib directory (mkdir lib)
    • Move lib* to the lib directory (mv lib* lib)
    • Move header files from ./sdk/include to . (mv ./sdk/include/*.h .)
  4. Optionally move the instantclient directory to another location
  5. Sudo to root (sudo -s) and build the module:
    • Install the python-dev package (apt-get install python-dev)
    • Export environment variables: 
      • export ORACLE_HOME="path/to/instantclient"
      • export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$ORACLE_HOME/lib"
    • Install the cx_oracle module using pip (pip install cx_oracle)
    • Hopefully get a "Successfully installed cx-oracle" message
  6. Exit the root shell (Ctrl-D)
  7. Add the ORACLE_HOME and LD_LIBRARY_PATH environment variables to your profile (or just manually export them)
  8. Launch python and test:
>>> import cx_Oracle
>>> dsn = cx_Oracle.makedsn('host', port, 'sid')
>>> connection = cx_Oracle.connect('user','password',dsn)
>>> cursor = connection.cursor()
>>> results = cursor.execute("select id from table where ROWNUM <= 10")
>>> cursor.fetchall()
[(705,), (718,), (719,), (721,), (725,), (726,), (727,), (737,), (748,), (769,)]
>>> cursor.close()
>>> connection.close()