Centos to Oracle Query

At work our MIS system (Management Information System/Student data) is an Oracle Database Server. Our Moodle (VLE) is the only interface that we have with the students. The course information, enrollment information, student data and attendance all reside on MIS but need to be displayed on Moodle. For a while I set up a system where MIS would do a cron job to create some csv files every night and then the Moodle cron job would process these (a process I had to write from scratch). I decided recently to change this to a direct database query connection (a political change in the organisation also helped). I realised that this also gives us the chance to write to the MIS system from Moodle which is a great place to capture student data. I already capture the students prior attainment (GCSE's and Alevels) which I then use to automatically create target grades. This can now be written to MIS.

Setting up Oracle Access on Centos

In order for Moodle(Centos) to access the MIS database PHP will need to:

·         Connect to the server

·         Connect to the oracle sql server

·         Query the correct database

·         Output the results

This will require PHP to know and understand the Oracle connections and therefore the following changes to the server need to take place:

·         Install the OCI8 extension for PHP

·         Install the Oracle Client Libraries/oracle instant client

I found yum repositories hard to come by for Oracle and so I had to download the correct rpm's from the oracle website: http://www.oracle.com/technetwork/topics/linuxsoft-082809.html . I downloaded:

• Basic
• Devel 

I also downloaded the correct versions that matched the version of oracle we had running, in our case, 10.2... 


On the centos box in a terminal I changed to the directory I had downloaded them to and ran (for each):

Rpm –ivh package_name

Where package name is one of the rpm’s and ‘–ivh’ is the install command.

Once these are run the packages are installed as;

Oracle Instant Client library in /usr/lib/oracle/<version>/client/lib

Oracle Instant Client SDK header files in /usr/include/oracle/<version>/client.

This above folder layout is a problem for PHP OCI8 extension installation because the “./configure –with-oci8=shared,instantclient,/path/to/instantclient/lib” will look for the Oracle Instant Client SDK header field in the same library folder i.e. /usr/lib/oracle/<version>/client folder in our case and since the location of SDK header files are different it will raise an error.

To solve this issue, we will create a symbolic link for Oracle Instant Client SDK library path in the Oracle Instant Client library path like

ln -s /usr/include/oracle/<version>/client /usr/lib/oracle/<version>/client/lib/include

This creates the symbolic link to the first part where the shortcut is the second part.

ln -s [TARGET DIRECTORY OR FILE] ./[SHORTCUT]

Once done, if you view /usr/lib/oracle/<version>/client/lib/include you will see the ‘include’ symbolic link.

Note:

The above step of creating the symbolic link may not work. When executing the below steps an error may occur due to it not finding the correct libraries. In this case I copied all files inside /usr/include/oracle/<version>/client and put them in /usr/lib/oracle/<version>/client/lib.

Installing the PHP OCI8 extension

The following uses;

·         http://www.php.net/manual/en/oci8.installation.php

·         http://www.php.net/manual/en/install.pecl.php

Install the php-pear package. (http://pear.php.net/manual/en/installation.getting.php)

$ wget http://pear.php.net/go-pear.phar

$ php go-pear.phar

Choose default settings by just pressing ‘enter’

$ pear (to see if it works; a list of commands should come up).

Auto install the extension:

$ pecl install oci8

Give it the Oracle home client. -> ‘instantclient, /usr/lib/oracle/<version>/client/lib’ when asked.

I got the error of SDK files not found so I copied all files inside /usr/include/oracle/<version>/client and put them in /use/lib/oracle/<version>/client/lib as states in the note above

Add the extension to the php.ini

Make sure that the extension .so file is in the same directory that php will look for its modules.

In the php .ini file (perhaps in /etc/php.ini) there is a line that reads:

extension_dir = “/usr/lib.php/modules”

The output of the pecl install oci8 will tell you where is stored its oci8.so

In the php.ini, within the ‘Dynamic Extensions’ section add:

extension=oci8.so

Restart Apache and Test

Restart the apache service.

Then navigate to:

·         phpInfo.php

·         Does it have the oracle module installed (oci8 extension)?

To test the connection from php itself I created a very simple script that outputted the results from a table:

$conn = oci_connect(‘USER’, 'PASSWORD', 'IPADDRESS:PORT/SERVICE');

if($conn)

{

                echo "SUCCESS";

                $s = oci_parse($conn, "select * from TABLE/VIEW");

                oci_execute($s);

                while($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS))

                {             

                                print_object($row);

                }

}

This worked and I now have access to an Oracle Database from my PHP server (Moodle.)