PHP FAQ
Oracle and PHP FAQ. This FAQ is mainly discusses Oracle data access methods from PHP (PHP Hypertext Preprocessor) scripts.
Contents
- 1 What is PHP and what's it got to do with Oracle?
- 2 What is the difference between the OCI8 and ORA extension modules?
- 3 How does one configure PHP to use Oracle?
- 4 How does one connect to Oracle?
- 5 Why do we get error "Call to undefined function: oci_connect()"?
- 6 How does one SELECT, INSERT, UPDATE and DELETE data from PHP?
- 7 How are database transactions handled in PHP?
- 8 How are database errors handled in PHP?
- 9 How does one call stored procedures from PHP?
- 10 Does PHP offer Oracle connection pooling?
- 11 What other resource are there?
What is PHP and what's it got to do with Oracle?[edit]
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 (http://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) ship with PHP (mod_php) pre-installed. Oracle Enterprise Linux also has PHP available. The OCI8 extension for OEL is available from Unbreakable Linux Network or PHP RPMs on Oracle OSS.
What is the difference between the OCI8 and ORA extension modules?[edit]
PHP offers two extension modules that can be used to connect to Oracle:
- Oracle Call-Interface functions (OCI8)
- Oracle 7 functions (ORA)
OCI8 should be used whenever possible since it is optimised and provides more options.
ORA is deprecated and no longer included in supported versions of PHP. It is unstable, does not include support for CLOBs, BLOBs, BFILEs, ROWIDs, etc. and should not be used.
- PHP 5.2 contains OCI8 1.2.
- PHP 5.3 contains OCI8 1.3, which improves reliability and adds features such as DRCP connection pooling support, FAN support, and external authentication.
You can download OCI8 1.3 from PECL and install in on PHP 4.3.9 onwards.
How does one configure PHP to use Oracle?[edit]
Follow these steps to prepare your PHP installation for connecting to Oracle databases:
- Download PHP from http://www.php.net/, install as per the install.txt or INSTALL files, and test if everything is working.
- Install the Oracle Instant Client, Oracle Client, or Oracle Server software on your machine and configure Oracle Net to connect to your database(s). See the SQL*Net FAQ for details.
- On Windows, edit your php.ini file and uncomment (remove the semi-colon) the following line:
extension = php_oci8.dll
- Ensure that your "extension_dir" parameter (in php.ini) points to the location where the above extension file resides.
... otherwise, compile PHP with the following option:
--with-oci8=shared,/path/to/oracle/home/dir
- Write a small program to test connectivity - see the next question.
Oracle Technology Network has an article on Installing PHP and Oracle using Oracle Instant Client.
How does one connect to Oracle?[edit]
Using the OCI8 Extension Module -
<?php
if ($c = oci_connect("hr", "hr_password", "localhost/XE")) {
echo "Successfully connected to Oracle.";
oci_close($c);
} else {
$err = oci_error();
echo "Oracle Connect Error " . $err['text'];
}
?>
NOTE: You might need to set Oracle environment variables such as ORACLE_HOME and NLS_LANG prior to starting your web server. Avoid setting variables in PHP because Oracle context may have been initialized by Apache prior to the execution of the script. However some variables used only at connection time are probably OK to set in scripts:
<?php
PutEnv("ORACLE_SID=ORCL");
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 oci_new_connect() function to ensure one gets a new session. Use the oci_pconnect() function to make persistent connections.
Why do we get error "Call to undefined function: oci_connect()"?[edit]
PHP is not using the correct extension module. Try compiling PHP with the following options:
--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
Check your PATH (on Windows) and LD_LIBRARY_PATH (Linux) or equivalent variable and make sure it is set to the location of the Oracle libraries.
How does one SELECT, INSERT, UPDATE and DELETE data from PHP?[edit]
The following example demonstrates how data can be manipulated:
<?php
$c = oci_connect("hr", "hr_password", "localhost/XE");
if (!$c) {
echo "Unable to connect: " . var_dump( oci_error() );
die();
}
// Drop old table...
$s = oci_parse($c, "drop table tab1");
oci_execute($s, OCI_DEFAULT);
// Create new table...
$s = oci_parse($c, "create table tab1 (col1 number, col2 varchar2(30))");
oci_execute($s, OCI_DEFAULT);
// Insert data into table...
$s = oci_parse($c, "insert into tab1 values (1, 'Frank')");
oci_execute($s, OCI_DEFAULT);
// Insert data using bind variables...
$var1 = 2;
$var2 = "Scott";
$s = oci_parse($c, "insert into tab1 values (:bind1, :bind2)");
oci_bind_by_name($s, ":bind1", $var1);
oci_bind_by_name($s, ":bind2", $var2);
oci_execute($s, OCI_DEFAULT);
// Select Data...
$s = oci_parse($c, "select * from tab1");
oci_execute($s, OCI_DEFAULT);
while (oci_fetch($s)) {
echo "COL1 = " . oci_result($s, "COL1") .
", COL2 = " . oci_result($s, "COL2") . "<br>\n";
}
// Commit to save changes...
oci_commit($c);
// Logoff from Oracle...
oci_free_statement($s);
oci_close($c);
?>
UPDATE and DELETE statements can be handled the same way INSERT statements are.
How are database transactions handled in PHP?[edit]
When using the OCI8 Extension Module, PHP will commit whenever oci_execute() returns successfully. One can control this behaviour by specifying OCI_COMMIT_ON_SUCCESS (the default) or OCI_DEFAULT as the second parameter to the oci_execute() function call. OCI_DEFAULT can be used to prevent statements from being auto-committed. The oci_commit() and oci_rollback() functions can then be used to control the transaction.
Note that committing is per connection, so any outstanding transaction on any statement handle for that connection will get committed. If you do not want that you need to use oci_new_connect() to get a separate handle.
If one doesn't commit or rollback at the end of a script, PHP will do an implicit rollback. This is opposite of the way SQL*Plus works.
How are database errors handled in PHP?[edit]
When using the OCI8 extension Module, the oci_error() 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 oci_error().
<?php
$c = oci_connect('hr', 'notmypassword', 'localhost/XE');
$err = oci_error(); // for connection errors do not pass a parameter
var_dump($err);
print "\nError code = " . $err['code'];
print "\nError message = " . $err['message'];
print "\nError position = " . $err['offset'];
print "\nSQL Statement = " . $err['sqltext'];
?>
How does one call stored procedures from PHP?[edit]
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 = oci_connect("hr", "hr_password", "localhost/XE");
if (!$c) {
echo "Unable to connect: " . var_dump( oci_error() );
die();
}
// Create database procedure...
$s = oci_parse($c, "create procedure proc1(p1 IN number, p2 OUT number) as " .
"begin" .
" p2 := p1 + 10;" .
"end;");
oci_execute($s, OCI_DEFAULT);
// Call database procedure...
$in_var = 10;
$s = oci_parse($c, "begin proc1(:bind1, :bind2); end;");
oci_bind_by_name($s, ":bind1", $in_var);
oci_bind_by_name($s, ":bind2", $out_var, 32); // 32 is the return length
oci_execute($s, OCI_DEFAULT);
echo "Procedure returned value: " . $out_var;
// Logoff from Oracle...
oci_free_statement($s);
oci_close($c);
?>
Does PHP offer Oracle connection pooling?[edit]
Unfortunately PHP does not offer connection pooling itself. One can open "persistent" Oracle connections with the oci_pconnect() 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.
Starting with Oracle Database 11g, PHP OCI8 can do connection pooling with Database Resident Connection Pooling (DRCP). See PHP Scalability and High Availability white paper for details.
What other resource are there?[edit]
- PHP OCI8 Manual Pages
- Using Oracle and PHP - a free guide to using PHP and Oracle
- Installation Guide for PHP and Oracle Instant Client
- Oracle Technology Network PHP Developer Center
- Several commercial books are available from your local book distributor
