A
Wetpaint Site

Sign in to
Wetpaint

Home

Discussions

Photos

Videos

News

Updates

Droplets

Members

Invite

Have a Wetpaint account? Sign in

EasyEdit

Email page

Share this

PHP Oracle FAQ

.

What is PHP?

What is Oracle's involvement with PHP

Where can I get more information?

What are the ORA, OCI8 and PDO_OCI extensions

How do I configure PHP to use Oracle?

How do I connect to Oracle with the OCI8 extension?

How do I connect to Oracle with the PDO_OCI driver for the PDO extension?

What are Valid Connection Strings for OCI8 and PDO_OCI?

Why do I get the error "Call to undefined function: oci_connect() /
ocilogon()"?

Help I'm not seeing any errors or anything at all!

What if PHP OCI8 is not installed or not found?

Where do I set Oracle environment variables?

What's the difference between oci_connect() and OCILogon(), or between
oci_parse() and OCIParse()?

How do I SELECT, INSERT, UPDATE and DELETE data from PHP?

How are database transactions handled in PHP?

How are database errors handled in PHP?

How do I call stored procedures from PHP?

Does PHP offer Oracle connection pooling?

How do I manage connections in OCI8?

How do I check my SQL statements are working?

How can I insert strings containing quotes?

How do I fetch results using associative arrays where the same column name
occurs?

What is pre-fetching in OCI8?

How do I use Oracle bind variables in OCI8?

How do I upload LOBS with OCI8?

Should I upgrade to PHP 5?

Can I do Operating System Authentication?

Can I do authentication using AS SYSDBA or AS SYSOPER?

Are NCHAR and NCLOB supported in PHP?

What is Zend Core for Oracle?

What is Zend Server?

What is PHP?

PHP is an open-source, interpreted, 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).

PHP is a
recursive acronym for "PHP Hypertext Preprocessor."

To test if PHP is
working, create a simple PHP document, say hello.php:

<html>
<p>If PHP is working, you will see "Hello,
world!" below:</p><hr>
<?php
echo "Hello,
world!<br>";
phpinfo(); // Print PHP version and configuration
info
?>
</html>

Execute hello.php from the
command line (php hello.php) or open it from a web browser
(http://localhost/hello.php) to see the output.

What is Oracle's involvement with PHP

Oracle is committed to
helping the PHP community make an impact on large enterprise developments by
delivering stable production environments, native integration with the Oracle
Database, and PHP support in the Oracle Application Server. Oracle makes direct
contributions to the open source PHP project and assists the PHP community in
maintaining PHP's Oracle drivers.

Where can I get more information?

There are many good
references:

The PHP Developer Center on OTN has many good resources.

The OTN PHP Discussion forum can help answer questions

The technical note on Installing PHP and the Oracle Instant Client for
Linux and Windows is also on OTN.

A free book on how to use PHP and Oracle from Oracle
discusses many details about OCI8 best practices.

Various books on PHP and Oracle can be purchased from bookstores.

The PHP OCI8 Reference Manual is the core place for
documentation. User annotations give tips and tricks.

The OCI8 test suite in the PHP Source
code shows the expected behavior of OCI8 calls in detail.

What are the ORA, OCI8 and PDO_OCI extensions

OCI8 extension: Oracle Call Interface functions
OCI8 is the
recommended PHP driver for Oracle. In use at many high profile sites, it
provides an easy to use, reliable interface for creating high performance web
applications. It supports SQL and PL/SQL calls, and among other features has
support for LOBs and collections. The latest release, OCI8 1.3, supports
advanced Oracle features such as connection pooling ("DRCP"), allowing better
use of database resources even when PHP applications are spread across
multiple web tier severs. DRCP scales to tens of thousands of concurrent
connections.

PDO_OCI extension: "PHP Data Object" functions
PDO_OCI is
the Oracle driver for the PDO data abstraction interface in PHP. While PDO is
gaining a little popularity, PDO_OCI isn't as mature as OCI8 and doesn't
support some of the features that OCI8 does. The implementation of PDO_OCI8 is
separate from OCI8 and provides a different user API.

ORA extension: Oracle functions
The obsolete ORA extension is no
longer included in PHP distributions. It had limited functionality and was
implemented using a now obsolete API to the Oracle database.

How do I configure PHP to use Oracle?

The OCI8 and PDO_OCI
extensions are included in various PHP distributions, starting with the PHP
source code and pre-built binaries for Windows available from http://www.php.net/downloads.php. Various other packages of
PHP are built from the source code release. There are PHP RPMs
for Linux on oss.oracle.com, which are also available via up2date to Unbreakable Linux Network subscribers. The Oracle drivers are
also included in Zend Core for Oracle and its replacement, Zend
Server.

If not using a pre-built package, the basic steps to install OCI8
are:

Download PHP from http://www.php.net/downloads.php and download Oracle Instant Client.

On Windows, edit your php.ini file and uncomment the following line:
extension = php_oci8.dll
Ensure that your extension_dir parameter (in
php.ini) contains the directory where the above extension library is located.

On non-Windows platforms rebuild PHP using the following configuration
option: --with-oci8=shared,instantclient,/path/to/instant/client/libs
Edit
your php.ini file and add: extension = oci8.so
Ensure that your
extension_dir parameter (in php.ini) points to the location where oci8.so was
installed.

Set environment variables required by Oracle, such as PATH (Windows) or
LD_LIBRARY_PATH (on Linux)

Restart you webserver.

There are a number of ways PHP can be
configured which allows maximum flexibility. The PHP
Documentation gives installation steps for various platforms. Oracle has comprehensive PHP Oracle install instructions
covering many possible scenarios on Windows and Linux. The Oracle technical note
on installing PHP and Oracle using the small
foot-print, free Oracle Instant Client libraries is very popular.

How do I connect to Oracle with the OCI8 Extension?

Using the
OCI8 extension:

<?php

$conn =
oci_connect('myusername', 'mypassword', 'myhost/XE');

if (!$conn)
{
trigger_error("Could not connect to database",
E_USER_ERROR);
}

?>

The example above connects to
the Oracle database "XE" on the computer "myhost".

The connection string
can be an "Easy Connect" string, an Oracle Net alias from the tnsnames.ora file,
or a hard coded Oracle Net connection string.

PHP will
share/re-use connections if the same user credentials are used more than once in
a script or httpd server session. You can use the oci_new_connect() function to
ensure a new session is used. Call the oci_pconnect() function to make a
persistent connection that isn't closed at the end of the script (making the
reconnection in the next script much faster).

How do I connect to Oracle with the PDO_OCI driver for the PDO
extension?

With the PDO_OCI driver:

<?php
try {
$dbh = new PDO('oci:dbname=myhost/XE',
'myusername', 'mypassword');
}
catch (PDOException $e)
{
trigger_error("Could not connect to database: ". $e->getMessage(),
E_USER_ERROR);
}
?>

The connection string can be an
"Easy Connect" string, an Oracle Net alias from the tnsnames.ora file, or a hard
coded Oracle Net connection string.

What are Valid Connection Strings for OCI8 and
PDO_OCI?

The connection string can be an "Easy Connect" string,
an Oracle Net alias from the tnsnames.ora file, or a hard coded Oracle Net
connection string:

1. If Oracle 10g or 11g libraries are used by
PHP, try the Easy Connect syntax. No long connection string is needed. No
tnsnames.ora network configuration file is required. To connect to the MYDB
database service running on mymachine use:

$c =
oci_connect('myusername', 'mypassword',
'mymachine.mydomain/MYDB');
$d = new
PDO('oci:dbname=myhost/XE', 'myusername', 'mypassword');

If
you are using the free Oracle XE database on the same machine as your web
server, the connection string would be:

$c =
oci_connect('myusername', 'mypassword', '127.0.0.1/XE');
$d = new PDO('oci:dbname=127.0.0.1/XE', 'myusername',
'mypassword');

The Easy Connect syntax only works if PHP is
linked with Oracle 10g or more recent libraries. It works even if the database
is another version. If PHP is using Oracle 9i libraries this type of connection
string will not work. See Oracle's Using the Easy Connect Naming Method
documentation for the syntax.

If you see the error "ORA-12514:
TNS:listener does not currently know of service requested in connect
descriptor", then the service name component is incorrect. On the database
server, run

lsnrctl services

to
find the service names that will be recognized.

2. A user chosen
net service name is often used to identify which database to connect to. By
default it is read from the environment variable ORACLE_SID or it can be
explicitly given as the third parameter in an OCI8 connection call. The net
service name MYDB that is used in Oracle's command-line SQL*Plus utility like:

sqlplus
myusername/mypassword@MYDB

can be used in PHP like:

$c = oci_connect('myusername', 'mypassword',
'MYDB');
$d = new PDO('oci:dbname=MYDB',
'myusername', 'mypassword');

The net service name is commonly
mapped to an actual database by an entry in a tnsnames.ora file :

MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = mymachine.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME = MYDB)
)
)

The default file
location is $ORACLE_HOME/network/admin/tnsnames.ora. On some operating
systems other locations (e.g. $HOME/.tnsnames.ora) will be checked too.
If your tnsnames.ora is in a non-standard location you can set the environment
variable TNS_ADMIN to the directory containing it. For example if you are using
/home/myuser/tnsnames.ora, do this before starting Apache:

TNS_ADMIN=/home/myuser
export
TNS_ADMIN

If the net service name used in oci_connect() cannot
be found in the tnsnames.ora, or the tnsnames.ora is not found at all by
PHP, you may get an error when logging in: Warning: ocilogon():
_oci_open_server: ORA-12154: TNS:could not resolve service name

The error
ORA-12154 can also occur if you have a file
$ORACLE_HOME/network/admin/sqlnet.ora and it specifies a
NAMES.DEFAULT_DOMAIN value. Unqualified net service names in oci_connect() calls
will have sqlnet.ora's value for NAMES.DEFAULT_DOMAIN appended to the alias. For
example if sqlnet.ora had:

NAMES.DEFAULT_DOMAIN =
au.oracle.com

then 'oci_connect("myusername", "mypassword",
"mydb")' causes Oracle to look for the alias "MYDB.AU.ORACLE.COM = . . ." in
tnsnames.ora. A quick solution is to change the tnsnames.ora entry to:

MYDB.AU.ORACLE.COM =
(DESCRIPTION =
(ADDRESS
= (PROTOCOL = TCP)(HOST = mymachine.mydomain)(PORT = 1521))
(CONNECT_DATA
=
(SERVER = DEDICATED)
(SERVICE_NAME =
MYDB)
)
)

3. If Easy Connect has been disabled,
or if you are using Oracle 8i or 9i libraries, another alternative is to
hardcode the full connection string in PHP:

$db =
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST =
mymachine.mydomain)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=MYDB)))";

$c
= oci_connect('myusername', 'mypassword', $db);
$d = new PDO("oci:dbname=$db", 'myusername',
'mypassword');

Why do I get the error "Call to undefined function: oci_connect() /
ocilogon()"?

Check phpinfo() output and make sure there is a section titled OCI8. Load
this script in your browser:

<?php
phpinfo();
?>

The pre-built Windows php_oci8.dll and php_pdo_oci.dll DLLs need Oracle
10.2 client libraries or greater. The php_oci8_11g.dll needs Oracle 11.1
client libraries or greater.

Make sure there is no library clash if multiple versions of Oracle are
installed on the machine. On Windows, set PATH or put the Oracle Instant
Client libraries in the PHP extension directory.

Make sure that Oracle environment variables are set in the process that
starts the webserver. Using PutEnv() in PHP scripts is error prone and
non-portable because scripts may be executed after library initialization has
already occurred.

There is a section on Common PHP Oracle Connection Errors in the
Underground PHP and Oracle manual.

Help I'm not seeing any errors or anything at
all!

Check the web server error file to make sure the OCI8 or
PDO_OCI extensions are being loaded by using the phpinfo() function
If there
are no issues with the extension, edit your php.ini file and
set:

display_errors = On

or
configure the error log file (and remember to check it!). This will show Oracle
errors as they occur, unless you have explicitly prefixed Oracle functions calls
with http://www.php.net/manual/en/language.operators.errorcontrol.php.
For security reasons, do not run production systems with display_errors set to
On.

During development set php.ini's error_reporting directive to

error_reporting = E_ALL | E_STRICT.

Test all
return values from OCI8 and PDO_OCI functions to prevent hidden problems and
misleading results and use oci_error() to fetch the error message.

What if PHP OCI8 is not installed or not found?

Commonly seen
errors are:

The error "Unable to load dynamic library php_oci8.dll" when starting the
web server.

The error "The dynamic link library OCI.dll could not be found in the
specified path" when starting the web server.

The error "PHP Fatal error: Call to undefined function oci_connect()" or
"Call to undefined function OCILogon()" when running a script.

The error "OCIEnvNlsCreate() failed. There is something wrong with your
system" when running a script.

PHP must be configured to locate the
php_oci8.dll (on Windows) or oci8.so (if using a shared library on Linux) files.
PHP must also be able to find Oracle's client libraries.

Make sure that your php.ini file is being found correctly. Create the
following script "phpinfo.php" where your web server can read it:

<?php
phpinfo();
?>
Load phpinfo.php in a browser.
If the "Configuration File (php.ini) Path" line shows only a directory path
and does not end with "php.ini", then your configuration file is not being
read. Move your php.ini to the directory shown and restart your web server.

Check the phpinfo() output and make sure the value of the "extension_dir"
directive contains the directory with php_oci8.dll e.g. "c:\php-5.2.9\ext" or
the location of oci8.so (if using a shared library on Linux). Check the
phpinfo() output and make sure "extension=php_oci8.dll" is uncommented (remove
the leading semi-colon). On Linux, if using a shared library, php.ini should
contain "extension=oci8.so".

Make sure the Oracle environment variables are set correctly before
starting the web server (see the next topic).

Make sure there is no clash if multiple Oracle versions or libraries have
been installed.

Windows users will need Oracle 10.2 or 11.1 libraries.

The Oracle directory that PHP links with needs to be readable by the owner
of the web server process. If you configure PHP to use Oracle 10gR2 libraries
from an ORACLE_HOME style install, and Apache crashes at startup, check the
directory permissions on the Oracle software. Oracle 10gR2 is shipped with a
more restrictive set of directory permissions than previous Oracle releases.
The 10.2.0.2 patchset contains $ORACLE_HOME/install/changePerm.sh to re-set
permissions on files required by external programs like PHP. With 10.2.0.2 you
may need to add the directories lib32, ldap, ldap/adminm, and srvm/lib32 to
the RELAX_DIR_ALL variable in this script before running it. If you don't want
to change your ORACLE_HOME permissions, build PHP with Oracle Instant Client.

Linux users might see an Apache error about being unable to load
libclntsh.so, but are more likely to notice problems when compiling PHP. The
compiler will fail with the error 'Cannot find file "ocidfn.h"' or 'Cannot
find file "oci.h"'. If you have Oracle installed but are missing the Oracle
header files, do a "Client" install of Oracle. In Oracle 9i download "Oracle9i
Database Release 2", run the installer and choose the "Client" option. In
Oracle 10g download the "Oracle Database 10g Client Release", run the
installer and choose the "Administrator" option. Instead of doing a full
Oracle client install, it's convenient and fast to build PHP using the free,
small Oracle Instant Client. See the OTN article Installing PHP and the Oracle Instant Client for
Linux and Windows.

On Linux, consider using a prebuilt version of PHP, such as the PHP RPMs.

Where do I set Oracle environment variables?

Setting all
Oracle environment variables before the web server starts is the only safe way
to get PHP talking to Oracle. Setting the variables in PHP scripts or the
httpd.conf file mostly does not work. There are numerous mail list and forum
posts because of general confusion about the environment. The behavior on
Windows and Linux also differs.

The environment needs to be set so PHP
can find Oracle's libraries and message files. Environment variables may also be
needed to identify the default local database, needed for locating configuration
files for remote database access, or needed to set Oracle's globalization
parameters correctly.

Using PHP's PutEnv() function is unlikely to work.
Using Apache's SetEnv directive is sometimes insufficient.

If Apache
starts but gives errors about Oracle libraries, and/or OCI8 function calls fail,
try looking at PHP's environment by using the phpinfo() script. Look at the
"Environment" section (not the "Apache Environment" section) of phpinfo()
output.

The Underground PHP and Oracle Manual has a section
"Setting Oracle Environment Variables for Apache" with more information.

An example shell script for starting Apache is "start_apache" :

#!/bin/sh

ORACLE_HOME=/u01/app/oracle/product/9.2
ORACLE_SID=orcl
export
ORACLE_HOME ORACLE_SID
export
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
echo "Oracle Home:
$ORACLE_HOME"
echo "Oracle SID: $ORACLE_SID"
echo Starting
Apache
#export > /tmp/envvars
./apachectl
start

Depending where your database server is and how you
connect to it you may also want to set TWO_TASK or TNS_ADMIN (see the next
topic). To ensure the correct character set is used, you may also want to set
NLS_LANG.

Apache, PHP and Oracle error messages that use phrases like
"could not load" or "could not resolve" all point to environment setup problems.
If you think you are seeing this, try uncommenting the "export" line in
"start_apache". After the script is run, the file /tmp/envvars will contain the
environment variables Apache knows about. This is useful to solve problems
starting Apache at machine boot time - the environment may be very different to
starting Apache in your user shell.

Triple-check the environment and
path configuration. On UNIX check that LD_LIBRARY_PATH (or equivalent, e.g
DYLD_LIBRARY_PATH on Mac OS X, SHLIB_PATH on HP-UX etc) contains
$ORACLE_HOME/lib or your Instant Client library directory. On Windows the PATH
variable may need to contain %ORACLE_HOME%\bin or the directory containing
Oracle Instant Client.

If you use Oracle Instant Client, you do not need
to set ORACLE_HOME.

Some messages like the runtime "OCIEnvNlsCreate()
failed. There is something wrong with your system" or the error "ocilogon():
_oci_open_session: OCIHandleAlloc? OCI_HTYPE_SVCCTX: OCI_INVALID_HANDLE" are
symptoms of an incorrect ORACLE_HOME.

If oci_connect() gives "ORA-12705
The NLS parameter value specified in an alter session statement is not valid or
not implemented", the environment might not consistently use one Oracle
installation. Try setting ORA_NLS to the NLS data file directory in the
ORACLE_HOME used to compile PHP, e.g. $ORACLE_HOME/ocommon/nls/admin/data.

To give an example of the problems if environment variables are not set
correctly, I did some tests to see what effect the basic environment in a
standard installation had on a PHP 4 login call:

$mycon = ocilogon("myusername", "mypassword",
"MYDB");

I used Red Hat Linux AS 2.1, Apache 1.3 and PHP
4.3.3.

With no ORACLE_HOME set prior to "apachectl start" and no
"putenv('ORACLE_HOME=/usr/oracle/MYDB')" in the PHP script I got: Warning:
ocilogon(): _oci_open_server: Error while trying to retrieve text for error
ORA-12154
This shows the connection failed, and the message files (which
are located under the Oracle home directory) could not be found. The Oracle
environment is incorrect.

With "putenv('ORACLE_HOME=/usr/oracle/MYDB')" in the PHP script but no
ORACLE_HOME set, I got: Warning: ocilogon(): _oci_open_server: ORA-12154:
TNS:could not resolve service name
The connection still failed, but now the
correct message text could be read from the message file. The Oracle
environment is still not correct.

With ORACLE_HOME set prior to "apachectl start" and removing the
"putenv()" script call, the connection succeeded. This is the recommended
configuration.

With ORACLE_HOME set correctly prior to "apachectl start" and with
"putenv()" using an invalid ORACLE_HOME directory, the connection succeeded. I
also tried this on Windows. This time the connection failed with the same
message as my first test above.

I had a similar set of results when I
replaced the PHP putenv() call with an Apache httpd.conf directive "setenv
ORACLE_HOME /usr/oracle/MYDB". Having said all this, some variables used only at
script run time can be set in your PHP scripts. In a working configuration, the
following code changed my default connection and connected to MYDB:

putenv("TWO_TASK=MYDB");
$mycon =
oci_connect("myusername", "mypassword");

The environment
variables TNS_ADMIN, NLS_DATE_FORMAT (and there may be others) can probably be
set in scripts too.

What's the difference between oci_connect() and OCILogon(), or between
oci_parse() and OCIParse()?

Some PHP extensions had their function
names standardized in PHP 5 to use underscore separation. Both naming styles can
still be used in OCI8, but the new style is recommended. New names
include:

oci_connect()
oci_parse()
oci_bind_by_name()
oci_execute()
oci_fetch_array()

Old
names include

OCILogon()
OCIParse()
OCIBindByName()
OCIExecute()
OCIFetchInto()

Function
names in PHP are case insensitive

A complete list of functions is found
in the PHP OCI8 Manual

How do I 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. The table is assumed to have been
previously created:

create table mytable (id
number, val varchar2(20));

The PHP code is:
<?php

$conn = oci_connect("hr", "hrpwd",
"localhost/XE");

if (!$conn) {
$m = oci_error();
echo
$m["message"];
exit;
}

// Insert using bind variables
$stid =
oci_parse($conn, "insert into mytable values (:i_b, :v_b)");
$id = 1;
$val
= "Scott";
oci_bind_by_name($stid, ":i_b", $id);
oci_bind_by_name($stid,
":v_b", $val);
oci_execute($stid);

// Update the row
$stid =
oci_parse($conn, "update mytable set val = :v_b where id = :i_b");
$id =
1;
$val = "Frank";
oci_bind_by_name($stid, ":i_b",
$id);
oci_bind_by_name($stid, ":v_b",
$val);
oci_execute($stid);

$stid = oci_parse($conn, "select * from
mytable");
oci_execute($stid);

// Query the table
echo "<table
border='1'>";
while ($row = oci_fetch_array($stid,
OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>";
foreach ($row as $item)
{
echo
"<td>".($item!==null?htmlentities($item):"&nbsp;")."</td>";
}
echo
"</tr>".PHP_EOL;
}
echo "</table>";

// Delete the
row
$stid = oci_parse($conn, "delete from mytable where id = :i_b");
$id =
1;
oci_bind_by_name($stid, ":i_b",
$id);
oci_execute($stid);

oci_free_statement($stid);
oci_close($conn);

?>

Using
bind variables is recommended for performance.

Tables can be created
using the oci_parse() and oci_execute() pair of calls. However, as with all
Oracle applications, DDL and DCL statements are only recommended during
application installation and not during application runtime.

How are database transactions handled in PHP?

By default, PHP
will autocommit whenever oci_execute() returns successfully. Passing OCI_DEFAULT
as the second parameter to the oci_execute() function call begins a transaction.
The oci_commit() and oci_rollback() functions can then be used to control the
transaction.

Note that when oci_commit() or oci_rollback() are used, or
oci_execute() autocommits, all transactions on other statement handles for the
connection are committed or rolled back. You cannot use a mix of
autocommit/explicit control on the same connection handle. If you want to do
that you need to use ociNLogon() to get a separate handle.

PHP will
rollback any uncommitted transaction at the conclusion of the script.

How are database errors handled in PHP?

When OCI8 functions
return failure, the oci_error() function can be used to obtain an array with
error code, message, offset and SQL text:

$stid =
oci_parse($conn, "select column_does_not_exist from employees");
$rc =
oci_execute($stid);
if (!$rc) {
$err = oci_error($stid);
echo "Code: "
. $err["code"] . "<br>";
echo "Message: " . $err["message"] .
"<br>";
echo "Position: " . $err["offset"] . "<br>";
echo
"Statement: " . $err["sqltext"] .
"<br>";
exit;
}

For connection errors, do not pass
a parameter to oci_error():

$conn =
oci_connect("hr", "not_my_password", "localhost/XE");

if (!$conn) {
$m
= oci_error();
echo $m["message"];
exit;
}

How do I call stored procedures from PHP?

The following
example shows how to call a stored procedure and return a value. The stored
procedure is assumed to have been previously created:

create procedure proc1(p1 IN number, p2 OUT number)
as
begin
p2 := p1 + 10;
end;

The PHP code
is:

<?php

// Connect to database
$conn
= oci_connect("hr", "hrpwd", "localhost/XE");
if (!$conn) {
echo
"Connection failed.";
exit;
}

// Call database procedure
$in_var
= 10;
$stid = oci_parse($conn, "begin proc1(:bind1, :bind2);
end;");
oci_bind_by_name($stid, ":bind1",
$in_var);
oci_bind_by_name($stid, ":bind2", $out_var, 32); // 32 is the
return length
oci_execute($stid);
echo "Procedure returned value: " .
$out_var;

// Logoff from
Oracle
oci_free_statement($stid)
oci_close($conn);

?>

Does PHP offer Oracle connection pooling?

The OCI8 1.3
extension supports connection pooling with Oracle 11g Database Resident
Connection Pooling (DRCP). This allows even small computers to maximize use of
database resources. DRCP scales to tens of thousands of connections, allowing
applications to grow without needing special connection management.

PHP
uses a multi-process model so shared connection pooling isn't possible natively
in PHP. You can open "persistent" Oracle connections with the oci_pconnect()
function. 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 such as SQL Relay
can be used to enable connection pooling for Oracle and other databases.

How do I manage connections in OCI8?

Connection management
was greatly improved with the PHP 5.1 OCI8 driver. Many bugs were fixed and
php.ini options for optimizing the behavior of persistent connections were
introduced. The latest OCI8 1.3 driver introduced support for Oracle 11g
Database Resident Connection Pooling (DRCP), allowing connections to be pooled,
even when multiple Apache mid-tiers are used. DRCP scales to tens of thousands
of connections. OCI8 1.3 also handles server downtime better. It can be
installed with earlier versions of PHP.

Optimizing database connections
in a frequently used script can improve performance and prevent resource
shortages. These are general tips for managing connections:

Use OCI8 1.3 with Oracle DRCP. See the whitepaper PHP Scalability and High Availability.

Set oci8.persistent_timeout to expire idle persistent connections. The
expiry is not asynchronous: a PHP script must invoke the PHP engine for the
timeout to be triggered.

Avoid multiple connections to Oracle in a single script.

Use the "persistent" oci_pconnect() call to minimize the number of
physical connection requests to the database. It has some limitations. The DB
sessions will stay open as long as Apache is running, which can consume
resources unless DRCP is available. If the DBA terminates open sessions with
ALTER SYSTEM KILL SESSION or worse, terminates the Oracle shadow process with
an operating system kill command, oci_pconnect() may succeed but subsequent
OCI8 calls may still fail. The Oracle FAN support in OCI8 1.3 mitigates these
particular problems in most cases. It is still good practice for scripts to
check for errors and handle them gracefully since database machines may
possibly go offline in the middle of a script.

Use Oracle Database Resource Manager (which replaces User Profiles) to
limit the resources used by any one connection and to terminate idle
persistent connections.

Use Oracle Net configuration parameters such as SQLNET.EXPIRE_TIME to free up unused database
resources.

If DRCP is not available, use the Apache MaxRequestsPerChild setting to
refresh the Apache processes after they have managed a certain number of
requests, depending on site load. An Apache "graceful restart" nightly will
also close open connections and allow night batch jobs to utilize more
database server memory.

[Acknowledgment: Massimo Squillace contributed
significantly to the original version of this section]

How do I check my SQL statements are working?

Getting no, or
incorrect, results is surprisingly often due to executing the wrong statement.
During development, echo each complete SQL statement from PHP to check it is
well formed and all variables are correctly expanded. Quoting errors or
misunderstandings with PHP's variable syntax in strings can cause incorrect
statements to be executed.

Testing SQL statements in SQL*Plus before
executing them in PHP also helps confirm correctness.

When statements
are entered in tools like SQL*Plus, a semi-colon is often used to tell the tool
that the statement is complete and can now be executed. However the semi-colon
is not considered part of the statement and is not sent to the database. In PHP
do not add a semi-colon to a SQL statement otherwise an Oracle error will occur.
This example is a valid query:

$sql = "SELECT *
FROM EMP_DETAILS_VIEW";

Oracle's inbuilt scripting language
PL/SQL has different syntax to SQL and a final semi-colon is required:

$plsql = "BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!'); END;";

How can I insert strings containing quotes?

Inserting strings
containing single quotes can be handled in several ways:

1. Use bind
variables. This also protects against "SQL Injection" security issues and can
greatly improve database performance. It is also the least likely to cause
coding errors:

$name = "O'Reilly";
$stid =
oci_parse($mycon, 'INSERT INTO CUSTOMERS (NAME) VALUES
(:nm)');
oci_bind_by_name($stid, ':nm', $name,
-1);
oci_execute($stid);

This is the recommended method.

2. Double every single quote:

$name =
"O'Reilly";
$name = str_replace("'", "''",
$name);
$stid = oci_parse($mycon, "INSERT
INTO CUSTOMERS (NAME) VALUES ('".$name."')");

This is not
recommended.

How do I fetch results using associative arrays where the same column
name occurs?

Oracle table column names are cases insensitive by
default but when fetched into associative arrays, PHP stores the names in upper
case so upper case keys must be used to extract the data:

$query = 'select dname from dept';

$stid = oci_parse($conn,
$query);
oci_execute($stid);
while (($row = oci_fetch_array($stid,
OCI_ASSOC))) {
echo $row['DNAME']."<br>\n";
}

If
the table was created with case sensitive column names this may not be true. Use
var_dump($row) to see the column names and data structure actually returned by
oci_fetch_array(). PHP has an array_change_key_case() function to change the
case of array keys if needed.

Table name prefixes are not included in
associative array keys. If a column name is used more than once in a query, use
column aliases to distinguish the columns. Otherwise there will only be one
associative array entry. If the aliases T1N and T2N were not used in the example
below, only results from one of the NAME columns would be returned:

$query = 'select myt1.name as t1n, myt2.name as
t2n from myt1, myt2';

$stid = oci_parse($conn,
$query);
oci_execute($stid);
while (($row = oci_fetch_array($stid,
OCI_ASSOC))) {
echo $row['T1N']."
".$row['T2N']."<br>\n";
}

What is pre-fetching in OCI8?

PHP OCI8 uses prefetching
to return multiple rows in one operation and reduce network traffic to the
database. Setting the prefetch row count with oci_set_prefetch() or the php.ini value oci8.default_prefetch
controls the number of rows that Oracle internally caches. Adjusting the
pre-fetch size can dramatically improve performance. Recent versions of OCI8
default the prefetch size to 100 rows. Older versions use 10. No matter what
value, OCI8 still returns one row at a time to the user script: the buffering is
done internally.
An example that sets the prefetch row count to 60 is:

<?php

$conn = oci_connect('myusername',
'mypassword', 'mydb');

$query = 'SELECT * FROM
EMP_DETAILS_VIEW';

$stid = oci_parse($conn,
$query);
oci_set_prefetch($stid, 60);
oci_execute($stid);
while (($row
= oci_fetch_array($stid, OCI_ASSOC))) {
foreach ($row as $item) {
echo
$item." ";
}
echo
"<br>\n";
}

oci_free_statement($stid);
oci_close($conn);

?>

You
can also tune statement caching with oci8.statement_cache_size to improve
performance of commonly executed statements.

How do I use Oracle bind variables in OCI8?

Bind variables
are strongly recommended because they help database performance and can improve
application security.

Variables used for binding need to be accessible
at the time when oci_execute() is called, not just when oci_bind_by_name() is
executed. PHP's internal variable reference counting and copy-on-write behavior
needs to be taken into account when coding.

If oci_bind_by_name() is
called inside a wrapper function or method and the PHP variable passed to
oci_bind_by_name() is local to the wrapper function there may be a problem. The
variable needs to be in scope when oci_execute() is later called. Otherwise an
Oracle error such as "ORA-01460: unimplemented or unreasonable conversion
requested" may occur or, confusingly, it can appear as if no value was set for
an "OUT" bind variable that you expect returned to the script.

The
sample below is a variation on this. The variable "$val" is local to the
"foreach" command. When oci_execute() is later called, no user data value is
found and the code returns no records:

<?php

$conn = oci_connect('myusername', 'mypassword',
'mydb');

$qs = 'SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = :dname
AND LOCATION_ID = :loc';

$dn = 'IT Support';
$lc = '1700';
$ba =
array(':dname' => $dn, ':loc' => $lc);

$stid = oci_parse($conn,
$qs);

foreach ($ba as $key => $val) {
oci_bind_by_name($stid, $key,
$val, -1);
}

oci_execute($stid);

while (($row =
oci_fetch_array($stid, OCI_ASSOC))) {
foreach ($row as $mv) {
echo $mv."
";
}
echo "<br>\n";
}

?>

Changing the
oci_bind_by_name() call fixes the problem:
. .
.
oci_bind_by_name($stid, $key, $ba[$key], -1);
. .
.

PHP numbers are converted to and from strings when they are
bound. This means the length parameter must generally be given to
oci_bind_by_name() when returning a numeric data value in a bind variable. The
length is the number of digits that will be returned.

Don't pass
variables to oci_bind_by_name() by reference. Sometimes you see this obsolete
syntax in older articles or documentation:

oci_bind_by_name($stid, ":mybindvar", &$val,
12);

It can cause problems. If you have set the E_STRICT error
reporting level, then you will get an error if you attempt this.

How do I upload LOBS with OCI8?

When uploading large objects,
watch out for size limitations either in the HTML script:

<input type="hidden" name="MAX_FILE_SIZE"
value="3000">

or in the LimitRequestBody directive in
httpd.conf, or in php.ini's upload_max_filesize directive.

Here is an
example script that uploads a JPG image into a BLOB column:

<?php

// Sample form to upload and insert an image into
an ORACLE BLOB
// column using PHP5's OCI8 API.
//
// Before running
this script, execute these statements in SQL*Plus:
// drop table btab;
//
create table btab (blobid number, blobdata blob);
//
// This example
uploads an JPG file and inserts it into a BLOB
// column. The image is
retrieved back from the column and displayed.
// Make sure there is no
whitespace before "<?php" else the wrong HTTP
// header will be sent and
the image won't display properly.
//
// Based on a sample originally found
in
//
http://www.php.net/manual/en/function.oci-new-descriptor.php

$myblobid =
1; // should really be a unique id e.g. a sequence number

if
(!isset($_FILES['lob_upload'])) {
?>

<form action="<?php echo
$_SERVER['PHP_SELF']; ?>"
method="POST"
enctype="multipart/form-data">

Image filename:
<input type="file" name="lob_upload">
<input type="submit"
value="Upload">
</form>

<?php
}
else {

$conn
= oci_connect("hr", "hrpwd", "localhost/XE");

// Delete any existing BLOB
so the query at the bottom
// displays the new data

$query = 'DELETE
FROM BTAB WHERE BLOBID = :MYBLOBID';
$stid = oci_parse ($conn,
$query);
oci_bind_by_name($stid, ':MYBLOBID', $myblobid);
$e =
oci_execute($stid, OCI_COMMIT_ON_SUCCESS);
if (!$e)
{
die;
}
oci_free_statement($stid);

// Insert the BLOB from
PHP's tempory upload area

$lob = oci_new_descriptor($conn,
OCI_D_LOB);
$stid = oci_parse($conn, 'INSERT INTO BTAB (BLOBID, BLOBDATA)
'
.'VALUES(:MYBLOBID, EMPTY_BLOB()) RETURNING BLOBDATA INTO
:BLOBDATA');
oci_bind_by_name($stid, ':MYBLOBID',
$myblobid);
oci_bind_by_name($stid, ':BLOBDATA', $lob, -1,
OCI_B_BLOB);
oci_execute($stid, OCI_DEFAULT);

// The function
$lob->savefile(...) reads from the uploaded file.
// If the data was
already in a PHP variable $myv, the
// $lob->save($myv) function could be
used instead.
if ($lob->savefile($_FILES['lob_upload']['tmp_name']))
{
oci_commit($conn);
}
else {
echo "Couldn't upload
Blob\n";
}
$lob->free();
oci_free_statement($stid);

// Now
query the uploaded BLOB and display it

$query = 'SELECT BLOBDATA FROM
BTAB WHERE BLOBID = :MYBLOBID';

$stid = oci_parse ($conn,
$query);
oci_bind_by_name($stid, ':MYBLOBID',
$myblobid);
oci_execute($stid, OCI_DEFAULT);
$arr =
oci_fetch_assoc($stid);
$result = $arr['BLOBDATA']->load();

// If
any text (or whitespace!) is printed before this header is sent,
// the text
won't be displayed and the image won't display properly.
// Comment out this
line to see the text and debug such a problem.
header("Content-type:
image/JPEG");
echo
$result;

oci_free_statement($stid);

oci_close($conn); // log
off
}
?>

Should I upgrade to PHP 5?

PHP 4 is no longer supported and
upgrading is recommended. If PHP 4 is needed for business reasons, I strongly
suggest you upgrade at least the OCI8 extension, using PECL.
Even if you have PHP 5.2, I recommend upgrading to the latest OCI8 version from
PECL. This new version of OCI8 will be included in PHP 5.3 by
default.

The OCI8 extension function names were standardized in PHP 5.
Most changes were the addition of underscores, for example OCIBindByName() is
now oci_bind_by_name(). A couple of function names are very different e.g.
ocilogoff() is now an alias for the new oci_close(). The biggest changes were
simplifications to data fetching functions. Refer to the manual for their new
syntax.

Both old and new names can be used so your scripts do not need
to be recoded. The OCI8
Documentation has entries for old and new names. User contributed comments
for the same functionality may be under either one.

Another name that
has changed in PHP 5 on Windows is the name of the directory containing the
extension DLLs. If starting the web server gives you the error "Unable to load
dynamic library 'c:\PHP\extension\php_oci8.dll'" edit your php.ini file and
change the value of the "extension_dir" directive from "extension" to "ext".

If you are upgrading to PHP 5.1.2 onwards and using the re-factored OCI8
extension for the first time, check the documentation on the new OCI8 php.ini
directives. In particular check oci.old_oci_close_semantics. In the re-factored
extension the oci_close() function will actually close a connection unless the
directive is set. Previously the call was a no-op.

Can I do Operating System Authentication?

OCI8 1.3 supports
Oracle External Authentication. Instead of storing a database username and
password in PHP scripts, credentials can be authenticated by an outside system
such as Oracle Wallet. The operating system user running the Apache process
could be granted read access to the wallet using Access Control Lists.

To
use external authentication, first configure the database to use an external
authentication method. Refer to Oracle documentation for details. In your PHP
script, pass the username as "/", pass an empty password, and set the
OCI_CRED_EXT flag as the fifth parameter in a connection call:

$c = oci_connect("/", "", $db, null,
OCI_CRED_EXT);

Can I do authentication using AS SYSDBA or AS SYSOPER?

It is
possible to connect AS SYSDBA or AS SYSOPER in PHP only with OCI8 extension. The
oci8.privileged_connect parameter must also first be set On. It is most likely
to be useful when PHP is used as a command-line script language. Connection can
be made with:

$c = oci_connect("/", "", null,
null, OCI_SYSDBA);

You can use external authentication with
privileged connections:

$c = oci_connect("/", "",
$db, null, OCI_CRED_EXT+OCI_SYSOPER);

I don't recommend you
enable oci8.privileged_connect as it removes a layer of security. If anyone can
execute arbitrary oci_connect() commands they may be able to gain privileged
access to your database.

Are NCHAR and NCLOB supported in PHP?

There is no NCHAR or
NCLOB support in the OCI8 extension.

The current PHP implementations of
oci8.c always use SQLCS_IMPLICIT for the character set form when it calls
Oracle's OCI. The character set form would need to be SQLCS_NCHAR to support
NCHAR and NCLOB, and other data handling changes may be needed in PHP's code.

What is Zend Core for Oracle?

Zend Core for Oracle (ZCO) is a pre-built PHP 5
distribution that includes the PHP OCI8 1.2 extension and Oracle 10g Instant
Client libraries. It has a web GUI management console.

The product
includes tight integration with Oracle Database client libraries, and native
support for XML and Web services, while also supporting increased adoption of
service-oriented architecture (SOA). It delivers a rapid development and
deployment foundation for database-driven applications.

Oracle and Zend
announced the general availability of Zend Core for Oracle on October 11,
2005.

Support and hot fix updates are available from Zend.

ZCO is
being replaced by Zend Server.

What is Zend Server?

Zend Server is a complete, enterprise-ready Web Application
Server for running and managing PHP applications that require a high level of
reliability, performance and security. It includes out-of-box connectivity for
Oracle databases on Windows and Linux, as well as application monitoring,
problem diagnostics and application performance optimization. It has full
support and software updates available from Zend.

After an extensive
private and public Beta cycle, Zend Server was declared production on April 7,
2009

Zend Server includes the OCI8 1.3 extension (which support Oracle
11g Database Resident Connection Pooling) and bundles the Oracle 11g Instant
Client libraries.

http://static.wetpaint.com/img/bg/1.png?v=20090728162048

pleleu

Latest page update: made by pleleu , Wednesday, 5:42 PM
EDT (about this
update About This Update
Edited by pleleu

1 word added

1 word deleted

view
changes
- complete
history)

Keyword tags:
faq installing php oracle php php
errors

More Info: links to this
page

Share this

Threads for this
page

Post a new
thread

There are no threads for this page.  Be the first
to start a new thread.

Related Content
(what's this?Related ContentThanks to keyword tags, links to related pages and
threads are added to the bottom of your pages. Up to 15 links are shown,
determined by matching tags and by how recently the content was updated; keeping
the most current at the top. Share your feedback on Wetpaint Central.)

Oracle Tweeters

Lista Blogów
o ORACLE

Polskie strony
o ORACLE

Lista
firm wykorzystujących technologie ORACLE

PL Wiki ORACLE

OWSM 11gR1 FAQ

FAQ
- Oracle Load Testing for Web Applications

OWSM 10gR3 FAQ

Installing
Oracle 10gR2 on RHEL AS4 update 4

Oracle
University - Trainings in 11i & R12 Applications/ERP

http://wiki.oracle.com/thread/3009933/Jobs+%40+ORACLE+PH%3A+QA+ANALYST

http://wiki.oracle.com/thread/3009897/Jobs++%40+ORACLE+PH%3A+SR.+QA+ANALYST

Home

Rules of
Conduct

FAQ

Oracle
OpenWorld

Oracle Technology Network

Oracle
Magazine

Applications

E
Business Suite 11i/R12

Business Intelligence

Database

Developer
Tools

Embedded

Enterprise Manager

Fusion
Middleware

Grid Computing

Java

Linux

Oracle and
FOSS

Oracle Secure Enterprise Search

Windows and
.NET

TimesTen

Oracle On
Demand

Oracle VM

Semantic Web

Installation Guides

Security & Compliance

Scripting Languages

PHP

PHP Oracle
FAQ

Python

Ruby on
Rails

Korn
Shell

Oracle
Certification

Acronyms

Oracle
Community

Cetus
Technologies

Spanish
Wiki

Web 2.0
Resource Library

Architect / Architecture Resources

QuestionsOnSOA

Japanese Wiki (日本語)

ISV
Guideline DBA friendly Software

Fusion

Oracle Cloud Computing

Oracle Italy Technology Insight Program

SOA
Events Calendar

Jobs for
Architects

France
Communauté Partenaires Database

Polskie
Portale

Desmistifying
SOA

Industries

High
availability with RAC or Fail Safe

Scripts
Shell desde Procedimiento BD Oracle

Communauté
E2.0 France

LAD PM
Community

E2.0 česká wiki

Oracle Mix
sandbox

Using
Shuttles in Oracle ADF

ERP 2.0

Nuove norme del Garante della Privacy

Debugging
Siebel EIM

Improving
Siebel Configuration Efficiency

AUFO Commission Technologies

Enterprise
Tool

RMAN RECOVERY

enterprise20spain

E2.0 France

Blurbs

KM - Test site

Prime People

Primavera

Job
Oracle Transportation Management

General Business Iberia

TEST - Oracle and ABC Company Page

PL Wiki
ORACLE

new page

(Uncategorized)

start a free website

Contact Wetpaint

Wetpaint
Blog

Help

Sitemap

Feedback

Privacy

Terms of
Use

Code of
Conduct

Other Sites:

Treo
Wiki

The Dell
TechCenter

Sidekick

iPhone Wiki

The HP Community
Wiki.

View more Wetpaint sites.