|
|
|
CII. Oracle Functions
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:
ORACLE_HOME
ORACLE_SID
LD_PRELOAD
LD_LIBRARY_PATH
NLS_LANG
For less frequently used Oracle environment variables such as
TNS_ADMIN, TWO_TASK, ORA_TZFILE, and the various Oracle
globalization settings like ORA_NLS33, ORA_NLS10 and the NLS_*
variables refer to Oracle documentation.
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:
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 | Name | Default | Changeable | Changelog |
|---|
| 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.
- oci8.privileged_connect
boolean
This option enables privileged connections using external credentials
(OCI_SYSOPER, OCI_SYSDBA).
- oci8.max_persistent
int
The maximum number of persistent OCI8 connections per process.
Setting this option to -1 means that there is no limit.
- oci8.persistent_timeout
int
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.
- oci8.ping_interval
int
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.
- oci8.statement_cache_size
int
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.
- oci8.default_prefetch
int
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.
- oci8.old_oci_close_semantics
boolean
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.
Example 1. Basic query <?php
$conn = oci_connect('hr', 'hr', 'orcl');
if (!$conn) {
$e = oci_error();
print htmlentities($e['message']);
exit;
}
$query = 'SELECT * FROM DEPARTMENTS';
$stid = oci_parse($conn, $query);
if (!$stid) {
$e = oci_error($conn);
print htmlentities($e['message']);
exit;
}
$r = oci_execute($stid, OCI_DEFAULT);
if (!$r) {
$e = oci_error($stid);
echo htmlentities($e['message']);
exit;
}
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {
print '<tr>';
foreach ($row as $item) {
print '<td>'.($item?htmlentities($item):' ').'</td>';
}
print '</tr>';
}
print '</table>';
oci_close($conn);
?> |
|
Example 2. Insert with bind variables <?php
// Before running, create the table:
// CREATE TABLE MYTABLE (mid NUMBER, myd VARCHAR2(20));
$conn = oci_connect('scott', 'tiger', 'orcl');
$query = 'INSERT INTO MYTABLE VALUES(:myid, :mydata)';
$stid = oci_parse($conn, $query);
$id = 60;
$data = 'Some data';
oci_bind_by_name($stid, ':myid', $id);
oci_bind_by_name($stid, ':mydata', $data);
$r = oci_execute($stid);
if ($r)
print "One row inserted";
oci_close($conn);
?> |
|
Example 3. Inserting data into a CLOB column <?php
// Before running, create the table:
// CREATE TABLE MYTABLE (mykey NUMBER, myclob CLOB);
$conn = oci_connect('scott', 'tiger', 'orcl');
$mykey = 12343; // arbitrary key for this example;
$sql = "INSERT INTO mytable (mykey, myclob)
VALUES (:mykey, EMPTY_CLOB())
RETURNING myclob INTO :myclob";
$stid = oci_parse($conn, $sql);
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
oci_execute($stid, OCI_DEFAULT);
$clob->save("A very long string");
oci_commit($conn);
// Fetching CLOB data
$query = 'SELECT myclob FROM mytable WHERE mykey = :mykey';
$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_execute($stid, OCI_DEFAULT);
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
$result = $row['MYCLOB']->load();
print '<tr><td>'.$result.'</td></tr>';
}
print '</table>';
?> |
|
You can easily access stored procedures in the same way as you
would from the command line.
Example 4. Using Stored Procedures <?php
// by webmaster at remoterealty dot com
$sth = oci_parse($dbh, "begin sp_newaddress( :address_id, '$firstname',
'$lastname', '$company', '$address1', '$address2', '$city', '$state',
'$postalcode', '$country', :error_code );end;");
// This calls stored procedure sp_newaddress, with :address_id being an
// in/out variable and :error_code being an out variable.
// Then you do the binding:
oci_bind_by_name($sth, ":address_id", $addr_id, 10);
oci_bind_by_name($sth, ":error_code", $errorcode, 10);
oci_execute($sth);
?> |
|
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: | Type | Mapping |
|---|
| 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: | Type | Mapping |
|---|
| 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 |
|
|
|
overload
