Introduction

If you’ve been following this series of posts so far, you’ll be the proud owner of an Apache web server and an Oracle database server, with the one cunningly used to help build the other. After that initial help, though, the Apache server has pretty much been sat there not doing very much.

So what if we make the web server become the client of the database, allowing us to interact with the database indirectly, by browsing to the web server? It’s an efficient approach because it means users only need access to a browser: no Oracle clients are required, for example; and all application code lives in just one place (the web server), so code updates only need to happen in one place. It’s called a 3-tier application architecture (more generally, n-tier where “n” is greater than 2) and it’s the main way of bringing database functionality to users these days …we tend not to go around installing SQL*Plus on everyone’s desktops!

If this sounds more like the stuff a developer does than a database administrator, you are right to a point: but a good DBA needs to be aware of what developers do and how they do it, in general terms. Plus there are many times a DBA needs to knock some front-end code together to prototype something, so basic familiarity with development ideas is, I’d say, something of a requirement for the job.

We need a suitable language in which to write the sort of code a web server can handle: Perl, Python, Ruby… all would do just fine, but PHP is perhaps the easiest one to get started with. I’m no PHP expert, of course, so don’t expect elegant code and best-practices examples here… but if you follow along, you’ll at least be able to start seeing how to do basic 3-tier development. Once you know how to start, it’s up to you how far you want to read elsewhere around the subject and get really good at it!

This articles assumes you’ve read the other two in this series, and have built your web and database servers as those articles suggested.

Configuring Apache and PHP

The first thing we’re going to have to do, then, is allow the Apache server to know how to talk to the Oracle database. By definition, that means the Apache server needs to become a client of the database -and that can only happen if Oracle client software is installed on the Apache machine.

Happily, if you followed the instructions contained in the first article of this series, you will already have the necessary software to hand:

The relevant bits there are the two “instantclient” files: you need both the ‘basic’ and the ‘sdk’ ones for all of this to work. You’ll notice, too, that they both get downloaded from the Oracle website as zip files, so the first thing to do is to unzip them:

su - root
cd /var/www
unzip instantclient-basic-linux32-11.2.0.2.0.zip
unzip instantclient-sdk-linux32-11.2.0.2.0.zip

You’ll remember from the first article that I specifically enabled the root account, despite the Apache server being an Ubuntu box (where such things are usually frowned upon!) You could do all of this with sudo commands if you really insisted on it, but it’s a lot simpler just to become root. Every command from now on assumes you’re issuing them as root, anyway.

The two unzip commands will create a new directory, called instantclient_11_2 in the /var/www directory tree. Now do the following:

cd instantclient_11_2
ln -s libclntsh.so.11.1 libclntsh.so

That creates a necessary symbolic link so that a library with a very version-specific name can be found by referring to a non-version-specific one.

Now, step back up a directory, so you’re back at /var/www:

cd ..

Here we’ll drop the latest version of the OCI8 library needed to get PHP talking to Oracle:

wget http://pecl.php.net/get/oci8-1.4.5.tgz

(You may want to check out http://pecl.php.net/package/oci8 first to make sure that version of the file is the latest one. It was at the time of writing, but new releases come quite frequently)

Next, we install the php-pear package (it allows us to install PHP extensions), together with the php5-dev package (needed to let us compile new PHP modules) and the libaio1 package:

apt-get install -y php-pear php5-dev libaio1

Finally, we’ll actually install the OCI8 extension we’ve just downloaded:

pecl install oci8-1.4.5.tgz

That last command is obviously version-specific, so substitute in the exact name of the file you actually downloaded earlier. It will prompt you to specify “the path to the ORACLE_HOME directory…”. At this point, you type in the following:

instantclient,/var/www/instinstantclient_11_2

…which tells the installer (a) that you’re using the instant client and not a full-blown Oracle client; and (b) that the path to the instant client files is /var/www/instantclient_11_2. Note that there are no spaces in any of that lot: just a comma separates the two components.

At this point, quite a bit of software compilation will take place. The final message it outputs should be along the lines of:

Build process completed successfully
Installing '/usr/lib/php5/..../oci8.so'
install ok...

Now you just need to do the following:

echo extension=oci8.so >> /etc/php5/apache2/php.ini
echo extension=oci8.so >> /etc/php5/cli/php.ini
echo /var/www/instantclient_11_2 >> /etc/ld.so.conf
ldconfig
echo export ORACLE_HOME=/var/www/instantclient_11_2 >> /etc/profile
export ORACLE_HOME=/var/www/instantclient_11_2
/etc/init.d/apache2 restart

All of that does the following:

• The first two echo statements here cause an “extension=” line to be added to each of the PHP configuration files. They let PHP know that the oci8 Oracle connectivity software is available to use.
• The next line add a new entry to the list of which shared libraries exist on the system (and where they exist), whilst running “ldconfig” causes the system to pick up the contents of that revised list and build appropriate links and cache entries (so the Oracle client libraries can actually be used).
• The last echo statement simply sets a system-wide environment variable that says where the ORACLE_HOME is (which is the instant client directory created when we unzipped the Oracle software earlier). The export statement after that makes the ORACLE_HOME setting take immediate effect.
• With all that done, the last line re-starts the Apache web server, which should therefore now be aware of all the things we’ve just set.

It should now be possible to check that Apache and PHP at least know, theoretically, that it’s possible to connect to Oracle databases. You do that by opening a browser on your host machine (i.e., a browser that is remote from the web server itself) and invoking the page which displays the PHP configuration data (we created this in the first article in the series and in my case, that means browsing to http://192.168.0.47/phpdata.php). Check to see if there’s an OCI section (the various sections are displayed in alphabetical order). You want to see something like this:

Once you see an oci8 section in the output, you know you’re almost there.