Oracle and PHP FAQ. This FAQ is mainly discusses Oracle data access methods from PHP (PHP Hypertext Preprocessor) scripts.

Oracle and PHP FAQ. This FAQ is mainly discusses Oracle data access methods from PHP (PHP Hypertext Preprocessor) scripts.

Contents [hide]
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?

[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:


If PHP is working, you will see "Hello World" below:

---

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.

[edit] What is the difference between the OCI8 and ORA extension modules?
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.

[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 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.

[edit] How does one connect to Oracle?
Using the OCI8 Extension Module -


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:


UPDATE and DELETE statements can be handled the same way INSERT statements are.

[edit] How are database transactions handled in PHP?
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.

[edit] How are database errors handled in PHP?
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().


[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.


[edit] Does PHP offer Oracle connection pooling?
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.

[edit] What other resource are there?
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