How I got pyodbc and Oracle Instant Client working on Ubuntu

A guide of questionable universality

John Raines
3 min readMay 12, 2021
Photo by Michael Schofield on Unsplash

I suddenly needed our application to query an Oracle server. We’re a Python shop that is currently running a python3.7-buster container. We use the pyodbc package for our other connections, and I was hoping we could get it working in this case. As I began to look for solutions, I first encountered a page in the pyodbc documentation titled Connecting to Oracle from RHEL or CentOS. Close, but not quite. After several hours of crawling the internets, I cobbled together the following guide, which may be not universal enough to help you at all. But if you’ve gotten this far, you’re desperate.

Note: I’ll provide commands more or less as they are run in our Dockerfile, minus the RUN. If you have a different use case, you may need some sudos.

Prereqs

We already had unixODBC installing in our container. For us, that’s a simple:

apt-get -y install unixodbc-dev

Get and install the Oracle Instant Client downloads

The URLs for current Instant Client version can be found on the Oracle site: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html. We used v21.1 and grabbed the following files, as specified in the pyodbc docs for running on Red Hat:

wget https://download.oracle.com/otn_software/linux/instantclient/211000/oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpmwget https://download.oracle.com/otn_software/linux/instantclient/211000/oracle-instantclient-odbc-21.1.0.0.0-1.x86_64.rpmwget https://download.oracle.com/otn_software/linux/instantclient/211000/oracle-instantclient-devel-21.1.0.0.0-1.x86_64.rpm

You can see that the downloads we want are the RPM format. So hooray if you’re RHEL, CentOS, or Amazon Linux, but the rest of us need another way to use them on Ubuntu. For that, I followed this brief walkthrough, which contains helpful explanations and caveats. Here’s the TL;DR:

apt-get install -y alienalien -i oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpm
alien -i oracle-instantclient-odbc-21.1.0.0.0-1.x86_64.rpm
alien -i oracle-instantclient-devel-21.1.0.0.0-1.x86_64.rpm

Finally, we need to add the new lib to the LD_LIBRARY_PATH (tsk, tsk, but it works):

export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/lib/oracle/21/client64/lib"

Add an entry for the driver in odbcinst.ini

And then you can append a driver entry into the /etc/odbcinst.ini file. I am accustomed to using tee for this, since I can sudo it if I wish. In a Dockerfile, I run this as a sequence of commands like so:

echo '' | tee -a /etc/odbcinst.iniecho '[MyOracle]' | tee -a /etc/odbcinst.iniecho 'Description=Oracle driver' | tee -a /etc/odbcinst.iniecho 'Driver=/usr/lib/oracle/21/client64/lib/libsqora.so.21.1' | tee -a /etc/odbcinst.iniecho 'UsageCount=1' | tee -a /etc/odbcinst.iniecho 'FileUsage=1' | tee -a /etc/odbcinst.ini

Another, nice syntax if you’re just making a shell script is:

sudo tee -a /etc/systemd/system/task_id_mgr.service > /dev/null <<EOT
[MyOracle]
Description=Oracle Unicode driver
Driver=/usr/lib/oracle/21/client64/lib/libsqora.so.21.1
UsageCount=1
FileUsage=1
EOT

Did you get an error?!

Because I did. I got this error:

[unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/21/client64/lib/libsqora.so.21.1' : file not found...

Despite the fact that that file certainly exists. This StackOverflow was helpful in directing me to search the dependencies using ldd. Running the command:

ldd /usr/lib/oracle/21/client64/lib/libsqora.so.21.1

printed a list of dependencies, one of which could not be found, a file named libaio.so.1. I’d suggest running this command yourself to ensure that all dependencies can be found. If nothing comes back missing, I expect the driver will function correctly. In my case, I needed to:

apt-get install libaio1 libaio-dev

Parting thoughts

I truly have no idea how universal this struggle is. The lack of documentation on it was what prompted me to write something. My guess is that different environments will have differing issues with dependencies. It would be great if the dependency failure did not provide an incorrect file not found error because that is pretty unhelpful. Thankfully, it’s easy to diagnose with ldd.

Happy coding.

--

--

John Raines

For money, I’m a software engineer who primarily works in machine learning platform design. For free, I read fantasy novels and raise children (my own).