“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.
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 :
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.
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
Persuading sudo to see $ORACLE_HOME
At this point, the solution presented here comes to the rescue.
In the terminal run…
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 184.108.40.206.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 220.127.116.11.0 >>>
Now you’ve got cxOracle up and running, you may want to check out some rather useful tips on how best to use it :