|
If Oracle support is enabled in PHP, but the Oracle client libraries
can not be found, you will get an error when trying to start Apache.
For example, on Windows if php.ini has extension=php_oci8.dll, but
the Oracle home cannot be found, the alert "The dynamic link library
OCI.dll could not be found in the specified path" is displayed.
Make sure the Oracle directory is readable by the OS user starting
the Web server.
Make sure the Oracle environment variables are set correctly before
starting Web server (see the next topic).
Linux users might see an Apache error about being unable to load
libclntsh.so, but are more likely to notice the problem earlier
when compiling PHP. The compiler will fail with the error "Cannot
find file ocidfn.h" or "Cannot find file oci.h." If you have
Oracle installed but are missing the Oracle header files, do a "Client"
install of Oracle. In Oracle 9i, download the Oracle9i Database
Release 2, run the installer, and choose the Client option. In
Oracle 10g, download the Oracle Database 10g Client
Release, run the installer, and choose the Administrator option.
Also see the OTN articles Installing
Oracle, PHP, and Apache on Windows 2000/XP and Installing
Oracle, PHP, and Apache on Linux.
Setting all Oracle environment variables before the Web server
starts is the only safe way to get PHP talking to Oracle. Setting
the variables in PHP scripts or the httpd.conf file mostly does
not work. There are numerous mail list and forum posts because of
general confusion about the environment. The behavior on Windows
and Linux also differs.
The environment needs to be set so PHP can find Oracle's libraries
and message files. Environment variables may also need to be set
to identify the default database or to locate configuration files
for remote database access.
An example shell script for starting Apache is start_apache
:
#!/bin/sh
ORACLE_HOME=/u01/app/oracle/product/10.1.2
ORACLE_SID=orcl
export ORACLE_HOME ORACLE_SID
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
echo "Oracle Home: $ORACLE_HOME"
echo "Oracle SID: $ORACLE_SID"
echo Starting Apache
#export > /tmp/envvars
./apachectl start Depending on where your database
server is and how you connect to it, you may also want set TWO_TASK
or TNS_ADMIN (see the next topic).
Apache, PHP, and Oracle error messages that use phrases like "could
not load" or "could not resolve" all point to environment setup
problems. If you are having problems with the environment, try uncommenting
the "export" line in start_apache. After the script is run, the
file /tmp/envvars will contain the environment variables Apache
knows about. This is useful to solve problems starting Apache at
machine boot time—the environment may be very different from starting
Apache in your user shell.
If Apache starts but gives errors about Oracle libraries, and/or
OCI8 function calls fail, try looking at PHP's environment. Create
the following script "phpinfo.php" where your Web server can read
it and load it in a browser:
<?php
phpinfo();
?> Triple-check the environment
and path configuration. On UNIX, check that LD_LIBRARY_PATH (or equivalent)
contains $ORACLE_HOME/lib. On Windows, the PATH variable may need
to contain %ORACLE_HOME%\bin.
Some messages such as the run-time "Warning: ocilogon(): _oci_open_session:
OCIHandleAlloc? OCI_HTYPE_SVCCTX: OCI_INVALID_HANDLE" are also symptoms
of an incorrect ORACLE_HOME.
If you have compiled PHP with an ORACLE_HOME that is different from the one
being used at run time, be sure to follow the steps carefully as described in Using PHP with OHS. If OCILogon()
gives "ORA-12705 The NLS parameter value specified in an alter session
statement is not valid or not implemented", try setting ORA_NLS33
to the NLS data file directory in the ORACLE_HOME used to compile
PHP. In Using PHP with OHS, this is called $RDBMS_ORACLE_HOME so
set ORA_NLS33 to $RDBMS_ORACLE_HOME/ocommon/nls/admin/data.
The author shares his experience:
I did some tests to see what effect the basic environment in a
standard installation had on a PHP login call:
$mycon = OCILogon("myusername", "mypassword", "MYDB"); I used Red Hat Linux AS 2.1,
Apache 1.3, and PHP 4.3.3.
- With no ORACLE_HOME set prior to "apachectl start" and no "putenv('ORACLE_HOME=/usr/oracle/MYDB')"
in the PHP script I got:
Warning: ocilogon(): _oci_open_server: Error while trying to retrieve text for error ORA-12154
This shows the connection failed, and the message files (which
are located under the Oracle home directory) could not be found.
-
With "putenv('ORACLE_HOME=/usr/oracle/MYDB')" in the PHP script
but no ORACLE_HOME set, I got:
Warning: ocilogon(): _oci_open_server: ORA-12154: TNS:could not resolve service name
The connection still failed, but now the correct message text
could be read from the message file.
-
With ORACLE_HOME set prior to "apachectl start" but without
"putenv()" the connection succeeded. This is the recommended
configuration.
- With ORACLE_HOME set correctly prior to "apachectl start" and
with "putenv()" using an invalid ORACLE_HOME directory, the connection
succeeded. I also tried this on Windows. This time the connection
failed with the same message as my first test above.
I had a similar set of results when I replaced the PHP putenv()
call with an Apache httpd.conf directive "setenv ORACLE_HOME /usr/oracle/MYDB".
However some variables can be set in your PHP scripts. After
setting ORACLE_HOME correctly before starting Apache the following
code changed my default connection and connected to MYDB:
putenv("TWO_TASK=MYDB");
$mycon = OCILogon("myusername", "mypassword");
The environment variables TNS_ADMIN, NLS_DATE_FORMAT (and there
may be others) can be set this way too.
Back to Top
-
A user-chosen net service name is often used to identify which
database to connect to. By default, it is read from the environment
variable ORACLE_SID or it can be explicitly given in the connection
call. The net service name MYDB could be used in Oracle's command-line
SQL*Plus utility, for example:
sqlplus myusername/mypassword@MYDB
or used in PHP like:
$mycon = OCILogon("myusername", "mypassword", "MYDB");
The net service name is commonly mapped to an actual database
by an entry in a tnsnames.ora file :
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYDB)
)
)
The default file location is $ORACLE_HOME/network/admin/tnsnames.ora.
On some operating systems other locations will be checked if
this does not exist. If your tnsnames.ora is in a non-standard
location, you can set the environment variable TNS_ADMIN to the
directory containing it. For example, if you are using /home/myuser/tnsnames.ora,
add these lines to start_apache (see the previous topic):
TNS_ADMIN=/home/myuser
export TNS_ADMIN
If the net service name used in OCILogon() cannot be found
in the tnsnames.ora, or the tnsnames.ora is not found at all
by PHP, you may get an error when logging in:
Warning: ocilogon(): _oci_open_server: ORA-12154: TNS:could not resolve service name
The error ORA-12154 can also occur if you have a file $ORACLE_HOME/network/admin/sqlnet.ora
and it specifies a NAMES.DEFAULT_DOMAIN value. Unqualified net
service names in OCILogon() calls will have sqlnet.ora's value
for NAMES.DEFAULT_DOMAIN appended to the alias. For example
if sqlnet.ora had:
NAMES.DEFAULT_DOMAIN = au.oracle.com
then 'OCILogon("myusername", "mypassword", "mydb")' causes
Oracle to look for the alias "MYDB.AU.ORACLE.COM = . . ." in
tnsnames.ora. A quick solution is to change the tnsnames.ora
entry to:
MYDB.AU.ORACLE.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYDB)
)
)
- If Oracle 10g libraries are used by PHP, try the new
Easy Connect syntax. No tnsnames.ora file is required. To connect
to the MYDB database service running on mymachine:
$c = OCILogon('myusername', 'mypassword', '//mymachine.mydomain/MYDB');
This will only work if the PHP-side libraries are from 10g.
If the database server is 10g but PHP is using Oracle
9i libraries, it will not work. See Oracle's Using
the Easy Connect Naming Method documentation for the Easy
Connect syntax.
-
If Easy Connect has been disabled or you are using Oracle
8i or 9i libraries, another alternative is to use the full connection
string:
$db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST = mymachine.mydomain)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=MYDB)))";
$mycon = OCILogon("myusername", "mypassword", $db);
Back
to Top
Optimizing database connections in a frequently used script can
improve performance and prevent resource problems. Some tips are:
- Avoid multiple connections to Oracle in a single script.
- If you have to make multiple connections, use different tnsnames.ora
aliases, even when you want to connect to the same Oracle instance
with a different user. This will avoid PHP context conflicts since
each connection will use a distinct TCP link to the database.
- Using the persistent OCIPLogon() call minimizes the number
of physical connection requests to the database. It has some limitations.
If the DBA terminates the session with ALTER SYSTEM KILL SESSION
or (worse) terminates the Oracle shadow process with an operating
system kill command, OCIPLogon() may succeed, but subsequent OCI8
calls may still fail. This can be timing dependent. When reusing
a persistent connection, always check its validity at the start
of the script with an explicit query. If you receive an Oracle
error then the connection is no longer valid and you may need
to logoff and login again. Some developers give the user a message
asking them to retry later and call PHP's apache_child_terminate()
to clean up the session.
- Use Oracle Database Resource Manager (which replaces User Profiles)
to limit the resources used by any one connection.
- Use Oracle Net configuration parameters such as SQLNET.EXPIRE_TIME
to free up unused database resources.
- Refresh the Apache processes after they have managed a certain
number of requests, depending on site load. An Apache graceful
restart nightly will clean up connections.
Back to Top
Getting no, or incorrect, results is surprisingly often due to
executing the wrong statement. During development, echo each complete
SQL statement from PHP to check it is well formed and all variables
are correctly expanded. Quoting errors or misunderstandings with
PHP's variable syntax in strings can cause incorrect statements
to be executed.
Testing SQL statements in SQL*Plus before executing them in PHP
also helps confirm correctness.
When statements are entered in tools like SQL*Plus, a semi-colon
is often used to tell the tool that the statement is complete and
can now be executed. However the semi-colon is not considered part
of the statement and is not sent to the database. In PHP, do not
add a semi-colon to a SQL statement—otherwise an Oracle error will
occur. This example is a valid query:
$sql = "SELECT * FROM EMP_DETAILS_VIEW"; Oracle's inbuilt scripting language
PL/SQL has syntax that is different from SQL and requires a final semi-colon:
$plsql = "BEGIN DBMS_OUTPUT.PUT_LINE('hi'); END;"; Back
to Top
Test all return values from OCI8 functions to prevent hidden problems
and misleading results.
During development, set php.ini's error_reporting
directive to E_ALL. Also set display_errors
on, or configure the error log file (and remember to check it!).
This will show Oracle errors as they occur, unless you have explicitly
prefixed Oracle functions calls with http://www.php.net/manual/en/language.operators.errorcontrol.php.
Back to Top
Inserting strings containing single quotes can be handled in several
ways:
- Use bind variables. This also protects against SQL Injection
security issues:
$name = "O'Reilly";
$stmt = 'INSERT INTO CUSTOMERS (NAME) VALUES (:nm)';
$stid = OCIParse($mycon, $stmt);
OCIBindByName($stid, ':nm', $name, -1);
OCIExecute($stid);
This is the recommended method.
- Double every single quote:
$name = "O'Reilly";
$name = str_replace("'", "''", $name);
$stmt = "INSERT INTO CUSTOMERS (NAME) VALUES ('".$name."')";
- Turn on magic_quotes_sybase
in php.ini and use addslashes():
$name = addslashes("O'Reilly");
$stmt = "INSERT INTO CUSTOMERS (NAME) VALUES ('".$name."')";
This is not recommended for portability reasons.
Back to Top
Oracle column names are cases insensitive by default, but when
fetching into associative arrays, specify the names in upper case:
$query = 'select dname from dept';
$stid = OCIParse($conn, $query);
OCIExecute($stid);
while (OCIFetchInto($stid, $row, OCI_ASSOC)) {
echo $row['DNAME']."<br>\n";
} It is possible to create a table with case-sensitive column names. Use var_dump($row)
to see the column names and data structure actually returned by
OCIFetchInto(). PHP has an array_change_key_case()
function to change the case of array keys if needed.
Table name prefixes are not included in associative array keys.
If a column name is used more than once in a query, use column aliases
to distinguish the columns. Otherwis, there will only be one associative
array entry. If the aliases T1N and T2N were not used in the example
below, only results from one of the NAME columns would be returned:
$query = 'select myt1.name as t1n, myt2.name as t2n from myt1, myt2';
$stid = OCIParse($conn, $query);
OCIExecute($stid);
while (OCIFetchInto($stid, $row, OCI_ASSOC)) {
echo $row['T1N']." ".$row['T2N']."<br>\n"; Back
to Top
There is no array fetch capability in PHP, but setting the prefetch
row count with OCISetPrefetch()
is similar and can dramatically improve performance. The advantage
of setting the prefetch row count instead of the array size is that
Oracle does the caching for you. Your data structures only have
to handle one row at a time, and your code can often be simpler.
Here's an example that sets the prefetch row count to 100:
<?php
$conn = OCILogon('myusername', 'mypassword', 'mydb');
$query = 'SELECT * FROM EMP_DETAILS_VIEW';
$stid = OCIParse($conn, $query);
OCIExecute($stid);
OCISetPrefetch($stid, 100);
while ($succ = OCIFetchInto($stid, $row)) {
foreach ($row as $item) {
echo $item." ";
}
echo "<br>\n";
}
OCILogoff($conn);
?> Back
to Top
Bind variables are generally recommended because they help database
performance.
- Problems with binding are often caused because the data value
needs to be accessible at the time when OCIExecute() is called,
not just when OCIBindByName() is executed.
If OCIBindByName() is called inside a wrapper function or
method, and the PHP variable passed to OCIBindByName() is local
to the wrapper function, there may be a problem. The variable
needs to be in scope when OCIExecute() is later called. Otherwise
an Oracle error such as "OCIStmtExecute: ORA-01460: unimplemented
or unreasonable conversion requested" may occur or, confusingly,
it can appear as if no value was set for an OUT variable.
The sample below is a variation on this. The variable $val
is local to the "foreach" command. The code returns no records:
<?php
$qs = 'SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = :dname AND LOCATION_ID = :loc';
$dn = 'IT Support';
$lc = '1700';
$ba = array(':dname' => $dn, ':loc' => $lc);
$conn = OCILogon('myusername', 'mypassword', 'mydb');
$stmt = OCIParse($conn, $qs);
foreach ($ba as $key => $val)
{
OCIBindByName($stmt, $key, $val, -1);
}
OCIExecute($stmt);
while ($succ = OCIFetchInto($stmt, $o)) {
foreach ($o as $mv) {
echo $mv." ";
}
echo "<br>\n";
}
?>
Changing the OCIBindByName() call fixes the problem:
. . .
foreach ($ba as $key => $val)
{
OCIBindByName($stmt, $key, $ba[$key], -1);
}
. . .
- PHP numbers are converted to and from strings when they are
bound. This means the length parameter must generally be given
to OCIBindByName() when returning a numeric data value in a bind
variable. The length is the number of digits that will be returned.
Back to Top
Some sample code to upload a BLOB is given in the OCINewDescriptor()
manual entry.
Watch out for size limitations either in the HTML script:
<input type="hidden" name="MAX_FILE_SIZE" value="3000"> or in the LimitRequestBody
directive in httpd.conf.
An example that uploads into a CLOB is:
<?php
//
// Sample form to upload and insert data into an ORACLE CLOB column
// using PHP's Oracle 8 API.
//
// Based on http://www.php.net/manual/en/function.oci-new-descriptor.php
// modified to work on CLOBs and use register_globals = Off.
//
// Before running this script, execute these statements in SQL*Plus:
// drop table myclobtab;
// create table myclobtab (c1 number, c2 clob);
//
// Tested with PHP 4.3.3 against Oracle 9.2
//
if (!isset($_FILES['lob_upload'])) {
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST" enctype="multipart/form-data">
Upload file: <input type="file" name="lob_upload">
<input type="submit" value="Upload">
</form>
<?php
}
else {
$myid = 1; // should really be a unique id e.g. a sequence number
$conn = OCILogon('myusername', 'mypassword', 'mydb');
// Delete any existing CLOB so the query at the bottom
// displays the new data
$query = 'DELETE FROM MYCLOBTAB';
$stmt = OCIParse ($conn, $query);
OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
OCIFreeStatement($stmt);
// Insert the CLOB from PHP's temporary upload area
$lob = OCINewDescriptor($conn, OCI_D_LOB);
$stmt = OCIParse($conn, 'INSERT INTO MYCLOBTAB (C1, C2) VALUES('.
$myid . ', EMPTY_CLOB()) RETURNING C2 INTO :C2');
OCIBindByName($stmt, ':C2', $lob, -1, OCI_B_CLOB);
OCIExecute($stmt, OCI_DEFAULT);
// The function $lob->savefile(...) reads from the uploaded file.
// If the data was already in a PHP variable $myv, the
// $lob->save($myv) function could be used instead.
if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
OCICommit($conn);
echo "CLOB successfully uploaded\n";
}
else {
echo "Could not upload CLOB\n";
}
$lob->free();
OCIFreeStatement($stmt);
// Now query the uploaded CLOB and display it
$query = 'SELECT C2 FROM MYCLOBTAB WHERE C1 = '.$myid;
$stmt = OCIParse ($conn, $query);
OCIExecute($stmt, OCI_DEFAULT);
OCIFetchInto($stmt, $arr, OCI_ASSOC);
$result = $arr['C2']->load();
echo '<pre>';
echo $result;
echo '</pre>';
OCIFreeStatement($stmt);
OCILogoff($conn);
}
?>
Back to Top
- Despite ongoing debate in the PHP community about how and if
function names should be standardized, the OCI8 extension function
names have been changed in PHP 5. Both old and new names can be
used. Most changes were the addition of underscores; for example
OCIBindByName() is now oci_bind_by_name(). A couple of function
names are very different: OCILogon() is now an alias for oci_connect(),
and the preferred name for OCILogoff() is now oci_close(). All
names are still case insensitive but are documented in lowercase
by convention.
The OCI8
Documentation was updated in March 2004 and shows only the
new names. If documentation on a particular function does not
seem to exist, look at the PHP_FALIAS macros in PHP's oci8.c
to see if the name you are using is an alias for another function.
Note: This FAQ uses the old function names so examples work with
PHP 4 and PHP 5.
- Another name that has changed in PHP 5 on Windows is the name
of the directory containing the extension DLLs. If starting the
Web server gives you the error "Unable to load dynamic library
'c:\PHP\extension\php_oci8.dll'" edit your php.ini file and change
the value of the extension_dir directive from "extension" to
"ext".
Back to Top
The PEAR
DB interface is a database abstraction layer that uses the same
syntax for different database brands. The standard PEAR DB error
function $db->getMessage() returns a simple PEAR error description.
For example, if connection fails for any reason, the message is always:
DB Error: connect failed You can get the exact Oracle error
number and message by using:
$db->getDebugInfo() It includes the Oracle error and
the complete statement, for example:
[nativecode=ORA-01017: invalid username/password; logon denied ] ** oci8://myusername:wrongpassword@mydb The Oracle message text can be extracted
from this string with a wrapper function, for example:
require_once('DB.php');
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'PrintOCIError');
// Display PEAR DB error
function PrintOCIError($e)
{
if (is_object($e)) {
$s = preg_match('/.*\[nativecode=(.*)/', $e->getDebugInfo(), $r);
$etxt = $s ? $r[1] : $e->getDebugInfo();
}
else {
$etxt = "Unknown Error";
}
echo "<p><b>Error</b>:</p>\n<pre>" .
htmlspecialchars($etxt)."</pre>\n";
}
$db = DB::connect("oci8://myusername:wrongpassword@mydb");
if (!DB::isError($db)) {
.
.
.
}
The output from PrintPEARDBError() is:
Error:
ORA-01017: invalid username/password; logon denied
Back
to Top
The PHP libraries were made OCI Thread-safe in a bug fixed in
PHP 4.3.5. In practice, it appears that only loaded servers had
encountered the problem, which exhibited random behavior including crashing.
See PHP
bug 26558 and PHP
Bug 26393
Back to Top
Allowing externally authenticated database connections over the
Web would be a potential security risk for most configurations.
Luckily PHP's OCI8 extension will not allow external authentication
where the username is "/" and the password an empty string. The
call in PHP's oci8.c to Oracle's OCISessionBegin() always sets the
credential flag to OCI_CRED_RDBMS. To support operating system authentication,
the PHP source code would have to be changed to pass Oracle the
OCI_CRED_EXT flag when appropriate.
Back to Top
It is not possible to connect AS SYSDBA or AS SYSOPER in PHP.
The call in PHP's oci8.c to Oracle's OCISessionBegin() function
always sets the mode flag to OCI_DEFAULT. To allow privileged connections,
this would need to be changed to OCI_SYSDBA or OCI_SYSOPER. But
this simple solution opens a potential security hole, see Re:
suggestion about php ocilogon() oracle OCI
FUNCTION
Back to Top
There is no NCHAR or NCLOB support in PHP.
The current PHP implementations of oci8.c always use SQLCS_IMPLICIT
for the character set form when it calls Oracle's OCI. The character
set form would need to be SQLCS_NCHAR to support NCHAR and NCLOB,
and other data-handling changes may be needed in PHP's code.
Back to Top |
