Remote access to Oracle

[adavis]

I'm trying to find out what is envolved in hitting an Oracle database which is on a NT box with PHP from a Unix box.

I know that PHP would have to be recompiled with the Oracle information. However, I understand that there is also an issue with Oracle drivers. I have been told that the Oracle drivers come on the Windows box, but not on the Unix and that a search has not found any free or reasonable priced drivers for the Unix box.

As far as firewalls go, the NT box is behind a firewall and the Unix box is not.

Our new company edict is "Doing more with less" so money is an issue.

Any comments, ideas, etc...

Thanks,
Alisa

[bastien]

Windows, Nix...blah blah blah ( why any one would run a serious oracle app on a windows box is beyond me, but i digress)

Ask your dba to set up networking on the nix boxes to talk to oralce on windows...sqlNET or a tnsNames file shoul do the trick (depending, of course on what version of Oracle you have)

[adavis]

Where these are run is out of my control...

But I too digress.

I called the dba of the Unix box, which sparked this question, and he has researched this some, but got pulled to another project before he found a solution.

I am pretty dense where all this is concerned, so you will need to be a bit more specific to get me to understand. I don't know what/where to find sqlNET or a tnsNames file. The Oracle db we are trying to hit is 8i. Also, which person do I need to be talking to, the Oracle dba or the sysadmin of the Unix box. Seems the Unix box is where the trouble is, but I don't know. That's why I'm here asking question. 😉

Thanks,
Alisa

[bastien]

The thing is there are a number of possible options when configuring Oracle to network. It also depends on what the webserver is running: straight scripting, Java, C++ programming

LDAP
Oracle Net
TNS Name
TCP/IP
Host Naming

The last is the easiest to set up.

The unix box will need
1. TCP/IP on it (probably there already)
2. Install Oracle Net Services
3. TCP/IP protocol adapter

Host name is resolved thru DNS or NIS network translations.

On the server (NT box)
Same as above
create a LISTENER to listen for and handle requests...

Your ORACLE dba should know how to do this, and/or have acess to the online Oracle docs to get this info needed to do this...

HTH

[XN85Turbo]

Well this is one task I have done already 🙂. You need to install OCI8 for php!

I used the following:

Server #1
Red Hat AS 4 (web server)

apache 1.3.33
php 4.3.9 (must use this version with my sample because of the oracle patch for it).

Server #2
Oracle 10g (running on Red Hat AS 3.4)

---

Oracle 10.1.0.3 Client Installation

Install Apache:

Step 1:

tar -zxvf apache_1.3.33.tar.gz
cd cd apache_1.3.33
./configure --enable-module=so --prefix=/usr/local/apache --with-port=8888
make
make install

Step 2:

vi /usr/local/apache/conf/httpd.conf

#insert these two lines under AddType application/x-tar .tgz

AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

#and under <IfModule mod_dir.c> add index.php

<IfModule mod_dir.c>
DirectoryIndex index.php index.htm index.html
</IfModule>

Install OCI8 PHP:

Step 1: Download and install the following rpm’s

rpm -ivh oracle-instantclient-basic-10.1.0.3-1.i386.rpm oracle-instantclient-devel-10.1.0.3-1.i386.rpm oracle-instantclient-sqlplus-10.1.0.3-1.i386.rpm

Step 2: Patching PHP 4.3.9 before compiling

tar -zxvf php-4.3.9.tar.tar

cd /usr/local/src/php-4.3.9

vi php_oci8ic_buildpatch

#add the following to the file top make the patch;

--- ext/oci8/config.m4.orig 2004-02-04 01:22:34.000000000 +1100
+++ ext/oci8/config.m4 2004-12-14 18:36:21.000000000 +1100
@@ -38,16 +38,46 @@
OCI8_VERSION=8.1
fi
else
- AC_MSG_ERROR(Oracle-OCI8 needed libraries not found)
+ AC_MSG_ERROR([Oracle (OCI8) required libraries not found])
fi
AC_MSG_RESULT($OCI8_VERSION)
])

-PHP_ARG_WITH(oci8, for Oracle-OCI8 support,
-[ --with-oci8[=DIR] Include Oracle-oci8 support. Default DIR is ORACLE_HOME.])
+AC_DEFUN(AC_OCI8IC_VERSION,[
+ AC_MSG_CHECKING([Oracle Instant Client version])
+ if test -f $PHP_OCI8_INSTANT_CLIENT/libociei.$SHLIB_SUFFIX_NAME; then
+ if test -f $PHP_OCI8_INSTANT_CLIENT/libclntsh.$SHLIB_SUFFIX_NAME.10.1; then
+ if test ! -f $PHP_OCI8_INSTANT_CLIENT/libclntsh.$SHLIB_SUFFIX_NAME; then
+ AC_MSG_ERROR([Link from $PHP_OCI8_INSTANT_CLIENT/libclntsh.$SHLIB_SUFFIX_NAME to libclntsh.$SHLIB_SUFFIX_NAME.10.1 not found])
+ fi
+ OCI8_VERSION=10.1
+ else
+ AC_MSG_ERROR([Oracle Instant Client library version not supported])
+ fi
+ else
+ AC_MSG_ERROR([Oracle Instant Client libraries not found])
+ fi
+ AC_MSG_RESULT([$OCI8_VERSION])
+])
+
+PHP_ARG_WITH(oci8, for Oracle (OCI8) support using ORACLE_HOME installation,
+[ --with-oci8[[=DIR]] Include Oracle (OCI8) support using an ORACLE_HOME
+ install. The default DIR is ORACLE_HOME])
+
+PHP_ARG_WITH(oci8-instant-client, for Oracle (OCI8) support using Oracle Instant Client,
+[ --with-oci8-instant-client[[=DIR]] Include Oracle (OCI8) support using
+ Oracle Instant Client. DIR is the directory with the
+ Instant Client libraries. On Linux it will default to
+ /usr/lib/oracle/<most_recent_version>/client/lib
+ Other platforms will need to have it explicitly specified.])

if test "$PHP_OCI8" != "no"; then
- AC_MSG_CHECKING([Oracle Install-Dir])
+
+ if test "$PHP_OCI8_INSTANT_CLIENT" != "no"; then
+ AC_MSG_ERROR([--with-oci8 and --with-oci8-instant-client are mutually exclusive])
+ fi
+
+ AC_MSG_CHECKING([Oracle Install Directory])
if test "$PHP_OCI8" = "yes"; then
OCI8_DIR=$ORACLE_HOME
else
@@ -116,14 +146,18 @@
-L$OCI8_DIR/lib $OCI8_SHARED_LIBADD
])
;;
+
10.1)
PHP_ADD_LIBRARY(clntsh, 1, OCI8_SHARED_LIBADD)
PHP_ADD_LIBPATH($OCI8_DIR/lib, OCI8_SHARED_LIBADD)
AC_DEFINE(HAVE_OCI8_ATTR_STATEMENT,1,[ ])
AC_DEFINE(HAVE_OCI_9_2,1,[ ])
+ AC_DEFINE(HAVE_OCI8_TEMP_LOB,1,[ ])
+ AC_DEFINE(PHP_OCI8_HAVE_COLLECTIONS,1,[ ])
;;
+
*)
- AC_MSG_ERROR(Unsupported Oracle version!)
+ AC_MSG_ERROR([Unsupported Oracle version])
;;
esac

@@ -145,10 +179,88 @@
-L$OCI8_DIR/lib $OCI8_SHARED_LIBADD
])

• dnl

• dnl Check if we have collections

• dnl

• PHP_CHECK_LIBRARY(clntsh, OCICollAssign,

• [

• AC_DEFINE(PHP_OCI8_HAVE_COLLECTIONS,1,[ ])

• ], [], [

• -L$OCI8_DIR/lib $OCI8_SHARED_LIBADD

• ])
  +
  +
  PHP_NEW_EXTENSION(oci8, oci8.c, $ext_shared)
  AC_DEFINE(HAVE_OCI8,1,[ ])

  PHP_SUBST_OLD(OCI8_SHARED_LIBADD)
  PHP_SUBST_OLD(OCI8_DIR)
  PHP_SUBST_OLD(OCI8_VERSION)
  +
  +elif test "$PHP_OCI8_INSTANT_CLIENT" != "no"; then
  +

• AC_MSG_CHECKING([Oracle Instant Client directory])

• if test "$PHP_OCI8_INSTANT_CLIENT" = "yes"; then
  +dnl Generally the Instant Client can be anywhere so the user must pass in the
  +dnl directory to the libraries. But on Linux we default to the most recent
  +dnl version in /usr/lib

• PHP_OCI8_INSTANT_CLIENT=ls -d /usr/lib/oracle/*/client/lib 2> /dev/null | tail -1

• if test -z "$PHP_OCI8_INSTANT_CLIENT"; then

• AC_MSG_ERROR([Oracle Instant Client directory not found. Try --with-oci8-instant-client=DIR])

• fi

• fi

• AC_MSG_RESULT($PHP_OCI8_INSTANT_CLIENT)
  +

• OCI8_DIR=$PHP_OCI8_INSTANT_CLIENT
  +

• AC_MSG_CHECKING([Oracle Instant Client SDK header directory])
  +
  +dnl Header directory for Instant Client SDK RPM install

• OCISDKRPMINC=echo "$PHP_OCI8_INSTANT_CLIENT" | sed -e 's!^/usr/lib/oracle/\(.*\)/client/lib[[/]]*$!/usr/include/oracle/\1/client!'
  +
  +dnl Header directory for Instant Client SDK zip file install

• OCISDKZIPINC=$PHP_OCI8_INSTANT_CLIENT/sdk/include
  +

• if test -f "$OCISDKRPMINC/oci.h"; then

• AC_MSG_RESULT($OCISDKRPMINC)

• PHP_ADD_INCLUDE($OCISDKRPMINC)

• OCI8INCDIR=$OCISDKRPMINC

• elif test -f "$OCISDKZIPINC/oci.h"; then

• AC_MSG_RESULT($OCISDKZIPINC)

• PHP_ADD_INCLUDE($OCISDKZIPINC)

• OCI8INCDIR=$OCISDKZIPINC

• else

• AC_MSG_ERROR([Oracle Instant Client SDK header files not found])

• fi
  +

• OCISYSLIBLIST=echo "$OCI8INCDIR" | sed -e 's!\(.*\)/include$!\1/demo/sysliblist!'

• if test -f "$OCISYSLIBLIST"; then

• PHP_EVAL_LIBLINE(cat $OCISYSLIBLIST, OCI8_SYSLI😎

• fi
  +

• AC_OCI8IC_VERSION($PHP_OCI8_INSTANT_CLIENT)

• case $OCI8_VERSION in

• 10.1)

• PHP_ADD_LIBRARY(clntsh, 1, OCI8_SHARED_LIBADD)

• PHP_ADD_LIBPATH($PHP_OCI8_INSTANT_CLIENT, OCI8_SHARED_LIBADD)

• ;;
  +

• *)

• AC_MSG_ERROR([Unsupported Oracle Instant Client version])

• ;;

• esac
  +

• AC_DEFINE(HAVE_OCI8_ATTR_STATEMENT,1,[ ])

• AC_DEFINE(HAVE_OCI_9_2,1,[ ])

• AC_DEFINE(HAVE_OCI8_TEMP_LOB,1,[ ])

• AC_DEFINE(PHP_OCI8_HAVE_COLLECTIONS,1,[ ])

• AC_DEFINE(HAVE_OCI_INSTANT_CLIENT,1,[ ])
  +

• PHP_NEW_EXTENSION(oci8, oci8.c, $ext_shared)

• AC_DEFINE(HAVE_OCI8,1,[ ])
  +

• PHP_SUBST_OLD(OCI8_SHARED_LIBADD)

• PHP_SUBST_OLD(OCI8_DIR)

• PHP_SUBST_OLD(OCI8_VERSION)
  +
  fi

Step 3: Installing PHP 4.3.9:

patch -u ext/oci8/config.m4 php_oci8ic_buildpatch

Step 4: Installing PHP 4.3.9:

[root@website php-4.3.9]# rm -rf autom4te.cache config.cache
[root@website php-4.3.9]# ./buildconf --force

'./configure' '--with-oci8-instant-client' '--with-apxs=/usr/local/apache/bin/apxs' '--enable-sigchild' '--with-config-file-path=/usr/local/apache/conf'

make
make install

cp php.ini-dist /usr/local/lib/php.ini

Step 5: Call this script to start Apache:

cd /usr/local/sbin/

vi apache_start

#Put this text in the script:

#!/bin/sh

APACHEHOME=/usr/local/apache/bin

LD_LIBRARY_PATH=/usr/lib/oracle/10.1.0.3/client/lib:${LD_LIBRARY_PATH}
TNS_ADMIN=/etc/oracle
export LD_LIBRARY_PATH TNS_ADMIN

echo Starting Apache
$APACHEHOME/apachectl start

export ORACLE_HOME=/usr/lib/oracle/10.1.0.3/client
export LD_LIBRARY_PATH=/usr/lib/oracle/10.1.0.3/client/lib
export TNS_ADMIN=/root

Step 6: create tnsnames.ora

vi /etc/oracle/tnsnames.org

#insert this text, remember this is a word doc and the first line is wrapped:

tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.1.0/Db_1/n

etwork/admin/tnsnames.ora
/etc/oracle/tnsnames.ora

Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Step 7: Start Apache:

chmod 777 /usr/local/sbin/apache_start

/usr/local/sbin/apache_start