SQL*Plus alternatives (at InOUG)

I spoke last Thursday at the Indiana Oracle Users Group on "The Command Line Lives: SQL*Plus and Alternatives".

I still need to get the full, S5 format presentation posted somewhere; for now, you can see the raw text of the presentation here.

It's an interesting topic, and one I'd like to get drawn more deeply into. Some nice things have been done in producing open-source command-line alternatives to SQL*Plus, including

• gqlplus
• Senora
• YASQL
• sqlpython

Unfortunately, the only one whose development currently seems to be active is sqlpython, and there's just me and Luca Canali working on that. I've just added functionality to sqlpython to let it fail over to sessions of the other tools (when "set failover true" is issued; doesn't work on Windows). That way, the exciting features of Senora and YASQL can be used from within sqlpython, instead of trying to remember which tool has which feature. It's quirky, though, since a full-fledged independent session of each tool is actually maintained "under the covers".

I'm dreaming of expanding sqlpython to the point where it incorporates all Senora and YASQL's features natively. Tragically, my dreams cannot yet be downloaded from the Cheese Shop.

xkcd

I promise not to make a habit of posting about xkcd.

But only because I'm counting on you to read it daily yourself.



I printed this today and then wandered the office, looking for someone to show it to, gradually coming to the horrifying realization that I don't work with anyone who would get it.

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