Connect to oracle database from php with oci

If you need to query oracle database from php – you will need to setup oci8 driver.
In this post I will talk about what it takes to set it up on CentOS 6.4 server running apache 2.2 with enabled SELinux.

Section 1. Install Oracle Instant Client.

Head to http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html and chose your Linux distribution type (64 bit or x86)

Since I’m on 64 bit after I click on the link for x64 I’m presented with multiple choices here

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

I will be downloading and installing following packages:

  • oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm
  • oracle-instantclient12.1-devel-12.1.0.1.0-1.x86_64.rpm
  • oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm

After installation I will need to setup several environmental variables.

I add following lines at the end of my /etc/bashrc

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export TNS_ADMIN=$ORACLE_HOME/admin
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
export PATH=$PATH:$ORACLE_HOME

I will also need to create directory for $TNS_ADMIN

mkdir /usr/lib/oracle/12.1/client64/admin

And will need to place tnsnames.ora under this directory which lists my database connections.
Actually this step is optional if you will be using so called Easy Connect string
So below is my tnsnames.ora file

#
# TNSNAMES.ORA
#

mydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
    )
    (SDU=5844)
    (CONNECT_DATA =
      (SERVICE_NAME = mydb)
    )
  )

Section 2. Install and configure oci8 driver

Logout and login as root to propagate new environment variables that we created in the previous step.
You should be able to see them now

env|grep -i oracle
LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
TNS_ADMIN=/usr/lib/oracle/12.1/client64/admin
PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/12.1/client64:/usr/lib/oracle/12.1/client64:/root/bin
ORACLE_HOME=/usr/lib/oracle/12.1/client64

Now let’s install some php packages

yum install php-pear php-devel
pear download pecl/oci8

At this point you should have in your directory file oci8-2.0.6.tgz
where 2.0.6 is a version number and it can be different from what I have now at the time of writing this.
Let’s uncompress it and install

tar -xzvf oci8-2.0.6.tgz
cd oci8-2.0.6
phpize
./configure --with-oci8=shared,instantclient,/usr/lib/oracle/12.1/client64/lib
make
make install

At the end of /etc/php.ini file add these 2 lines

[OCI8]
extension=oci8.so

And restart apache

service httpd restart

Section 3. Test and reconfigure SELinux

Let’s test it.
Under /var/www/html directory create file index.php with the sample connection to our database.
Below sample code is taken from http://www.php.net/manual/en/oci8.examples.php
Notice how on line 3 I’m using Easy Connect string, so if you are using Easy Connect string you don’t need to have tnsnames.ora file.


<!--?php <br ?-->
$conn = oci_connect('hr', 'welcome', '192.168.0.100/mydb');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Prepare the statement
$stid = oci_parse($conn, 'SELECT * FROM departments');
if (!$stid) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
    $e = oci_error($stid);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Fetch the results of the query
print "</pre>
\n";while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) { print "\n"; foreach ($row as $item) {print "\n"; } print "\n";}print "
<table border="1">
<tbody>
<tr>
<td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") . "</td>
</tr>
</tbody>
</table>
<pre>
\n";

oci_free_statement($stid);
oci_close($conn);

?>

When I try to test it – off course nothing is displayed on the page and I have error in the /var/log/httpd/error_log saying

[Fri Dec 20 09:32:35 2013] [error] [client 10.32.118.145] PHP Warning:  oci_connect(): ORA-12546: TNS:permission denied in /var/www/html/index.php on line 3
[Fri Dec 20 09:32:35 2013] [error] [client 10.32.118.145] PHP Fatal error:  ORA-12546: TNS:permission denied in /var/www/html/index.php on line 6

Further examination of the log /var/log/audit/audit.log reveals these errors

type=AVC msg=audit(1387556756.066:17251): avc:  denied  { name_connect } for  pid=30906 comm="httpd" dest=1521 scontext=unconfined_u:system_r:httpd_t:s0 tcontext=system_u:object_r:oracle_port_t:s0 tclass=tcp_socket

OK, so let’s examine what we can do about it.

getsebool -a|grep -i http|grep -i net
httpd_can_network_connect --> off
httpd_can_network_connect_cobbler --> off
httpd_can_network_connect_db --> off
httpd_can_network_memcache --> off
httpd_can_network_relay --> off

Let’s try to set httpd_can_network_connect to on

setsebool -P httpd_can_network_connect on

And reload our page.
It works!!!

Section 4. References

This entry was posted in Linux, Oracle, php and tagged , , . Bookmark the permalink.

4 Responses to Connect to oracle database from php with oci

  1. victor_garduno says:

    Man!!! you are awesome!!!! THANK YOU VERY MUCH!!!

  2. Hrios says:

    nice post

  3. Rohit Aggarwal says:

    Great post… thanks.. saved me a lot of time !

  4. cahyo says:

    Mann!! You are awsome. Spot on for what I’m looking for. Thank you very much.

Leave a Reply

Your email address will not be published. Required fields are marked *