Configuring OPAL on Ubuntu Desktop – without the Oracle Instant Client

Many years ago, my son had more-or-less worked out that Santa was a myth, but hadn’t wanted to say anything for fear of decreasing the number of Christmas presents he might get.
Taking my parental duties as seriously as I do, I took him to one side and explained the truth…
After Return of the Jedi, Darth Vader fell upon hard-times. There weren’t many film roles about for Dark Lords of the Sith. Eventually he decided upon a change of career and bought the round off Father Christmas, who was retiring.
Obviously, Darth Vader has a rather more direct approach to naughty children and if my son didn’t behave himself, not only would he not get any presents but he might get something cut off.
It is for this reason that Simon has the Darth Vader theme as the ringtone on his phone for when I call.

All of which has at best, a tenuous link to the theme of this post ( but I thought it was time to get into the festive spirit).

Following on from last week’s introduction to PL/SQL, some people have asked about using PL/SQL a web application (without all that mucky APEX stuff). In order to start working up some examples of this, I thought it would be a good idea to use PHP as a front-end. Yes – Oracle’s version of a LAMP system – Oracle, PHP, Apache, Linux (OPAL). After all, how hard could it be ?

Starting Point

My starting point is a system running Oracle Express Edition on Ubuntu 9.10 desktop. What I need to do now is :

  • Install Apache
  • Install PHP
  • Test that their both working
  • Install OCI8 so that I can talk to the Oracle database
  • Check it all works as expected

Incidentally, if you need to install Oracle XE, you can find instructions here.

At this point I should say that what follows is the result of trial ( and quite a few errors). Whilst it resulted in success for me, it may not be the quickest way to achieve this goal.
The other point to note is that, I’m aware that there are some brave souls out there that have taken the plunge into both Linux and Oracle simultaneously ( hello Wayne), so I’ve tried to minimise the amount of work required at the $ prompt (though there’s still a fair bit) by using Synaptic Package Manager to install the packages. I haven’t tested, but I can see no reason why you couldn’t install the package mentioned here simply by using apt-get install on the command line if you’re that way inclined.
Also, I’ve taken a safety first approach of ensuring that both the database and the web server are shutdown when installing packages.

Installing Apache

First up, everyones’ favourite web server…

On the desktop, select the System Menu, Administration, Synaptic Package Manager
Enter the administrator password when prompted.

You should see a screen that looks something like this

Synaptic Package Manager

In the Quick Search bar at the top, enter apache.
Mark the following packages for installation ( just click the box to the left of the package name and select Mark for Installation from the drop-down menu that appears) :

apache2
libapache2-mod-php5

When you’re done, click the Apply button on the top menu.
Once Synaptic has completed the installation, we should be able to test.

At this point, it’s worth mentioning the init script that gets created as part of the Apache installation in /etc/init.d
The script – apache2 – is used to administer the webserver and we’ll be using three of the command-line options here – start, stop and status.
We want to check whether Apache is up and running so…
The script – apache2 – is used to administer the webserver and we’ll be using three of the command-line options here – start, stop and status.

We want to check whether Apache is up and running so…

$ sudo /etc/init.d/apache2 status 
[sudo] password for mikes: 
 * Apache is running (pid 1425). 
$ 

If Apache isn’t up and running, nothing will be returned by this command.
To start Apache…

$ sudo /etc/init.d/apache2 start 
 * Starting web server apache2                                                  apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName 
                                                                         [ OK ] 
$ 

You’ll notice that, in my case, I got a message about the ServerName. For some reason, Apache has decided not to use localhost as the server address, but instead opted for the other default entry in the /etc/hosts file – 127.0.1.1.

Therefore, when testing to see if Apache is working as expected, I used the url http://127.0.1.1 rather than the more traditional http://localhost

This is the next step, open a web browser and type in the appropriate URL ( one of the above).
You should get this …

Apache Test Page

At this point, I shut down Apache and in preparation for the PHP installation :

$ sudo /etc/init.d/apache2 stop 
 * Stopping web server apache2                                                  apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName 
 ... waiting                                                             [ OK ] 
$

PHP

Back to Synaptic Package Manager. This time, enter php in the Quick Search bar.
The packages we want to select is :

php5

The next step is to write a php file and deploy it to Apache so we can check that it’s working.
We need to save the file in the directory Apache uses to store it’s web pages :
The file in question is the php equivalent of the “hello world” program. The phpinfo function simply reports the php configuration on the web server :

<?php 
phpinfo();
?>

Once we’ve saved this as phpinfo.php, we need to copy it to the appropriate directory ( /var/www) :

$ sudo cp phpinfo.php /var/www/.
$

Now we need to start Apache again :

$ sudo /etc/init.d/apache2 start 
 * Starting web server apache2                                                  apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName 
                                                                         [ OK ] 
$ 

Fingers crossed…back to the browser and type in http://127.0.1.1/phpinfo.php
Hopefully you should see something like :

Top of the phpinfo page

If all is working as planned we’re now going to stop Apache ( again) and Oracle XE.
This is probably not strictly necessary, but I wasn’t feeling particularly adventurous at the time so I played it safe.
Incidentally, to stop Oracle XE, you just need to go to the Applications Menu on the desktop and select Oracle Database 10g Express Edition, Stop Database.
The apache stop command is the same as the one we used before the PHP installation.

OCI8

After wading through much confusing information, it seems that OCI8 is probably the best choice for interfacing with the database. Almost every guide I’ve looked at states that you need to have the Oracle Instant Client installed to set this up. However, as we’ve already got Oracle XE, this should not be necessary as we have all the required environment variables set up. The key thing here is that the OCI8 installation needs to reference a file called libclntsh.so
If we do a quick search, we can find this in the lib directory under $ORACLE_HOME :

$ sudo find $ORACLE_HOME -name libclntsh.so 
[sudo] password for mikes: 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libclntsh.so 
$ 

OK, looks like we’re good to go.
Open up Synaptic again. Enter php in the Quick Search bar.
This time, we want to install :

php5-dev
php-pear

Once Synaptic has finished, we can get on with the OCI8 install.
This uses PECL ( PHP Extensions Community Library). This seems to do for PHP extensions what apt does for packages.
Before we run this, we need to make a note of the $ORACLE_HOME as we’ll be prompted for it during the install :

$ echo $ORACLE_HOME 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 
$ 

Now run the pecl utility :

$ sudo pecl install oci8

When prompted, just enter the ORACLE_HOME path.
The install will then throw up lots of text ending with something like :

Build process completed successfully 
Installing '/usr/lib/php5/20060613+lfs/oci8.so' 
install ok: channel://pecl.php.net/oci8-1.4.4 
configuration option "php_ini" is not set to php.ini location 
You should add "extension=oci8.so" to php.ini

As a result of the last set of packages we installed, we have a php.ini at two different locations, one for the php command-line interface and one for php on Apache.

$ sudo gedit /etc/php5/apache2/php/php.ini

I added the required line in the Extensions section of the file ( line 620 in my case). The line is :

extension=oci8.so

Now make the same change for the command-line php.ini :

$ sudo gedit /etc/php5/cli/php.ini

Now we need to tell apache about the Oracle database. Specifically we need to add the $ORACLE_HOME and $LD_LIBRARY_PATH to the environment variables file that Apache reads on startup ( /etc/apache2/envvars).
First get the values for these environment variables :

$ echo $ORACLE_HOME 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 
$ echo $LD_LIBRARY_PATH 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib: 
$ 

NOTE – the : at the end of the LD_LIBRARY_PATH is supposed to be there. It’s not a typo. I mentioned this because I thought it was and initially left it off of the entry in envvars with, as they say, hilarious results.

$ sudo gedit /etc/apache2/envvars

I added the two environment variables at the bottom of the envvars file which now looks like this :

# envvars - default environment variables for apache2ctl

# Since there is no sane way to get the parsed apache2 config in scripts, some
# settings are defined via environment variables and then used in apache2ctl,
# /etc/init.d/apache2, /etc/logrotate.d/apache2, etc.
export APACHE_RUN_USER=www-data
export APACHE_RUN_GROUP=www-data
export APACHE_PID_FILE=/var/run/apache2.pid

## The locale used by some modules like mod_dav
export LANG=C
## Uncomment the following line to use the system default locale instead:
#. /etc/default/locale

export LANG
#
# Added for OCI8 PHP setup
#
export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib:
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

Whilst we’re on the command line, we may as well start apache up :

$ sudo /etc/init.d/apache2 start 
 * Starting web server apache2                                                  apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName 
                                                                         [ OK ] 
$ 

While we’re at it, we can now start the database up again ( Applications, Oracle Database 10g Express Editions, Start Database).

At this point, it’s a good idea to check phpinfo.php again ( http://127.0.1.1/phpinfo.php). If it’s all going to plan, we should see the following section in the output :

phpinfo now has an OCI section

What we need now is a bit of PHP to connect to and retrieve some data from Oracle. First off, make sure that the HR account in the database is unlocked.
Go to the Database Home Page ( Applications, Oracle Database 10g Express Edition, Go to Database Home Page).
Connect as SYSTEM
Click the Administration icon and select Database Users / Manage Users from the drop-down.
Click on the HR user, set the status to Unlocked, and click the Alter User button :

Unlock the HR user

Now for the test script. Save this as oci8test.php :

<?php
    /* Connect to the database as the hr user
       oci_connect( user, password, database)
    */
    $conn = oci_connect("hr", "hr", "127.0.1.1/XE");
    /* Parse a select statement
     oci_parse( connection object, statement)
    */
    $stid = oci_parse( $conn, "select last_name, salary from employees order by 1");
    // Run the statement
    oci_execute( $stid);
    /* Get the complete result set back. $nrows is the count of the 
       number of rows fetched.
      $results is an array to hold the result set
    */
    $nrows = oci_fetch_all( $stid, $results);
    // Make it all pretty in an HTML table
    echo "<html><head><title>Oracle PHP Test</title></head><body>";
    echo "<center><h2>Oracle PHP Test</h2>";
    echo "<table border=1 cellspacing='0' width='50%'>\n<tr>\n";
    echo "<td><b>Name</b></td>\n<td><b>Salary</b></td>\n</tr>\n";
    for( $i = 0; $i < $nrows; $i++)
    {
        echo "<tr>\n";
        echo "<td>".$results["LAST_NAME"][$i]."</td>";
        echo "<td>" .number_format($results["SALARY"][$i], 2)."</td>";
        echo "</tr>\n";
    }
    echo "<tr><td colspan='2'>Number of Rows: $nrows</td></tr></table>";
    echo "<br><em>If you see data, then it works!</em><br></center></body></html>";
?>

Now move it to the appropriate directory :

$ sudo cp oci8test.php /var/www/.

When we point the browser at the appropriate URL ( http://127.0.1.1/oci8test.php) we should get :

Not pretty, but it proves it works

And we’re done.

As a postscript to all of this…
It was Christmas Eve. Wheezing up the stairs to fulfill my role of Darth Vader ( nee Santa Claus), I was surprised to find an envelope for me on my son’s bedroom door. The contents were two cinema tickets and a short note :
Dear Mr Vader,
These are for you and Mrs Vader.
Please leave my mini-yule log alone.

Sigh. They grow up so quickly these days.

About these ads

One thought on “Configuring OPAL on Ubuntu Desktop – without the Oracle Instant Client

  1. Pingback: Install Apache 2.2, MySQL 5, PHP 5, phpMyAdmin for Linux Ubuntu 8.10 | PHP5 Web Hosting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s