Accessing Oracle from PHP

This document describes how to access an Oracle database from PHP.

Install

  • php5-dev
  • php-pear
  • zip
  • unzip
  • libaio1

Download the instant client basic and sdk (2 files) from www.oracle.com . Note that the client version is at most 2 major releases downwards compatible with the server version, so you cannot use client version 11 on a server with version 8. Create a new directory /opt/oracle and unzip the 2 files which you have downloaded.

Then run these commands:

ln -s instantclient_10_2 instantclient
cd instantclient
pwd > /etc/ld.so.conf.d/oracle.conf
ldconfig
ln -s libclntsh.so.10.1 libclntsh.so
ln -s libocci.so.10.1 libocci.so
pecl install oci8

The version numbers may have to be adjusted according to your needs, but leave oci8 as-is.

The last command will ask a question. Answer with:

1
shared,instantclient,/opt/oracle/instantclient
<enter>

Then run these commands:

echo "extension=oci8.so" > /etc/php5/apache2/conf.d/oracle.ini
echo "extension=oci8.so" > /etc/php5/cli/conf.d/oracle.ini
/etc/init.d/apache2 restart

To test the connection, I wrote the following code:

<?php

function do_oracle_query($query)
{
   $db = "(DESCRIPTION=
             (ADDRESS_LIST=
                (ADDRESS=
                   (PROTOCOL=TCP)
                   (HOST = <oracle_hostname>)
                   (PORT = <oracle_portnumber>)
                )
             )
             (CONNECT_DATA =
                (SID = <oracle_databasename>)
             )
          )";
   $db_conn = oci_connect("<oracle_username>", "<oracle_password>", $db);
   if (!$db_conn)
   {
      echo "Cannot connect to the database...\n";
      print_r(oci_error());
      return 0;
   }
   $stmt = oci_parse($db_conn, $query);
   oci_execute($stmt);
   $nrows = oci_fetch_all($stmt, $results);
   oci_free_statement($stmt);
   oci_close($db_conn);
   return $results;
}

function test_connection()
{
   $results = do_oracle_query("select SYSDATE from dual");
   if ($results === 0)
      return;
   $n = Count($results['SYSDATE']);
   if ($n == 0)
   {
      print "No results";
      return;
   }
   print $results['SYSDATE'][0];
}

test_connection();

?>

Note that <oracle_hostname>, <oracle_portnumber>, <oracle_databasename>, <oracle_username> and <oracle_password> need to be adjusted accordingly. The table "dual" used here is a fake table you can specify in Oracle SQL queries when you do not want to use any table for your query.