How I got pyodbc and Oracle Instant Client working on Ubuntu
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.
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-126.96.36.199.0-1.x86_64.rpmwget https://download.oracle.com/otn_software/linux/instantclient/211000/oracle-instantclient-odbc-188.8.131.52.0-1.x86_64.rpmwget https://download.oracle.com/otn_software/linux/instantclient/211000/oracle-instantclient-devel-184.108.40.206.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-220.127.116.11.0-1.x86_64.rpm
alien -i oracle-instantclient-odbc-18.104.22.168.0-1.x86_64.rpm
alien -i oracle-instantclient-devel-22.214.171.124.0-1.x86_64.rpm
Finally, we need to add the new lib to the
LD_LIBRARY_PATH (tsk, tsk, but it works):
Add an entry for the driver in
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
Description=Oracle Unicode driver
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:
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
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