Connecting to Oracle10g from PHP using OCI-8 (Linux)
This Article is based on Linux, for Solaris click here.
Overview
PHP (recursive acronym for "PHP: Hypertext Preprocessor") is a widely-used Open Source general-purpose scripting language that is especially suited for
Web development and can be embedded into HTML.
With PHP you are not limited to output HTML. PHP's abilities includes outputting images and PDF files generated on the fly. You can also output easily any text, such as XHTML and any other XML file.
One of the strongest and most significant features in PHP is its support for a wide range of databases. Writing a database-enabled web page is incredibly simple. In this article we extend and test the material found in the original article.
Software Components tested in this Article
Software
Notes
GENTOO Linux 2004.1 Our Platform for Development Apache 2.0.52 The PHP community still recommends Apache 1.3, specially for security reasons. PHP-4.3.10 The latest PHP Release is 5.0.3. Oracle 10.1.0.3 for Linux x86 Oracle Database 10g Release 1 (10.1.0.3) for Linux x86.
The Installation of Oracle 10.1.0.3 is not shown in this article.Instant Client Package - Basic Needed to run OCI, OCCI, and JDBC-OCI applications Instant Client Package - SDK Additional header files and an example makefile for developing Oracle applications with Instant Client. OCI8 Patch for PHP from Oracle If you are using PHP 4.3.10 you can save the patch to a file, e.g. php_oci8ic_buildpatch
Installing Apache-2.0.52
The installation and configuration of Apache is not explained in detail here, see original documentation. Download the sources, unpack and install them as follows:
./configure --prefix=/usr/local/apache \
--enable-mods-shared=most \
--enable-ssl=shared \
--enable-mime-magic \
--enable-cern-meta \
--enable-proxy \
--enable-proxy-connect \
--enable-proxy-ftp \
--enable-proxy-http \
--enable-usertrack \
--enable-unique-id \
--enable-deflateEdit the apache configuation file httpd.conf and add:
AddType application/x-httpd-php .php .phtml
AddType application/x-httpd-php-source .phps
Installing PHP-4.3.10 with the OCI8 Extension
To add Oracle connectivity on Linux, PHP needs to be recompiled with the OCI8 Patch.
- Download PHP-4.3.10 and unpack it.
- Download the Basic and the SDK Instant Client packages from the Instant Client page on OTN.
Collectively the two RPMs are about 30MB in size.
- Install rpm utility (GENTOO Linux has usually no rpm installed). Probably you will encounter blocking problems.
emerge rpm
Calculating dependencies ...done!
!!! Error: the dev-libs/libelf package conflicts with another package.
!!! both can't be installed on the same system together.
!!! Please use 'emerge --pretend' to determine blockers.
Okay, here is the problem: we can't emerge rpm because there is a conflict with the libelf (installed) package. Let's try to resolve the conflict.
emerge -p rpm
Calculating dependencies ...done!
[blocks B ] dev-libs/libelf (from pkg dev-libs/elfutils-0.84)
[ebuild N ] dev-lang/python-2.2.3-r5
[ebuild N ] dev-libs/elfutils-0.84
[ebuild N ] app-arch/rpm-4.2_pre069
As you can see libelf is blocking rpm as [blocks B ] points.
From emerge man pages:
Blockers are defined when two packages will clobber each others files, or otherwise
cause some form of breakage in your system. However, blockers usually do not need
to be simultaneously emerged because they usually provide the same functionality.
So to resolve the conflict proceed as follows:
emerge unmerge libelf
And there should be no blocking package now...
emerge -p rpm
Calculating dependencies ...done!
[ebuild N ] dev-lang/python-2.2.3-r5
[ebuild N ] dev-libs/elfutils-0.84
[ebuild N ] app-arch/rpm-4.2_pre069
Now proceed as usual...
emerge xfree
- Install the RPMs as the root user. On GENTOO Linux, rpm is usually not there and can be in installed as follows.
rpm -Uvh oracle-instantclient-basic-10.1.0.3-1.i386.rpm
rpm -Uvh oracle-instantclient-devel-10.1.0.3-1.i386.rpm
The first RPM puts the Oracle libraries in /usr/lib/oracle/10.1.0.3/client/lib and the second creates headers in /usr/include/oracle/10.1.0.3/client
cd /usr/lib/oracle/10.1.0.3/client/lib
ls -l
-r--r--r-- 1 root root 1461081 Dec 2 21:03 classes12.jar
lrwxrwxrwx 1 root root 17 Jan 17 11:35 libclntsh.so -> libclntsh.so.10.1*
-rwxr-xr-x 1 root root 13495923 Dec 2 21:03 libclntsh.so.10.1*
-r-xr-xr-x 1 root root 2121849 Dec 2 21:03 libnnz10.so*
lrwxrwxrwx 1 root root 15 Jan 17 11:35 libocci.so -> libocci.so.10.1*
-rwxr-xr-x 1 root root 913575 Dec 2 21:03 libocci.so.10.1*
-rwxr-xr-x 1 root root 66159152 Dec 2 21:03 libociei.so*
-rwxr-xr-x 1 root root 96517 Dec 2 21:03 libocijdbc10.so*
-r--r--r-- 1 root root 1397543 Dec 2 21:03 ojdbc14.jar
cd /usr/include/oracle/10.1.0.3/client
ls -l
-r--r--r-- 1 root root 32137 Dec 2 21:03 nzerror.h
-r--r--r-- 1 root root 84539 Dec 2 21:03 nzt.h
-r--r--r-- 1 root root 11587 Dec 2 21:03 occiAQ.h
-r--r--r-- 1 root root 36750 Dec 2 21:03 occiCommon.h
-r--r--r-- 1 root root 70671 Dec 2 21:03 occiControl.h
-r--r--r-- 1 root root 33996 Dec 2 21:03 occiData.h
-r--r--r-- 1 root root 2115 Dec 2 21:03 occi.h
-r--r--r-- 1 root root 29156 Dec 2 21:03 occiObjects.h
-r-xr-xr-x 1 root root 7155 Dec 2 21:03 oci1.h*
-r--r--r-- 1 root root 10228 Dec 2 21:03 oci8dp.h
-r--r--r-- 1 root root 404315 Dec 2 21:03 ociap.h
-r-xr-xr-x 1 root root 6055 Dec 2 21:03 ociapr.h*
-r--r--r-- 1 root root 43225 Dec 2 21:03 ocidef.h
-r-xr-xr-x 1 root root 4014 Dec 2 21:03 ocidem.h*
-r--r--r-- 1 root root 11331 Dec 2 21:03 ocidfn.h
-r--r--r-- 1 root root 8954 Dec 2 21:03 ociextp.h
-r--r--r-- 1 root root 132242 Dec 2 21:03 oci.h
-r-xr-xr-x 1 root root 6638 Dec 2 21:03 ocikpr.h*
-r--r--r-- 1 root root 4464 Dec 2 21:03 ocixmldb.h
-r--r--r-- 1 root root 19099 Dec 2 21:03 odci.h
-r--r--r-- 1 root root 6605 Dec 2 21:03 oratypes.h
-r--r--r-- 1 root root 15031 Dec 2 21:03 orid.h
-r--r--r-- 1 root root 102726 Dec 2 21:03 ori.h
-r--r--r-- 1 root root 157674 Dec 2 21:03 orl.h
-r--r--r-- 1 root root 42626 Dec 2 21:03 oro.h
-r--r--r-- 1 root root 116591 Dec 2 21:03 ort.h
-r-xr-xr-x 1 root root 9520 Dec 2 21:03 xa.h*
- Patch PHP-4.3.10
Apply the patch to PHP's ext/oci8/config.m4. The patch line numbers are based on PHP 4.3.9.
If you are using PHP 4.3.10 you can save the patch to a file, e.g. php_oci8ic_buildpatch, and install it using:
cd php-4.3.10/ext/oci8
patch -u config.m4 php_oci8ic_buildpatch
The patch creates a new PHP configuration parameter: --with-oci8-instant-client[=DIR]. On Linux, by default, it uses the latest version of the Instant Client installed from the RPMs. A directory to the Oracle libraries can be specified to use a different version. In either case, the correct SDK headers will automatically be used. The new parameter is mutally exclusive with the existing --with-oci8 parameter.
- Rebuild the configure script in the top-level PHP directory.
cd php-4.3.10
rm -rf config.cache
./buildconf --force
- Rebuild PHP
./configure \
--with-apxs2=/usr/local/apache/bin/apxs \
--with-oci8-instant-client \
--enable-sigchild
make
make install
cp php.ini-dist /usr/local/lib/php.ini
Note, that a new PHP4 module was created in /usr/local/apache/modules
cd /usr/local/apache/modules
ls -l libphp4.so
-rwxr-xr-x 1 root root 6113514 Jan 17 12:01 libphp4.so
- Set LD_LIBRARY_PATH to /usr/lib/oracle/10.1.0.3/client/lib and restart Apache.
If a tnsnames.ora file is used to define Oracle Net service names, set TNS_ADMIN to the directory containing the file.
It is important to set all Oracle environment variables before starting Apache. A script helps do that:
cd /etc/init.d
cat apache2
#!/sbin/runscript
ORACLE_HOME=/opt/oracle/product/10.1.0; export ORACLE_HOME
ORACLE_SID=AKI1; export ORACLE_SID
TNS_ADMIN=/home/oracle/config/10.1.0 export TNS_ADMIN
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1; export NLS_LANG
LD_LIBRARY_PATH=/usr/lib/oracle/10.1.0.3/client/lib; export LD_LIBRARY_PATH
opts="${opts} reload"
depend() {
need net
use dns logger netmount
after sshd
}
start() {
ebegin "Starting apache2"
[ -f /var/log/apache2/ssl_scache ] && rm /var/log/apache2/ssl_scache
env -i PATH=$PATH /sbin/start-stop-daemon --quiet \
--start --startas /usr/local/apache2/bin/httpd \
--pidfile /var/run/apache2.pid -- -k start ${APACHE2_OPTS}
eend $?
}
stop() {
ebegin "Stopping apache2"
/usr/local/apache/bin/apachectl stop >/dev/null
start-stop-daemon -o --quiet --stop --pidfile /var/run/apache2.pid
eend $?
}
reload() {
ebegin "Gracefully restarting apache2"
/usr/local/apache2/bin/httpd -t ${APACHE2_OPTS} &>/dev/null
if [ "$?" = "0" ]
then
if [ -f /var/run/apache2.pid ]
then
kill -USR1 $(</var/run/apache2.pid)
eend $?
else
svc_start
eend $?
fi
else
if [ -f /var/run/apache2.pid ]
then
svc_stop
fi
/usr/local/apache2/bin/httpd -t ${APACHE2_OPTS}
eend 1
fi
}
./apache2 stop
./apache2 start
To confirm the extension is configured, create a simple PHP script test.php where the web server can read it.
<?php
phpinfo();
?>
Load the script into a browser using a URL similar to "http://localhost/test.php".
The browser page should contain an "oci8" section saying "OCI8 Support enabled".
Connecting to Oracle
Oracle connection information is passed to OCILogon() to create a connection. Tools linked with Instant Client are always "remote" from any database server and an Oracle Net connection identifier must be used along with a username and password. The connection information is likely to be well known for established Oracle databases. With new systems the information is given by the Oracle installation program when the database is set up. The installer should have configured Oracle Net and created a service name.
Here is a simple PHP script: oci-test.php
<?php
// Set Oracle Environment
PutEnv("ORACLE_SID=AKI1");
PutEnv("ORACLE_HOME=/opt/oracle/product/10.1.0");
PutEnv("TNS_ADMIN=/home/oracle/config/10.1.0");
PutEnv("LD_LIBRARY_PATH=/usr/lib/oracle/10.1.0.3/client/lib");
// Try to connect to Oracle
if ($conn = OCILogon("system", "manager", "AKI1")) {
// Prepare the Query
$query = "SELECT osuser,machine,program FROM v\$session ORDER BY osuser, machine";
$stid = OCIParse($conn, $query);
// Fetch the Rows
if (OCIExecute($stid)) {
$numcols = OCINumCols($stid);
echo "<table border=\"1\" cellpadding=\"3\" bgcolor=\"#F0F0F0\">\n";
echo "<caption>".OCIServerVersion($conn)."</caption>\n";
echo "<tr><th>".OCIColumnname($stid,1)."<br>".OCIColumnType($stid,1)."</th>";
echo "<th>".OCIColumnname($stid,2)."<br>".OCIColumnType($stid,2)."</th>";
echo "<th>".OCIColumnname($stid,3)."<br>".OCIColumnType($stid,3)."</th></tr>\n";
while (OCIFetchInto($stid, &$result, OCI_ASSOC)) {
echo "<tr><td>";
echo $result['OSUSER'];
echo "</td><td>";
echo $result['MACHINE'];
echo "</td><td>";
echo $result['PROGRAM'];
echo "</td></tr>\n";
}
echo "</tr></table>\n";
echo "<p>\n";
}
OCIFreeStatement($stid);
OCILogoff($conn);
} else {
$err = OCIError();
echo "Oracle Connect Error " . $err[text];
exit;
}
?>Load the script into a browser using a URL similar to "http://localhost/oci-test.php".
Conclusion
PHP has built in support for OCI8 - but to make it work smoothly, PHP4 must be patched as shown in this article.