The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • php
    May 3, 2006

Getting Oracle Connection Errors Faster in PHP

Christopher Jones
Senior Principal Product Manager

[This article may also help tune slow connections that succeed without errors.]

When a connection fails, you want to know about it as soon as possible.  With Oracle Net there are many ways to configure connection and authentication.  For example a connection:

   $c = oci_connect("hr", "hr", "abc");
could be evaluated by Oracle 10g as using the Easy Connect syntax to machine "abc" (using the default port and database service) or using a net alias "abc" configured in a tnsnames.ora file.

In this example both internal connection methods may be tried sequentially adding to the time delay before you see an error.  It depends on your Oracle Net and DNS settings.

How Oracle is configured to authenticate the user's credentials (here a username and password) can also have an effect.

The problem is not specific to PHP.  In Oracle's SQL*Plus the connection
    $ sqlplus hr/hr@abc
would be the same.

In a basic Oracle 10g installation, one way to return an error as soon as possible is to set this in your sqlnet.ora file:
The DIRECTORY_PATH value disables Easy Connect's "hostname:port/service" syntax.  Instead, create a tnsnames.ora and use a network alias.

Setting AUTHENTICATION_SERVICES to NONE may prevent privileged database connections.  On Windows you may want to use NTS.  Check the Oracle Net Services Administrator's Guide and Oracle Net Services Reference guides for details and for the other authentication methods and authentication-type specific timeout parameters.

If you use Oracle Instant Client, you'll need to create sqlnet.ora on your client machine and set the TNS_ADMIN environment variable to its directory.

I tried this out.  I created tnsnames.ora and sqlnet.ora in my instant client directory. Here is tnsnames.ora.  The service is an invalid service on a far-distant machine so connection will always fail.
          (HOST = www.google.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)))
And here is sqlnet.ora:
I set the environment:
    $ export LD_LIBRARY_PATH=`pwd`
    $ export TNS_ADMIN=`pwd`
Note TNS_ADMIN is set to a directory, not the tnsnames.ora file itself.

When I ran SQL*Plus the error returned after a few seconds.  This would be just the time delay from my machine to the non existent database service:
    $ ./sqlplus -l hr/hr@MYDB2
    SQL*Plus: Release - Production on Thu May 4 11:17:22 2006
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    ORA-12560: TNS:protocol adapter error
Without the sqlnet.ora settings the connection took so long that I killed SQL*Plus from another shell:
    $ mv sqlnet.ora sqlnet.ora.bak
    $ ./sqlplus -l hr/hr@MYDB2
    SQL*Plus: Release - Production on Thu May 4 11:15:02 2006
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
To use the sqlnet.ora and tnsnames.ora files in PHP, make sure that TNS_ADMIN is set in the shell that starts the webserver.  See "Connection Strings" in PHPfest Tutorial.

Join the discussion

Comments ( 1 )
  • Gareth Roberts Monday, May 15, 2006
    This is a damned good tip. In the event of a catastrophic failure on the DB server, or it just falls over, I expect oci_connect to return a value pretty quickly so I can deal with its failure to connect appropriately. Hopefully it does what I think it does.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.