Monday, October 02, 2006

Oracle-free Oracle access

I got an interesting question from Guido d'Amico... he wants to use Python scripts to access Oracle databases from machines with no Oracle software installed. Between us, we came up with these options.
  • cx_Oracle and DCOracle2: These "classic" DB-API2 modules both rely on the OCI (Oracle Call Interface), a piece of software distributed by Oracle. (I believe all comparable means for accessing Oracle from other languages rely on the OCI, too.) There's just no way around that - you need some sort of Oracle client installed on the machine you're using them from.
    You don't have to bulk up your machine with a full-blown standard Oracle client, though.
    • Oracle Instant Client is lightweight (85 MB on my Windows box), free, and redistributable. For better and worse, it comes as a simple zipped set of files - if you want any environment variables set (ORACLE_HOME, PATH), you need to do that yourself.
    • OracleXEClient is likewise lightweight (72 MB) and free, and very easy to install.
    Neither of these options comes with a /network/admin/ folder, which might be a little confusing - unless you want to make your connections with Easy Connect, you'll need to set up $ORACLE_HOME/network/admin/tnsnames.ora by yourself.
  • You can use ODBC. mxODBC has been around for a while, but is not free for commercial use. pyODBC is free, and I hadn't actually heard of it until I researched this question - maybe I'll review it (or at least find a review) sometime soon.
  • You can use JDBC from Jython. Andy Todd and Przemek Piotrowski have blog posts detailing this.
  • You can go to IronPython and... um... OK, I've never yet done database access from IronPython, but I assume that using ODT.NET from IronPython is easy enough.

    EDIT: Przemek Piotrowski has not just made it work, he's posted a tutorial on ODP under IronPython. Thanks, Przemek!

3 comments:

Andy Todd said...

Of course, if you go the ODBC route you need an ODBC driver that talks to Oracle. I believe both Microsoft and Oracle make ones available. Which means that you end up with something that talks OCI on your desktop anyway.

Also note that mxODBC isn't free for commercial use.

For minimal footprint I'd go with the instant client and cx_Oracle, although I should note that I've never tested this combination. Hmmm, I feel a blog post coming up.

Anonymous said...

Hi Catherine, actually you don't need to setup a tnsnames.ora file in $ORACLE_HOME/netword/admin to use db aliases. This is an old trick: you can take one of those aliases, remove the CR and use the 'long line' as your connect string.

Ex: connect user/pass@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT= 1521))) (CONNECT_DATA = (SERVICE_NAME = myservice)(SERVER = DEDICATED)))"

It can be used on RAC too instead of ez-connect (which doesn't allow for client side load balancing ).

Luca

Guido d'Amico said...

Hi Catherine,
thanks for the profiling ;-) this was very informative indeed!

My problem was that I need to have several machines that run tests to be able to get the tests' definitions and to upload the tests results to a db, which is not locally installed.

The solution cx_Oracle + Instant Client would have worked OK, but given that the same scripts needed to access other dbs (mySQL) and that they are run through Jython, we ended up using JDBC. This allows for a creation of a network module that would hide completely the details fo which db and driver is used from the testers' eyes (not a requirement, but a nice to have anyway).

One last comment about tnsnames.ora: in the docs for cx_Oracle there is a nice function called

makedsn

that allows to create the connection string on the fly just from the host, port and sid, which I found very convenient.

Thanks!