Tuesday, October 09, 2007

cx_Oracle and Oracle XE on Ubuntu

When I first posted about using Oracle XE on Ubuntu, I thought that getting cx_Oracle working with XE was pretty easy. I was wrong! Last night, I installed on a "clean" machine, and it was a lot harder; perhaps last time, I benefited from packages I'd already installed, meeting secret prereqs without knowing it.

[EDIT: As of June 2008, a streamlined set of instructions is here.]

Fortunately, I did work out the necessary steps.
  1. sudo apt-get install libc6-dev

    If you don't, you'll get
    In file included from /usr/lib/gcc/i486-linux-gnu/4.1.2/include/syslimits.h:7,
    from /usr/lib/gcc/i486-linux-gnu/4.1.2/include/limits.h:11,
    from /usr/include/python2.5/Python.h:18,
    from cx_Oracle.c:6:
    /usr/lib/gcc/i486-linux-gnu/4.1.2/include/limits.h:122:61: error: limits.h: No such file or directory
    In file included from cx_Oracle.c:6:
    /usr/include/python2.5/Python.h:32:19: error: stdio.h: No such file or directory
    ... when you try to install cx_Oracle.

  2. export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    export PATH=$ORACLE_HOME/bin:$PATH


    If you don't, you'll get
    error: cannot locate an Oracle software installation
    or

    ImportError:libclntsh.so.10.1:cannot open shared object file: No such file or directory
  3. Download the source code tarball, unzip and unpack, cd into it, and
    sudo python setup.py install
    You will get
    error in cx_Oracle setup command: Oracle home does not refer to an 8i, 9i, 10g or 11g installation
    because setup.py doesn't account for XE. You need to hack it.

    Download the patch useXE.diff and run
    patch setup.py useXE.diff

    Now run
    sudo python setup.py install
    again.

    I'm going to submit a patch to the cx_Oracle folks, so hopefully it will be fixed for the next release.

    [EDIT: Anthony Tuininga, cx_Oracle's developer, has taken my patch, improved it, and incorporated it into the cx_Oracle trunk; as of the next cx_Oracle release beyond 4.3.3, this is fixed. This sort of thing is what makes open source fun!

    To grab the patched version from the cx_Oracle development trunk right now, install Subversion on your machine, then
    svn co https://cx-oracle.svn.sourceforge.net/svnroot/cx-oracle/trunk cx-oracle
    ]

  4. If you're accessing Oracle XE on your own machine, start your listener. On my machine, at least, the XE installation didn't do that.
    sudo su - oracle
    export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    export PATH=$ORACLE_HOME/bin:$PATH
    lsnrctl startexit


    You may want to edit your .bashrc to do this; otherwise, you'll need to for every session in which you access Oracle.

    If you're using Oracle XE as a client to access Oracle (XE or otherwise) on another machine, skip this step, but you'll need to make sure the SID you're looking for is in $ORACLE_HOME/network/admin/tnsnames.ora.

  5. At last, you can start using cx_Oracle! See the DB-API2 Cheat Sheet for some quick syntax reminders.

EDIT: comment from teambob

I mistakenly deleted the following comment:

I installed via easy_install, but there was a bit of a trick.

After setting the environment variables I installed using the following command: sudo -E easy_install cx_oracle

The "-E" option to sudo causes all your environment variables to be passed to easy_install

13 comments:

Unknown said...

Thanks a lot Catherine, I had the same problem making cx_Oracle work with Ubuntu. Now it works fine.
-Jens

Unknown said...

Thanks, that just made my day... a major breakthru- I was going mad trying to hook a Zope DA up to cx_Oracle

Unknown said...

Thanks, that just made my day... a major breakthru- I was going mad trying to hook a Zope DA up to cx_Oracle

ChrisW said...

Hi Catherine,

Thanks for this, but in the version of the instantclient I have, libnnz10.so lives in the root in the oracleHome, so I had to remove the ',lib' from that line. I this a common thing for people?

cheers,

Chris

ChrisW said...

Hi Catherine,

Thanks for this, but in the version of the instantclient I have, libnnz10.so lives in the root in the oracleHome, so I had to remove the ',lib' from that line. I this a common thing for people?

cheers,

Chris

Anonymous said...

Hi catherine,

Thanks, I have successfully install a cx_Oracle 4.3.2 with an oracle 8.1.7 client on ubuntu hardy heron.

Mikael.

Unknown said...

I tried that hack for cx_oracle but get the following error...

$ patch setup.py useXE.diff
patching file setup.py
Hunk #1 FAILED at 144.

my overall error message is here...

4$ sudo python setup.py install
Traceback (most recent call last):
File "setup.py", line 72, in module
raise DistutilsSetupError, "cannot locate an Oracle software installation"
distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

Unknown said...

Jeremiah,

If you go to http://sourceforge.net/projects/cx-oracle/ you can get cx_Oracle 4.4, where this bug has already been fixed. I'd try that instead of messing around with this patch - it was only a stopgap. Get your ORACLE_HOME and LD_LIBRARY_PATH set properly, install cx_Oracle 4.4, and I think it'll work.

Unknown said...

thanx!

(I got ImportError: libclntsh.so.10.1 and you helped me)

Anonymous said...

THANK YOU! This had the little snippet I needed to get it all working.

Unknown said...

I tried all of this and none of it worked. What did work for me was to set the environment variable LD_LIBRARY_PATH in httpd.conf

#replace with your real LD_LIBRARY_PATH, your $ORACLE_HOME/lib, but the full path.
SetEnv LD_LIBRARY_PATH "/usr/.../server/lib"

You still need the bash.bashrc export statements to log into sqlplus, but this should let you use cx_Oracle on apache.

sabs said...

svn co https://cx-oracle.svn.sourceforge.net/svnroot/cx-oracle/trunk cx-oracle has been moved to :
https://svn.code.sf.net/p/cx-oracle/code/trunk

sabs said...

svn co https://cx-oracle.svn.sourceforge.net/svnroot/cx-oracle/trunk cx-oracle is now moved to svn co https://svn.code.sf.net/p/cx-oracle/code/trunk
Still I got error regarding Oracle version coz my instant client was 12.1 (instantclient_12_1). So added the following line in setup.py:
filesToCheck = [
("12c", "libclntsh.so.12.1"),
..