PHP FAQ
From Oracle FAQ
Oracle and PHP FAQ. This FAQ is mainly discusses Oracle data access methods from PHP (PHP Hypertext Preprocessor) scripts.
[edit] What is PHP and what's it got to do with Oracle?
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:
<html>
If PHP is working, you will see "Hello World" below:<hr>
<?php
echo "Hello world";
phpinfo(); // Print PHP version and config info
?>
</html>
Execute hello.php from command line (php hello.php) or open it from a web browser (ttp://localhost/hello.php) to see the output. If it's not working, PHP is not correctly installed and this FAQ will not help you.
Note that current versions of Oracle's HTTP Server (Apache) does not ship with PHP (mod_php) pre-installed and that Oracle does not support mod_php or the language itself; however, it may support configurations that include mod_php. Future releases of Oracle iAS (Internet Application Server) will support PHP and include instructions on how to install and use PHP.
[edit] 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 optimised and provides more options.
ORA does not include support for CLOBs, BLOBs, BFILEs, ROWIDs, etc. and should not be used.
[edit] How does one configure PHP to use Oracle?
Follow these steps to prepare your PHP installation for connecting to Oracle databases:
- Download PHP from www.php.net, 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). See the SQL*Net FAQ for details.
- 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.
[edit] How does one connect to Oracle?
Using the OCI Extension Module -
<?php
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 -
<?php
if ($c=ora_logon("scott@orcl","tiger")) {
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, look at this example:
<?php
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. One can use the OCINLogon() function to ensure one gets a new session. Use the OCIPLogon() function to make persistent connections.
[edit] Why do we get 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 one can just uncomment the following lines in the php.ini file:
;extension = php_oci8.dll ;extension = php_oracle.dll
[edit] How does one 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:
<?php
$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);
?>
[edit] 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.
[edit] 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.
<?php $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:
<?php print "nError code = " . ora_errorcode(); print "nError message = " . ora_error(); ?>
[edit] How does one 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.
<?php
// 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);
?>
[edit] Does PHP offer Oracle connection pooling?
Unfortunately PHP does not offer connection pooling. One 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 like SQL Relay (http://sqlrelay.sourceforge.net/) can be used to enable connection pooling for Oracle and other databases.
