Python 3 + Oracle on Ubuntu Server 14.04

I’ll invite my readers to check my previous post on this topic to get a sense of how I feel about Oracle.

Small updates it seems to get cx_Oracle working with Python 3 on Ubuntu 14.04. Assume sudo/root for all the following.

  1. Download the RPM versions of both the basic and SDK clients for from
    1. Note: If you’re not familiar with the idiotic hell that is Oracle downloads, you have to do this on a browser where you can click a radio button and THEN download, so wget from the server on wish you actually want to install this stuff is unfortunately a no go.
    2. The 12.x version of this stuff still doesn’t work with cx_Oracle. At least not for me.
  2. scp the rpm files to the target server
  3. ssh to the target server
  4. apt-get install libaio1 alien
  5. For both the RPMs:
    alien -d RPM_FILE_NAME
  6. For both the newly created Debian packages:
  7. touch /etc/
  8. echo “/usr/lib/oracle/11.2/client64/lib” > /etc/
  9. echo “export ORACLE_HOME=/usr/lib/oracle/11.2/client64” >> /etc/environment
  10. echo “export LD_LIBRARY_PATH=$ORACLE_HOME/lib” >> /etc/environment
  11. source /etc/environment
  12. ldconfig
  13. pip3 install cx-oracle
  14. Put the relevant tnsnames.ora in $ORACLE_HOME/network/admin
Note that unlike my previous instructions you can install cx_Oracle using pip3 once you have everything in place. Maybe you could use pip before but I seemed to have better luck with the converted RPM, and at that point I was kind of tearing my hair out so I probably didn’t go back to take a run at it using pip once the Oracle bits were sorted.
The big change here is the necessity to install both the basic client and the SDK client packages for Oracle; it worked with Python 2.7 on Ubuntu 12.04 without the SDK package.

Python + Oracle on Ubuntu Server 12.04

Affectionately known among all non-masochists in the world of IT as The Seventh Circle of Hell (with real hell being preferable), working with Oracle is always a hair-tearing nightmarish fork-in-the-eye please-for-the-love-of-god-kill-me-now experience that none but those who look to Ted Bundy, Jeffrey Dahmer, and John Wayne Gacy for moral and spiritual guidance would wish upon even their most reviled enemies.

Yes, it’s that bad. And apparently nowhere is it worse than when one attempts to get Oracle working with Python on Ubuntu.

I’m not even talking about installing the Oracle database server itself here people, I’m just needing a Python application to talk to an existing Oracle database. One would think, as with every other database server on the planet (and yes, I’m including that other slice of hell SQL Server in that statement since it’s a damn sight simpler to get working — even on Linux — than Oracle), you’d simply apt-get and/or pip install a library or two and be done with it.

If you actually do think that, you’ve already forgotten that this is Oracle we’re talking about.

That said, one does what one has to do to keep the paychecks coming, so if you need to do this here’s the steps to make it all happen. (Note that on Step 1 I’m assuming you have already installed all the other Python packages you may need. I’m focusing on the stuff you may not have that you definitely need.)

  1. sudo apt-get install libaio1 alien
  2. Download the RPM of version of the Oracle client from (note that as of the date of this writing the 12.x version doesn’t work, or at least didn’t for me)
    1. You have to have an Oracle Web Account and use that to log in and download this, which makes using wget on the target server itself or automating the process for use with something like a Vagrant provisioning script rather problematic. Short version, you’ll have to download this locally and then scp it up to the target server. What I did is downloaded and converted this RPM as well as the other necessary RPM, converted them once, and put them in a git repo from which I can clone in my Vagrant provisioning script. Whether or not that adheres to the licensing agreement, I don’t know and I don’t care. If you’re paranoid, check with a lawyer before repeating my solution on this.
    2. If the version number differs slightly from what I have here, adjust later steps accordingly.
  3. Download the RPM of the Python 2.7/Oracle 11g version of the cx_Oracle Python libraries from
  4. scp the RPMs up to the target server as needed.
  5. Convert the RPMs to Debian packages using alien:
    sudo alien -d FILENAME.rpm (where FILENAME is of course the name of each of the two RPM files)
  6. sudo dpkg -i oracle-installclient11.2-basic_11.
  7. sudo vim /etc/
    1. Note: this file won’t already exist, so you’ll be creating this as a new file in this step
  8. Enter the following in the newly created oracle.conf file and save it:
  9. export ORACLE_HOME=/usr/lib/oracle/11.2/client64
  11. sudo ldconfig
  12. sudo dpkg -i cx-oracle_5.1.2-2_amd64.deb
  13. cd /usr/lib/python2.7
  14. sudo mv site-packages/cx_Oracle* dist-packages
  15. sudo rmdir site-packages
  16. sudo ln -s dist-packages site-packages
  17. Verify installation by opening a Python interpreter and run the following:
    import cx_Oracle
    1. If you don’t get an import error, everything is working properly

As far as automating this for use with Vagrant,  in my provisioning script I simply echoed the export statements in the steps above into /etc/environment, did source /etc/environment and followed that with ldconfig. Other than that the steps in the bash script are pretty much what’s above, but if people are interested in seeing the script let me know and I can post it.

And there you have it. A lot of trial and error and head bashing went into that final solution, and since I kind of cobbled together the steps from various resources I’ll post those below in case you want to see some of the other solutions and source material.

Happy Oracleing. Or not.



CFML and Oracle Stored Procedures: Experimentation and Solutions

I love a challenge. Most of the time when someone tells me something can’t be done, I have to prove it to myself even if these attempts are frequently exercises in futility. And even if said something actually cannot be done, I always feel like I learn a ton during the process.

Such was the case when I was working with a coworker recently on getting data back from some Oracle stored procedures into either Open BlueDragon or Railo. Before I proceed, let me state very clearly that I hardly ever touch Oracle so if I’m off-base on any of this I’m happy for an Oracle expert to educate me. In my defense, I will say I did quite a bit of searching on this topic, and more hours of experimentation than I care to admit, so I wasn’t just screaming “Oracle sucks!” and not actually trying to intelligently and logically get things to work. Also bear in mind that in this situation we didn’t write the storedprocs and we do not have the ability to alter them. (Necessity is the mother of invention and all that.)

Back to the task at hand. The storedprocs in question return Oracle REF CURSORs, which is basically Oracle’s way of thumbing their ever-expanding nose at the world by doing things differently than everyone else simply because they want to. (Oracle fans, if there’s a legitimate explanation for this utter nonsense I’m all ears, because I certainly couldn’t find one.)

If your first thought is, “But this just works on Adobe ColdFusion!” you are in fact correct. This is because Adobe CF ships with DataDirect drivers that handle getting an Oracle REF CURSOR into a format that can be used by CF. Because REF CURSORs ain’t your plain old Java ResultSets like the entire rest of the known database universe uses, my assumption is that there’s some translation that goes on either in the driver, or on the CF side, or both, to make this work. But in other CFML engines if you’re using the plain old JDBC drivers–yes, even Oracle’s own JDBC drivers–this doesn’t “just work.”

Let’s assume it’s entirely in the driver. I could either purchase the DataDirect drivers (expensive, and silly to have to do this for this one seemingly small thing), or I could use the ones that ship with CF. Well, a little birdie told me (ahem) that if you try to use the ones that ship with CF anywhere but with CF you’ll get a licensing error when trying to run queries. So that solution’s out.

You might also think you could simply call the storedproc from within a normal CFQUERY tag as opposed to using CFSTOREDPROC and an out parameter. Good thought, but even if you get the syntax figured out, since the storedprocs in question are looking for an out parameter to be passed into the storedproc (figure that one out), there’s no real way to get the data returned from the storedproc into a variable within the SQL statement that you can actually access. I spent a lot of time on this and got the storedproc itself executing fine, but getting at the data was where I spent the bulk of my experimentation time, and I finally gave up.

So where does that leave us? Long and short of it is that without using the DataDirect drivers (even if that would in fact work), there is no way that I could come up with that would allow calling an Oracle storedproc that returns a REF CURSOR from OpenBD or Railo that would work.

At that point do we throw up our hands in despair and state that it simply isn’t possible? Certainly not! We expand our thought process, put all options on the table, and forge ahead because we must not let technology, particularly of the Oracle variety, defeat us!

Since I was at the point where there was no way to have OpenBD or Railo deal with what the Oracle storedproc was returning (and believe me I’m happy to be proven wrong here), I decided the most expedient route would be to write the database access piece in Java and have CFML call that Java object. Once I’m in Java I can work with the Oracle and JDBC datatypes directly and have more control over what I will return to the CFML engine once I get the REF CURSOR back from Oracle.
The question then becomes if it’s better to convert the REF CURSOR into a native CFML query object on the Java side, or to return something like a standard Java ResultSet to CFML and use the ResultSet directly. There are numerous other options as well, particularly once we’re dealing with this in Java and have the freedom to do pretty much whatever we want.

I won’t go into the gory details, but in one of my early attempts I ran into a problem where I couldn’t access the ResultSet because by the time the Oracle delegate class was delegated to the Tomcat delegate class, I had to hack around a bit and get the underlying delegate on the Oracle side just to display the data. Neat problem to solve, but not exactly the most straight-forward solution.

I then decided to try creating a query object native to the CFML engine from the Java ResultSet. This worked well, and is certainly a valid approach, particularly if you know you’re going to deploy to a specific CFML engine. If you want to build something that will work on any CFML engine things get more complicated because your Java methods have to return a specific datatype, so you can’t have a single method in your Java class that will return the various underlying CFML query object types to the respective engines.

But as a wise man once said, all problems in computer science can be solved by another level of indirection (except, of course, the problem of too many levels of indirection). So I created the Java class to talk to Oracle and return the REF CURSOR, and then wrapped that puppy in a CFC that has conditional logic to return a CFML query object native to the engine on which it’s running (i.e. either OpenBD or Railo). Works great!

The only outstanding issue at this point that makes me a hair uncomfortable is that in order for all this to work, I can’t explicitly close my callable statements, ResultSets, and datasource connections on the Java side. If I close those items in Java and then return the ResultSet to the CFML engine, then I can’t actually iterate over the ResultSet since it’s closed.

I could of course close the ResultSet from CFML (I wouldn’t have access to the callable statement or the database connection directly), but I’m not nearly as concerned about the ResultSet as I am about the database connection. At least I’m using the Tomcat datasource connection pooling so that should mitigate this concern a bit. In theory Tomcat and the JVM will handle closing this stuff when it’s no longer in use, but I need to do a bit of load testing to be sure it’s going to work well under load.
There’s also some attributes in the Tomcat <Resource> XML definition that you can leverage to alleviate potential problems. Specificaly the maxActive, maxIdle, removeAbandoned, and removeAbandonedTimeout attributes can help keep things cleaned up since I’m in situation where I can’t clean things up manually.

As far as the database connection pooling goes, thus far we’ve seen problems on Windows with Tomcat 6.0.26. It works for a while but then starts throwing database connection pooling errors and all the connections time out. Drop this all on Red Hat Enterprise Linux with Tomcat 7, and things are rock solid. Go figure.

I’m happy to share the Java and CFC code if anyone’s interested, and I’m really curious to hear how others have solved this issue because I’m still not convinced what I came up with is the best solution, even though it’s certainly working well. So far, anyway (knock on wood).

Has Oracle been a disaster for Sun’s open source?

As others have noted, this is a good demonstration of the fact that open source projects are effectively “immortal”: provided there is sufficient interest among users, they can always be forked. It should also serve as a reminder to Oracle that they are the guardians of the open source projects formerly managed by Sun, not the owners (well, they own the copyright, but that’s not quite the same.) If it fails to move the projects forward in the way that many users would like, it may well be faced with more forks.

The problem is that Oracle is naturally trying to optimise its acquisition of Sun for its own shareholders, but seems to have forgotten that there are other stakeholders too: the larger open source communities that have formed around the code. That may make sense in the short term, but is undoubtedly fatal in the long term: free software cannot continue to grow and thrive without an engaged community.

This is a really insightful analysis by Glyn Moody of what the Oracle acquisition of Sun may mean for important open source technologies big and small, from things like OpenSSO and OpenSolaris to, MySQL, and even Java.

The mention of the “immortality” of free software and open source projects is really key in my mind. A lot of people wondered about the future of MySQL when Sun bought it, and many are outright panicked now that MySQL is in the hands of Oracle.

But that’s what’s so great about free software: none of these things are truly in Oracle’s control. They belong to the communities around them. So ultimately it wouldn’t be wise for Oracle to piss off the communities that made these projects what they are.

That doesn’t mean Oracle won’t do something terribly unwise but if they do, the code’s out there and the free software community will ultimately do what’s right and best for the project in the long run, even if that means forking.