Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

“We need to go through Tow-ces-ter”, suggested Deb.
“It’s pronounced Toast-er”, I corrected gently.
“Well, that’s just silly”, came the indignant response, “I mean, why can’t they just spell it as it sounds ?”
At this point I resisted the temptation of pointing out that, in her Welsh homeland, placenames are, if anything, even more difficult to pronounce if you’ve only ever seen them written down.
Llanelli is a linguistic trap for the unwary let alone the intriguingly named Betws-Y-Coed.
Instead, I reflected on the fact that, even when you have directions, things can sometimes be a little less than straight forward.

Which brings me to the wonderful world of Python. Having spent some time playing around with this language, I wanted to see how easy it is to plug it into Oracle.
To do this, I needed the cxOracle Python library.
Unfortunately, installation of this library proved somewhat less than straightforward – on Linux Mint at least.
What follows are the gory details of how I got it working in the hope that it will help anyone else struggling with this particular conundurum.

My Environment

The environment I’m using to execute the steps that follows is Mint 13 (with the Cinnamon desktop).
The database I’m connecting to is Oracle 11gXE.

In Mint, as with most other Linux Distros, Python is part of the base installation.
In this particular distro version, the default version of Python is 2.7.

If you want to check to see which version is currently the default on your system :

which python
/usr/bin/python

This will tell you what file gets executed when you invoke python from the command line.
You should then be able to do something like this :

ls -l /usr/bin/python
lrwxrwxrwx 1 root root 9 Apr 10  2013 python -> python2.7

One other point to note is that, if you haven’t got it already, you’ll probably want to install the Oracle Client.
The steps you follow to do this will depend on whether your running a 32-bit or 64-bit OS.

To check this, open a Terminal Window and type :

uname -i

If this comes back with x86_64 then you are running 64-bit. If it’s i686 then you are on a 32-bit os.
In either case, you can find the instructions for installation of the Oracle Client on Debian based systems here.

According to the cxOracles’s official SourceForge site, the next bit should be simple.
Just by entering the magic words…

pip install cxOracle

…you can wire up your Python scripts to the Oracle Database of your choice.
Unfortunately, there are a few steps required on Mint before we can get to that point.

Installing pip

This is simple enough. Open a Terminal and :

sudo apt-get install python-pip

However, if we then run the pip command…

pip install cx_Oracle

cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory

It seems that, in order to run this, there is one further package you need…

sudo apt-get install python-dev

Another point to note is that you need to execute the pip command as sudo.
Even then, we’re not quite there….

sudo pip install cx_Oracle

Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    Traceback (most recent call last):
      File "<string>", line 14, in <module>
      File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>
        raise DistutilsSetupError("cannot locate an Oracle software " \
    distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 14, in <module>

  File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>

    raise DistutilsSetupError("cannot locate an Oracle software " \

distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

----------------------------------------
Command python setup.py egg_info failed with error code 1
Storing complete log in /home/mike/.pip/pip.log

So, whilst we now have all of the required software, it seems that sudo does not recognize the $ORACLE_HOME environment variable.

You can confirm this as follows. First of all, check that this environment variable is set in your session :

echo $ORACLE_HOME
/usr/lib/oracle/11.2/client64

That looks OK. However….

sudo env |grep ORACLE_HOME

…returns nothing.

Persuading sudo to see $ORACLE_HOME

At this point, the solution presented here comes to the rescue.

In the terminal run…

sudo visudo

Then add the line :

Defaults env_keep += "ORACLE_HOME"

Hit CTRL+X then confirm the change by selecting Y(es).

If you now re-run the visudo command, the text you get should look something like this :

#
# This file MUST be edited with the 'visudo' command as root.
#
# Please consider adding local content in /etc/sudoers.d/ instead of
# directly modifying this file.
#
# See the man page for details on how to write a sudoers file.
#
Defaults        env_reset
Defaults        mail_badpass
Defaults        secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:$
Defaults        env_keep += "ORACLE_HOME"
# Host alias specification

# User alias specification

# Cmnd alias specification

# User privilege specification
                               [ Read 30 lines ]
^G Get Help  ^O WriteOut  ^R Read File ^Y Prev Page ^K Cut Text  ^C Cur Pos
^X Exit      ^J Justify   ^W Where Is  ^V Next Page ^U UnCut Text^T To Spell

You can confirm that your change has had the desired effect…

sudo env |grep ORACLE_HOME
ORACLE_HOME=/usr/lib/oracle/11.2/client64

Finally installing the library

At last, we can now install the cxOracle library :

sudo pip install cx_Oracle
Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    
Installing collected packages: cx-Oracle
  Running setup.py install for cx-Oracle
    
Successfully installed cx-Oracle
Cleaning up...

To make sure that the module is now installed, you can now run :

python
Python 2.7.3 (default, Feb 27 2014, 19:37:34) 
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> help('modules')

Please wait a moment while I gather a list of all available modules...

If all is well, you should be presented with the following list :

ScrolledText        copy_reg            ntpath              tty
SgiImagePlugin      crypt               nturl2path          turtle
SimpleDialog        csv                 numbers             twisted
SimpleHTTPServer    ctypes              oauth               types
SimpleXMLRPCServer  cups                opcode              ubuntu_sso
SocketServer        cupsext             operator            ufw
SpiderImagePlugin   cupshelpers         optparse            unicodedata
StringIO            curl                os                  unittest
SunImagePlugin      curses              os2emxpath          uno
TYPES               cx_Oracle           ossaudiodev         unohelper
TarIO               datetime            packagekit    

Finally, you can confirm that the library is installed by running a simple test.
What test is that ?, I hear you ask….

Testing the Installation

A successful connection to Oracle from Python results in the instantiation of a connection object. This object has a property called version, which is the version number of Oracle that the database is running on. So, from the command line, you can invoke Python…

python
Python 2.7.3 (default, Feb 27 2014, 19:58:35) 
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.

… and then run

>>> import cx_Oracle
>>> con = cx_Oracle.connect('someuser/somepwd@the-db-host-machine/instance_name')
>>> print con.version
11.2.0.2.0
>>> 

You’ll need to replace someuser/somepwd with the username and password of an account on the target database.
The db-host-machine is the name of the server that the database is sitting on.
The instance name is the name of the database instance you’re trying to connect to.

Incidentally, things are a bit easier if you have an Oracle client on your machine with the TNS_ADMIN environment variable set. To check this :

env |grep -i oracle
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
ORACLE_HOME=/usr/lib/oracle/11.2/client64

Assuming that your tnsnames.ora includes an entry for the target database, you can simply use a TNS connect string :

>>> import cx_Oracle
>>> con = cx_Oracle.connect('username/password@database')
>>> print con.version
11.2.0.2.0
>>> 

Useful Links

Now you’ve got cxOracle up and running, you may want to check out some rather useful tips on how best to use it :

ANSI Joins and Uppercase Keywords – making PL/SQL look less like COBOL

The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.
Continue reading

Oracle’s hidden documentation – Commenting the Data Model in Oracle

The Football (or Soccer, if you prefer) World Cup is almost upon us.
England have absolutely no chance so even the false hope that traditionally accompanies major tournaments won’t be around to spoil the enjoyment.
What makes this World Cup extra special is the fact that it’s taking place in Brazil – the spiritual home of the Beautiful Game.
The only previous occasion that Brazil hosted the tournament was in 1950, and it’s worth a brief look at what went on then, if only to provide the basis of the examples that follow.
Back in 1950, as now, money was a bit scarce.
Brazil agreed to host the tournament on condition that the format would be designed to maiximize the number of games played and therefore the gate revenue generated.
It is for this reason that the 1950 tournament is unique in World Cup history as the only tournament to be decided, not by a final, but by a round-robin “Final Pool”.
Then, as now, England travelled to Brazil. Unlike now there was a fair degree of confidence, not to say arrogance, about the prospects of the national team showing these foreigners how the game should really be played.
The Empire may have been slipping away, but it was still a widely held belief – in England at least – that God was an Englishman.
In the event, England managed to lose to an amatuer USA team 1-0 and then get sent packing by Spain. Continue reading

Tracing for fun and (tk)profit

If you ever wanted proof that time is relative, just consider The Good Old Days.
Depending on your age, nationality, personal preferences etc, that time could be when rationing finally ended; or when Trevor Brooking won the Cup for West Ham with a “bullet” header; or possibly when Joe Carter hit a three-run homer to seal back-to-back World Series for the Blue Jays.
Alternatively, it could be when you were able to get on to the database server and use tkprof to analyse those tricky database performance issues.

In these days of siloed IT Departments, Oracle trace files, nevermind the tkprof utility are out of the reach of many developers.
The database server itself is the preserve of Unix Admins and DBAs, groups which, with good reason, are a bit reluctant to allow anyone else access to the Server at the OS level.

Which is a pity. Sometimes there is just no substitute for getting into the nitty gritty of exactly what is happening inside a given session.

For those of you who miss The Good Old Days of tkprof, what follows is an exploration of how to access both trace files and even the tkprof utility itself without leaving the comfort of your database.
I’ll go through a quick recap of :

  • how to generate a trace file for a session
  • using tkprof to make sense of it all

Then, coming bang up to date :

  • viewing a trace file using an external table – and why you might want to
  • Using a preprocessor to generate tkprof output
  • implementing a multi-user solution for tkprof

Continue reading