|
PHP is a 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, such as hello.php:
<html>
<p>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 the 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 cannot
help you.
Back to Top
PHP offers two extension modules that can be used to connect
to Oracle:
- The normal Oracle functions (ORA)
- The Oracle Call-Interface functions (OCI)
Note: OCI should be used whenever possible
since it is optimized and provides more options. For example,
ORA doesn't include support for CLOBs, BLOBs, BFILEs, ROWIDs,
etc.
Follow these steps to prepare your PHP installation
for connecting to Oracle databases:
- Download PHP from www.php.net, install as directing in the install.txt
file, and test that everything is working.
- Install the Oracle Client (or Server) software on your machine
and configure SQL*Net to connect to your database(s).
- Edit your php.ini file and uncomment the following two lines
(only if your version shipped with precompiled extension modules):
;extension = php_oci8.dll
;extension = php_oracle.dll
... otherwise, compile PHP with the following options:
--with-oracle=/path/to/oracle/home/dir
--with-oci8=/path/to/oracle/home/dir
- Ensure that your extension_dir parameter (in php.ini) points
to the location where the above extension files reside.
- Write a small program to test connectivity—see the next question.
Using the OCI Extension Module:
<?php
if ($c=OCILogon("scott", "tiger", "orcl")) {
echo "Successfully connected to Oracle.\n";
OCILogoff($c);
} else {
$err = OCIError();
echo "Oracle Connect Error " . $err[text];
}
?>
Using the ORA Extension Module:
<?php
if ($c=ora_logon("scott@orcl","tiger")) {
echo "Successfully connected to Oracle.\n";
ora_commitoff($c);
ora_logoff($c);
} else {
echo "Oracle Connect Error " . ora_error();
}
?>
Note: You might
want to set your Oracle environment from within PHP before connecting.
See this example:
<?php
PutEnv("ORACLE_SID=ORCL");
PutEnv("ORACLE_HOME=/app/oracle/product/9.2.0");
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. You can use the OCINLogon()
function to ensure one gets a new session. Use the OCIPLogon()
function to make persistent connections.
PHP is not using the correct extension module. Try compiling PHP
with the following options:
--with-oracle=/path/to/oracle/home/dir
--with-oci8=/path/to/oracle/home/dir
On Windows systems you can just uncomment the following lines in
the php.ini file:
;extension = php_oci8.dll
;extension = php_oracle.dll
The following example demonstrates
how data can be SELECTed and manipulated via INSERT,
UPDATE and DELETE statements:
<?php
$c=OCILogon("scott", "tiger", "orcl");
if ( ! $c ) {
echo "Unable to connect: " . var_dump( OCIError() );
die();
}
// Drop old table...
$s = OCIParse($c, "drop table tab1");
OCIExecute($s, OCI_DEFAULT);
// Create new table...
$s = OCIParse($c, "create table tab1 (col1 number, col2 varchar2(30))");
OCIExecute($s, OCI_DEFAULT);
// Insert data into table...
$s = OCIParse($c, "insert into tab1 values (1, 'Frank')");
OCIExecute($s, OCI_DEFAULT);
// Insert data using bind variables...
$var1 = 2;
$var2 = "Scott";
$s = OCIParse($c, "insert into tab1 values (:bind1, :bind2)");
OCIBindByName($s, ":bind1", $var1);
OCIBindByName($s, ":bind2", $var2);
OCIExecute($s, OCI_DEFAULT);
// Select Data...
$s = OCIParse($c, "select * from tab1");
OCIExecute($s, OCI_DEFAULT);
while (OCIFetch($s)) {
echo "COL1=" . ociresult($s, "COL1") .
", COL2=" . ociresult($s, "COL2") . "\n";
}
// Commit to save changes...
OCICommit($c);
// Logoff from Oracle...
OCILogoff($c);
?>
When using the OCI Extension Module,
PHP will commit whenever ociexecute() returns successfully.
One can control this behavior by specifying OCI_COMMIT_ON_SUCCESS
(the default) or OCI_DEFAULT as the second parameter to the ociexecute()
function call. OCI_DEFAULT can be used to prevent statements from
being auto-committed. The OCICommit() and OCIRollback()
functions can then be used to control the transaction.
Note that when OCI_DEFAULT is used on any statement handle, it
is inherited by the other statement handles for the connection.
You cannot use a mix of autocommit/explicit commit on the same connection
handle. If you want to do that, you need to use ociNLogon()
to get a separate handle.
The ORA Extension Module supports an autocommit mode. Use
the ORA_CommitOn() and ORA_CommitOff() functions
to toggle between autocommit mode and normal mode. When in normal
mode (ORA_CommitOff), you can use the ORA_Commit()
and ORA_Rollback() functions to control transactions.
If you don't commit or roll back at the end of a script, PHP
will do an implicit commit. This is consistent with the way SQL*Plus
works.
When using the OCI extension Module,
the OCIError() function can be used to obtain an array
with error code, message, offset, and SQL text. You can also obtain
the error for a specific session or cursor by supplying the appropriate
handle as an argument to OCIError(). Without any arguments,
OCIError() will return the last encountered error.
<?php
$err = OCIError();
var_dump($err);
print "\nError code = " . $err[code];
print "\nError message = " . $err[message];
print "\nError position = " . $err[offset];
print "\nSQL Statement = " . $err[sqltext];
?>
When using the ORA Extension Module, You can use the ora_error()
and ora_errorcode() functions to report errors: <?php
print "\nError code = " . ora_errorcode();
print "\nError message = " . ora_error();
?>
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=OCILogon("scott", "tiger", "orcl");
if ( ! $c ) {
echo "Unable to connect: " . var_dump( OCIError() );
die();
}
// Create database procedure...
$s = OCIParse($c, "create procedure proc1(p1 IN number, p2 OUT number) as " .
"begin" .
" p2 := p1 + 10;" .
"end;");
OCIExecute($s, OCI_DEFAULT);
// Call database procedure...
$in_var = 10;
$s = OCIParse($c, "begin proc1(:bind1, :bind2); end;");
OCIBindByName($s, ":bind1", $in_var);
OCIBindByName($s, ":bind2", $out_var, 32); // 32 is the return length
OCIExecute($s, OCI_DEFAULT);
echo "Procedure returned value: " . $out_var;
// Logoff from Oracle...
OCILogoff($c);
?>
Unfortunately, PHP does not offer
connection pooling. You can open persistent Oracle connections
with the ora_plogon()and OCIPLogon() 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 such as SQL Relay can be used to enable connection
pooling for Oracle and other databases.
|
