The Oracle/PHP FAQ
What is PHP?
PHP is a recursive acronym for "PHP Hypertext Preprocessor." It is an open-source, interpretive, HTML-centric, server-side scripting language. PHP is especially suited for Web development and can be embedded into HTML pages. PHP is comparable to languages such as JSP (Java Server Pages) and Oracle's PSP (PL/SQL Server Pages).
This FAQ describes how PHP interacts with the Oracle Database. It assumes that the reader has PHP installed and working. To test if PHP is working, create a simple PHP document, say hello.php:
If PHP is working, you will see "Hello World" below:
echo "Hello world";
phpinfo(); // Print PHP version and config info
?>
Execute hello.php from command line (php hello.php) or open it from a web browser () to see the output. If it's not working, PHP is not correctly installed and this FAQ will not help you.
--------------------------------------------------------------------------------
What is Oracle's involvement with PHP?
Oracle is committed to helping the PHP community make a bigger impact on large enterprise developments by delivering stable production environments, native integration with the Oracle Database, and PHP support in the Oracle Application Server. For example, Zend Core for Oracle, developed in partnership with Zend Technologies, supports businesses using PHP with Oracle Database for mission-critical Web applications. It provides a seamless out-of-the-box experience delivering a stable, high performance, easy-to-install and supported PHP development and production environment fully integrated with the Oracle Database. This single download gives you a PHP 5 distribution that's ready for development and deployment against your Oracle database—best of all, it's free!
--------------------------------------------------------------------------------
What is the difference between the OCI and ORA extension modules?
PHP offers two extension modules that can be used to connect to Oracle:
The normal Oracle functions (ORA); and
the Oracle Call-Interface functions (OCI).
OCI should be used whenever possible since it is optimized and provides more options. For example, ORA doesn't include support for CLOBs, BLOBs, BFILEs, ROWIDs, etc.
--------------------------------------------------------------------------------
How do I configure PHP to use Oracle?
Follow these steps to prepare your PHP installation for connecting to Oracle databases:
Download PHP from , install as per the install.txt file, and test if everything is working.
Install the Oracle Client (or Server) software on your machine and configure SQL*Net to connect to your database(s).
Edit your php.ini file and uncomment the following two lines (only if your version shipped with pre-compiled extension modules):
;extension = php_oci8.dll
;extension = php_oracle.dll
... otherwise, compile PHP with the following options:
--with-oracle=/path/to/oracle/home/dir
--with-oci8=/path/to/oracle/home/dir
Ensure that your "extension_dir" parameter (in php.ini) points to the location where the above extension files reside.
Write a small program to test connectivity - see the next question.
--------------------------------------------------------------------------------
How do I connect to Oracle?
Using the OCI Extension Module:
if ($c=OCILogon("scott", "tiger", "orcl")) {
echo "Successfully connected to Oracle.\n";
OCILogoff($c);
} else {
$err = OCIError();
echo "Oracle Connect Error " . $err[text];
}
?>
Using the ORA Extension Module:
if ($c=ora_logon("")) {
echo "Successfully connected to Oracle.\n";
ora_commitoff($c);
ora_logoff($c);
} else {
echo "Oracle Connect Error " . ora_error();
}
?>
NOTE: You might want to set your Oracle environment from within PHP before connecting. See this example:
PutEnv("ORACLE_SID=ORCL");
PutEnv("ORACLE_HOME=/app/oracle/product/9.2.0");
PutEnv("TNS_ADMIN=/var/opt/oracle");
...
Please note that PHP will share/re-use connections if the same userid/password combination is used (more than once) on a particular "page" or httpd server session. You can use the OCINLogon() function to ensure one gets a new session. Use the OCIPLogon() function to make persistent connections.
--------------------------------------------------------------------------------
Why do we get the error "Call to undefined function: ora_logon()/ ocilogon()"?
PHP is not using the correct extension module. Try compiling PHP with the following options:
--with-oracle=/path/to/oracle/home/dir
--with-oci8=/path/to/oracle/home/dir
On Windows systems you can just un-comment the following lines in the php.ini file:
;extension = php_oci8.dll
;extension = php_oracle.dll
--------------------------------------------------------------------------------
How do I SELECT, INSERT, UPDATE and DELETE data from PHP?
The following example demonstrates how data can be SELECTed and manipulated via INSERT, UPDATE and DELETE statements:
$c=OCILogon("scott", "tiger", "orcl");
if ( ! $c ) {
echo "Unable to connect: " . var_dump( OCIError() );
die();
}
// Drop old table...
$s = OCIParse($c, "drop table tab1");
OCIExecute($s, OCI_DEFAULT);
// Create new table...
$s = OCIParse($c, "create table tab1 (col1 number, col2 varchar2(30))");
OCIExecute($s, OCI_DEFAULT);
// Insert data into table...
$s = OCIParse($c, "insert into tab1 values (1, 'Frank')");
OCIExecute($s, OCI_DEFAULT);
// Insert data using bind variables...
$var1 = 2;
$var2 = "Scott";
$s = OCIParse($c, "insert into tab1 values (:bind1, :bind2)");
OCIBindByName($s, ":bind1", $var1);
OCIBindByName($s, ":bind2", $var2);
OCIExecute($s, OCI_DEFAULT);
// Select Data...
$s = OCIParse($c, "select * from tab1");
OCIExecute($s, OCI_DEFAULT);
while (OCIFetch($s)) {
echo "COL1=" . ociresult($s, "COL1") .
", COL2=" . ociresult($s, "COL2") . "\n";
}
// Commit to save changes...
OCICommit($c);
// Logoff from Oracle...
OCILogoff($c);
?>
--------------------------------------------------------------------------------
How are database transactions handled in PHP?
When using the OCI Extension Module, PHP will commit whenever ociexecute() returns successfully. One can control this behaviour by specifying OCI_COMMIT_ON_SUCCESS (the default) or OCI_DEFAULT as the second parameter to the ociexecute() function call. OCI_DEFAULT can be used to prevent statements from being auto-committed. The OCICommit() and OCIRollback() functions can then be used to control the transaction.
Note that when OCI_DEFAULT is used on any statement handle, it is inherited by the other statement handles for the connection. You cannot use a mix of autocommit/explicit commit on the same connection handle. If you want to do that you need to use ociNLogon() to get a separate handle.
The ORA Extension Module supports an autocommit mode. Use the ORA_CommitOn() and ORA_CommitOff() functions to toggle between autocommit mode and normal mode. When in normal mode (ORA_CommitOff), one can use the ORA_Commit() and ORA_Rollback() functions to control transactions.
If one doesn't commit or rollback at the end of a script, PHP will do an implicit commit. This is consistent with the way SQL*Plus works.
--------------------------------------------------------------------------------
How are database errors handled in PHP?
When using the OCI extension Module, the OCIError() function can be used to obtain an array with error code, message, offset and SQL text. One can also obtain the error for a specific session or cursor by supplying the appropriate handle as an argument to OCIError(). Without any arguments, OCIError() will return the last encountered error.
$err = OCIError();
var_dump($err);
print "\nError code = " . $err[code];
print "\nError message = " . $err[message];
print "\nError position = " . $err[offset];
print "\nSQL Statement = " . $err[sqltext];
?>
When using the ORA Extension Module, one can use the ora_error() and ora_errorcode() functions to report errors:
print "\nError code = " . ora_errorcode();
print "\nError message = " . ora_error();
?>
--------------------------------------------------------------------------------
How do I call stored procedures from PHP?
The following example creates a procedure with IN and OUT parameters. The procedure is then executed and the results printed out.
// Connect to database...
$c=OCILogon("scott", "tiger", "orcl");
if ( ! $c ) {
echo "Unable to connect: " . var_dump( OCIError() );
die();
}
// Create database procedure...
$s = OCIParse($c, "create procedure proc1(p1 IN number, p2 OUT number) as " .
"begin" .
" p2 := p1 + 10;" .
"end;");
OCIExecute($s, OCI_DEFAULT);
// Call database procedure...
$in_var = 10;
$s = OCIParse($c, "begin proc1(:bind1, :bind2); end;");
OCIBindByName($s, ":bind1", $in_var);
OCIBindByName($s, ":bind2", $out_var, 32); // 32 is the return length
OCIExecute($s, OCI_DEFAULT);
echo "Procedure returned value: " . $out_var;
// Logoff from Oracle...
OCILogoff($c);
?>
--------------------------------------------------------------------------------
Does PHP offer Oracle connection pooling?
Unfortunately, PHP does not offer connection pooling. You can open "persistent" Oracle connections with the ora_plogon() and OCIPLogon() function calls. Nevertheless, persistent connections do not scale as well as connection pooling. A persistent connection will be kept open for a process, but it will not allow connections to be shared between different processes.
Third-party tools such as SQL Relay can be used to enable connection pooling for Oracle and other databases.
