These functions allow you to access Oracle 10, Oracle 9, Oracle 8 and Oracle 7 databases using the Oracle Call Interface (OCI). They support binding of PHP variables to Oracle placeholders, have full LOB, FILE and ROWID support, and allow you to use user-supplied define variables.
You will need the Oracle client libraries to use this extension. Windows users will need libraries with version at least 10 to use the php_oci8.dll.
The most convenient way to install all the required files is to use Oracle Instant Client, which is available from here: http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html. To work with OCI8 module "basic" version of Oracle Instant Client is enough. Instant Client does not need ORACLE_SID or ORACLE_HOME environment variables set. You still may need to set LD_LIBRARY_PATH and NLS_LANG, though.
Before using this extension, make sure that you have set up your Oracle environment variables properly for the Oracle user, as well as your web daemon user. These variables should be set up before you start your web-server. The variables you might need to set are as follows:
After setting up the environment variables for your webserver user, be sure to also add the webserver user (nobody, www) to the oracle group.
If your webserver doesn't start or crashes at startup: Check that Apache is linked with the pthread library:
# ldd /www/apache/bin/httpd libpthread.so.0 => /lib/libpthread.so.0 (0x4001c000) libm.so.6 => /lib/libm.so.6 (0x4002f000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x4004c000) libdl.so.2 => /lib/libdl.so.2 (0x4007a000) libc.so.6 => /lib/libc.so.6 (0x4007e000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)
If the libpthread is not listed you have to reinstall Apache:
Please note that on some systems, like UnixWare it is libthread instead of libpthread. PHP and Apache have to be configured with EXTRA_LIBS=-lthread.
The behaviour of these functions is affected by settings in php.ini.
Table 1. OCI8 Configuration Options
|oci8.privileged_connect||"0"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
|oci8.max_persistent||"-1"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
|oci8.persistent_timeout||"-1"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
|oci8.ping_interval||"60"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
|oci8.statement_cache_size||"20"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
|oci8.default_prefetch||"10"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
|oci8.old_oci_close_semantics||"0"||PHP_INI_SYSTEM||Available since PHP 5.1.2.|
Here's a short explanation of the configuration directives.
This option enables privileged connections using external credentials (OCI_SYSOPER, OCI_SYSDBA).
The maximum number of persistent OCI8 connections per process. Setting this option to -1 means that there is no limit.
The maximum length of time (in seconds) that a given process is allowed to maintain an idle persistent connection. Setting this option to -1 means that idle persistent connections will be maintained forever.
The length of time (in seconds) that must pass before issuing a ping during oci_pconnect(). When set to 0, persistent connections will be pinged every time they are reused. To disable pings completely, set this option to -1.
Note: Disabling pings will cause oci_pconnect() calls to operate at the highest efficiency, but may cause PHP to not detect faulty connections, such as those caused by network partitions, or if the Oracle server has gone down since PHP connected, until later in the script. Consult the oci_pconnect() documentation for more information.
This option enables statement caching, and specifies how many statements to cache. To disable statement caching just set this option to 0.
Note: A larger cache can result in improved performance, at the cost of increased memory usage.
This option enables statement prefetching and sets the default number of rows that will be fetched automatically after statement execution.
Note: A larger prefetch can result in improved performance, at the cost of increased memory usage.
This option controls oci_close() behaviour. Enabling it means that oci_close() will do nothing; the connection will not be closed until the end of the script. This is for backward compatibility only. If you find that you need to enable this setting, you are strongly encouraged to remove the oci_close() calls from your application instead of enabling this option.
The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.
Statement execution mode. Statement is not committed automatically when using this mode.
Statement execution mode. Use this mode if you don't want to execute the query, but get the select-list's description.
Statement execution mode. Statement is automatically committed after oci_execute() call.
Statement fetch mode. Used when the application knows in advance exactly how many rows it will be fetching. This mode turns prefetching off for Oracle release 8 or later mode. Cursor is cancelled after the desired rows are fetched and may result in reduced server-side resource usage.
Used with oci_bind_by_name() when binding BFILEs.
Used with oci_bind_by_name() when binding CFILEs.
Used with oci_bind_by_name() when binding CLOBs.
Used with oci_bind_by_name() when binding BLOBs.
Used with oci_bind_by_name() when binding ROWIDs.
Used with oci_bind_by_name() when binding named data types. Note: in PHP < 5.0 it was called OCI_B_SQLT_NTY.
The same as OCI_B_BFILE.
The same as OCI_B_CFILEE.
The same as OCI_B_CLOB.
The same as OCI_B_BLOB.
The same as OCI_B_ROWID.
The same as OCI_B_NTY.
Used with oci_bind_by_name() to bind LONG values.
Used with oci_bind_by_name() to bind LONG RAW values.
Used with oci_bind_by_name() to bind RAW values.
Used with oci_bind_array_by_name() to bind arrays of NUMBER.
Used with oci_bind_array_by_name() to bind arrays of INTEGER.
Used with oci_bind_array_by_name() to bind arrays of CHAR.
Used with oci_bind_array_by_name() to bind arrays of VARCHAR.
Used with oci_bind_array_by_name() to bind arrays of CHARZ.
Used with oci_bind_array_by_name() to bind arrays of STRING.
Used with oci_bind_array_by_name() to bind arrays of LONG VARCHAR.
Used with oci_bind_array_by_name() to bind arrays of FLOAT.
Used with oci_bind_array_by_name() to bind arrays of LONG.
Default mode of oci_fetch_all().
Alternative mode of oci_fetch_all().
Used with oci_fetch_array() to get empty array elements if field's value is NULL.
Used with oci_fetch_array() to get value of LOB instead of the descriptor.
This flag tells oci_new_descriptor() to initialize new FILE descriptor.
This flag tells oci_new_descriptor() to initialize new LOB descriptor.
This flag tells oci_new_descriptor() to initialize new ROWID descriptor.
The same as OCI_DTYPE_FILE.
The same as OCI_DTYPE_LOB.
The same as OCI_DTYPE_ROWID.
Used with OCI-Lob->flush to free buffers used.
Used with OCI-Lob->writeTemporary to indicate explicilty that temporary CLOB should be created.
Used with OCI-Lob->writeTemporary to indicate explicilty that temporary BLOB should be created.
Example 1. Basic query
Example 2. Insert with bind variables
Example 3. Inserting data into a CLOB column
You can easily access stored procedures in the same way as you would from the command line.
Example 4. Using Stored Procedures
The oci8 extension provides you with 3 different functions for connecting to Oracle. It is up to you to use the most appropriate function for your application, and the information in this section is intended to help you make an informed choice.
Connecting to an Oracle server is a reasonably expensive operation, in terms of the time that it takes to complete. The oci_pconnect() function uses a persistent cache of connections that can be re-used across different script requests. This means that you will typically only incur the connection overhead once per php process (or apache child).
If your application connects to Oracle using a different set of credentials for each web user, the persistent cache employed by oci_pconnect() will become less useful as the number of concurrent users increases, to the point where it may start to adversely affect the overall performance of your Oracle server due to maintaining too many idle connections. If your application is structured in this way, it is recommended that you either tune your application using the oci8.max_persistent and oci8.persistent_timeout configuration settings (these will give you control over the persistent connection cache size and lifetime) or use oci_connect() instead.
Both oci_connect() and oci_pconnect() employ a connection cache; if you make multiple calls to oci_connect(), using the same parameters, in a given script, the second and subsequent calls return the existing connection handle. The cache used by oci_connect() is cleaned up at the end of the script run, or when you explicitly close the connection handle. oci_pconnect() has similar behaviour, although its cache is maintained separately and survives between requests.
This caching feature is important to remember, because it gives the appearance that the two handles are not transactionally isolated (they are in fact the same connection handle, so there is no isolation of any kind). If your application needs two separate, transactionally isolated connections, you should use oci_new_connect().
oci_new_connect() always creates a new connection to the Oracle server, regardless of what other connections might already exist. High traffic web applications should try to avoid using oci_new_connect(), especially in the busiest sections of the application.
Table 2. The driver supports the following types when binding parameters using oci_bind_by_name() function:
|SQLT_NTY||Maps a native collection type from a PHP collection object, such as those created by oci_new_collection().|
|SQLT_BFILEE||Maps a native descriptor, such as those created by oci_new_descriptor().|
|SQLT_CFILEE||Maps a native descriptor, such as those created by oci_new_descriptor().|
|SQLT_CLOB||Maps a native descriptor, such as those created by oci_new_descriptor().|
|SQLT_BLOB||Maps a native descriptor, such as those created by oci_new_descriptor().|
|SQLT_RDD||Maps a native descriptor, such as those created by oci_new_descriptor().|
|SQLT_NUM||Converts the PHP parameter to a 'C' long type, and binds to that value.|
|SQLT_RSET||Maps a native statement handle, such as those created by oci_parse() or those retrieved from other OCI queries.|
|SQLT_CHR and any other type||Converts the PHP parameter to a string type and binds as a string.|
Table 3. The following types are supported when retrieving columns from a result set:
|SQLT_RSET||Creates an oci statement resource to represent the the cursor.|
|SQLT_RDD||Creates a ROWID object.|
|SQLT_BLOB||Creates a LOB object.|
|SQLT_CLOB||Creates a LOB object.|
|SQLT_BFILE||Creates a LOB object.|
|SQLT_LNG||Bound as SQLT_CHR, returned as a string|
|SQLT_LBI||Bound as SQLT_BIN, returned as a string|
|Any other type||Bound as SQLT_CHR, returned as a string|