Zurück

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:
  1. Download the files and unpack them.
  2. cd <unpack-directory>
  3. ./configure
  4. make
  5. 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-deflate

Edit 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.

  1. Download PHP-4.3.10 and unpack it.
     
  2. Download the Basic and the SDK Instant Client packages from the Instant Client page on OTN.
     
  3. 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*
     
  4. 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.
     
  5. Rebuild the configure script in the top-level PHP directory.

    cd php-4.3.10
    rm -rf config.cache
    ./buildconf --force

     
  6. 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
     
  7. 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.