Connecting to Oracle10g from PHP using OCI-8 (Solaris-9)
This Article is based on Solaris, for Linux 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
Solaris 9 (64Bit) Our Platform for Development and Production 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 Sun Solaris 64-Bit Oracle Database 10g Release 1 (10.1.0.3) for Solaris Operating System (SPARC) (64-bit)
The Installation of Oracle 10.1.0.3 is not shown in this article.Instant Client Package - Basic (32-Bit) Needed to run OCI, OCCI, and JDBC-OCI applications. Download the 32-Bit Package. Instant Client Package - SDK (32-Bit) Additional header files and an example makefile for developing Oracle applications with Instant Client. Download the 32-Bit Package. 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
Additional Tools needed to build PHP-OCI8
Software
Notes
patch-2.5.4.tar.gz patch - apply a diff file to an original m4-1.4.2.tar.gz m4 - macro processor Install Instructions:
- Download the files and unpack them.
- cd <unpack-directory>
- ./configure
- make
- make install
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. Note that we use the 32-Bit Libraries for the Basic and the SDK Instant Client packages, because PHP is still a 32-Bit application.
- Download PHP-4.3.10 and unpack it.
- Download the Basic and the SDK Instant Client packages from the Instant Client page on OTN.
- Install the ZIP-Files as the root user.
unzip instantclient-basic-solaris32-10.1.0.3.zip
cd ./instantclient10_1
mkdir -p /usr/lib/oracle/10.1.0.3/client/lib
cp * /usr/lib/oracle/10.1.0.3/client/lib
cd ..
unzip instantclient-sdk-solaris32-10.1.0.3.zip
cd ./instantclient10_1/sdk/include
mkdir -p /usr/include/oracle/10.1.0.3/client
cp * /usr/include/oracle/10.1.0.3/client
cd /usr/lib/oracle/10.1.0.3/client/lib
ln -s libclntsh.so.10.1 libclntsh.so
ln -s libocci.so.10.1 libocci.so
ls -l
-r--r--r-- 1 root other 1461081 2005-01-21 12:59 classes12.jar
lrwxrwxrwx 1 root other 17 2005-01-21 13:00 libclntsh.so -> libclntsh.so.10.1*
-r-xr-xr-x 1 root other 19882808 2005-01-21 12:59 libclntsh.so.10.1*
-rwxr-xr-x 1 root other 19505384 2005-01-21 12:59 libclntsh.so.9.1.new*
-r-xr-xr-x 1 root other 4607528 2005-01-21 12:59 libnnz10.so*
lrwxrwxrwx 1 root other 15 2005-01-21 13:00 libocci.so -> libocci.so.10.1*
-r-xr-xr-x 1 root other 1320136 2005-01-21 12:59 libocci.so.10.1*
-rwxr-xr-x 1 root other 66161824 2005-01-21 12:59 libociei.so*
-r-xr-xr-x 1 root other 111056 2005-01-21 12:59 libocijdbc10.so*
-r--r--r-- 1 root other 1397543 2005-01-21 12:59 ojdbc14.jar
cd /usr/include/oracle/10.1.0.3/client
ls -l
-r--r--r-- 1 root other 32137 2005-01-21 10:13 nzerror.h
-r--r--r-- 1 root other 84539 2005-01-21 10:13 nzt.h
-r--r--r-- 1 root other 2115 2005-01-21 10:13 occi.h
-r--r--r-- 1 root other 11587 2005-01-21 10:13 occiAQ.h
-r--r--r-- 1 root other 36750 2005-01-21 10:13 occiCommon.h
-r--r--r-- 1 root other 70671 2005-01-21 10:13 occiControl.h
-r--r--r-- 1 root other 33996 2005-01-21 10:13 occiData.h
-r--r--r-- 1 root other 29156 2005-01-21 10:13 occiObjects.h
-r--r--r-- 1 root other 132242 2005-01-21 10:13 oci.h
-r-xr-xr-x 1 root other 7155 2005-01-21 10:13 oci1.h*
-r--r--r-- 1 root other 10228 2005-01-21 10:13 oci8dp.h
-r--r--r-- 1 root other 404315 2005-01-21 10:13 ociap.h
-r-xr-xr-x 1 root other 6055 2005-01-21 10:13 ociapr.h*
-r--r--r-- 1 root other 43225 2005-01-21 10:13 ocidef.h
-r-xr-xr-x 1 root other 4014 2005-01-21 10:13 ocidem.h*
-r--r--r-- 1 root other 11331 2005-01-21 10:13 ocidfn.h
-r--r--r-- 1 root other 8954 2005-01-21 10:13 ociextp.h
-r-xr-xr-x 1 root other 6638 2005-01-21 10:13 ocikpr.h*
-r--r--r-- 1 root other 4464 2005-01-21 10:13 ocixmldb.h
-r--r--r-- 1 root other 19099 2005-01-21 10:13 odci.h
-r--r--r-- 1 root other 6871 2005-01-21 10:13 oratypes.h
-r--r--r-- 1 root other 102726 2005-01-21 10:13 ori.h
-r--r--r-- 1 root other 15031 2005-01-21 10:13 orid.h
-r--r--r-- 1 root other 157674 2005-01-21 10:13 orl.h
-r--r--r-- 1 root other 42626 2005-01-21 10:13 oro.h
-r--r--r-- 1 root other 116591 2005-01-21 10:13 ort.h
-r-xr-xr-x 1 root other 9520 2005-01-21 10:13 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
patching file config.m4
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 12106444 2005-01-21 11:13 libphp4.so
- Set Oracle Environment and restart Apache.
Set LD_LIBRARY_PATH to /usr/lib/oracle/10.1.0.3/client/lib. 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
#!/bin/sh
# Akadia AG, Arvwenweg 4, CH-3604 Thun
# ----------------------------------------------------------------------
# File: apache2
#
# Autor: Martin Zahn / 22.02.2002
#
# Purpose: Startup Apache2
# ----------------------------------------------------------------------
# Setup ORACLE environment used for PHP4 Access
ORACLE_HOME=/opt/oracle/product/10.1.0; export ORACLE_HOME
ORACLE_SID=QUO3; export ORACLE_SID
TNS_ADMIN=/home/oracle/config/10.1.0 export TNS_ADMIN
ORA_NLS10=${ORACLE_HOME}/nls/data; export ORA_NLS10
CLASSPATH=${CLASSPATH}:${ORACLE_HOME}/jdbc/lib/classes12.zip; export CLASSPATH
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1; export NLS_LANG
LD_LIBRARY_PATH=/usr/lib/oracle/10.1.0.3/client/lib; export LD_LIBRARY_PATH
case "$1" in
'start')
echo "Starting Apache2"
/usr/local/apache/bin/apachectl -k start -D SSL -D PHP4
;;
'stop')
echo "Stopping Apache2"
/usr/local/apache/bin/apachectl stop
;;
esac
./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.