Oracle Databases

Yesterday, I was given access to the data cache held by iSolutions here. Well, a view on to it. And it doesn’t have every piece of data we requested (first name, last name, e-mail address, faculty, supervisor, degree title, year of degree and photo), let alone all of the data the University holds (which is our eventual goal).

That aside, I was given the keys to dad’s car and was about to set off on a wild data-ride when I drove straight into the garage door. I know from experience that iSolutions use Oracle as their database system; what I didn’t know is how hard this is to access.

At first I thought maybe I should download a client so I can interrogate my view of the database, before installing the necessary PHP extension to access it programmatically. I found out this client (or part of it) is called SQL*Plus and headed over to the Oracle client download page.

However, after agreeing to the license and clicking the download link, I was hit with this page:

I tried a few different links, I tried going to the registration page, but everything to do with the Oracle sign on process was giving me this error page. “Well forget this client”, I thought to myself, “I’ll just interact with Oracle through PHP”.

Easier said than done!

Obviously my early code didn’t work:

$conn = oci_connect('hr', 'welcome', 'localhost/XE');

The oracle extension isn’t installed yet, so PHP doesn’t know what oci_connect is. “I’ve enabled MySQL before, it can’t be that much harder to enable Oracle”, I confidently muttered. I followed a few instructions:

Installing Oracle libraries for PHP5 on Ubuntu Server 8.04

How To Install Oracle XE in Ubuntu 64 Bit

The main problem I encountered is that in all cases, I have to download the Oracle client. Except I can’t get past that sign-on page! The first tutorial has a solution: add the Oracle OSS repository to your source list and type sudo apt-get install oracle-xe-client. Seems simple, except the repository only has binaries for the x86 version of the client, which apt-get refuses to install if you have an 64-bit installation of Ubuntu 🙁

The second tutorial explains how to manually install the .deb packages after wgetting them from the repository. It was going well until I ran sudo pecl install oci8, and received the following error:

/usr/bin/ld: skipping incompatible /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/lib/libclntsh.so when searching for -lclntsh
/usr/bin/ld: cannot find -lclntsh

I cannot find any information about why this doesn’t work, but I imagine it is because that clntsh library is for version 10.2.0 of the client and maybe oci8 expects something newer?

My only solution is that I need to download the latest client and see if that works. While writing this post, it dawned on me that it’s a bit strange that the Oracle sign on page was not working, so I tried it in a different browser. It turns out that the Oracle sign on page is incompatible with Opera, which is a shame.

I started following “something” but realised it was just making things more complicated and decided to just follow the instructions on the Ubuntu PHP Oracle page. And it works! Now I can connect PHP to Oracle.

Lessons Learned

1. Just follow the instructions on https://help.ubuntu.com/community/PHPOracle.
2. Don’t use Opera to download from the oracle site, because their single sign on page does not work.