Getting Started With Oracle (OCI)
As more and more PHP users are using Oracle as their database of choice, it is becoming more important to address issues surrounding PHP’s Oracle Interfaces. The journey begins with a quick look into the fundamentals of Oracle and more specifically in this case, Oracle8 and Oracle8i using PHPs OCI8 Function Library. A brief explanation taken from the PHP Manual (http://www.php.net/manual/ref.oci8.php):
These functions allow you to access Oracle8 and Oracle7 databases. It uses the Oracle8 Call-Interface (OCI8). You will need the Oracle8 client libraries to use this extension.
This extension is more flexible than the standard Oracle extension. It supports binding of global and local PHP variables to Oracle placeholders, has full LOB, FILE and ROWID support and allows you to use user-supplied define variables.
From here on, I will only refer to any Oracle version as “Oracle”. This article is assuming you have already installed PHP and Oracle and have them up and running. Help with Oracle can be found at http://www.oracle.com or http://technet.oracle.com (subscription my be required).
The issues covered in this article are:
1. Connecting to Oracle
2. Creating and exectuting SQL statements
3. Displaying Results
4. limit/offset approach to result ‘pagination’
As an extra bonus, you’ll also see how to perform a limit/offset based display of a large number of query results.
Let’s Begin!
<?php
/*
We’ll start by setting up the groundwork for the display of results using a
limit/offset scheme. The scheme used in this example is an updated version of
Rod Kreisler’s original article
(https://phpbuilder.com/columns/rod20000221.php3).
The updated version can be found at http://php.socket7.net.
if $offset is set below zero (invalid) or empty, set to zero
*/
if (empty($offset) || $offset < 0) {
$offset=0;
}
/*
Now set the limit, or the number of results you would like displayed ‘per page’
of the total result set.
*/
$limit = 3;
/*
Connecting to Oracle. First, you can set your Oracle environment variable
ORACLE_SID. In Windows this can be done using regedit, on UNIX you can use the
putenv() function:
putenv(“ORACLE_SID=ORASID”);
Or, if you would rather, you can reference your ORACLE_SID directly when
connecting to the database. Let’s reference ORACLE_SID directly in this
example.
If you are using PHP as an Apache Module you can use persistent connections.
Using PHP as a CGI, use non-persistant connections. Let’s use non-persistent
connections in this example.
*/
$conn = OCILogon(“user_name”, “password”, “ORASID”);
/*
Error checking. If no database connection was made display an error and exit
the script.
*/
if (!$conn) {
echo “<h1>ERROR – Could not connect to Oracle</h1>”;
exit;
}
/*
If the connection was successful, $conn will now contain the connection
identifier. Otherwise, the script will end and output “Could not connect to
Oracle.”
Now create and parse your SQL statement to count the number of rows to be
returned by a non LIMIT-ed query.
The format is “Select count(*) from table_name” as count() in this case is a SQL
function and will execute in the database, rather than relying on PHP to count
the number of rows returned in a result set.
*/
$sql = “Select count(*) from table_name”;
$stmt = OCIParse($conn, $sql);
if(!$stmt) {
echo “<h1>ERROR – Could not parse SQL statement.</h1>”;
exit;
}
/*
If you entered an invalid SQL statement, or another error occurred, you will see
the “Could not parse SQL” error, otherwise $stmt now contains the Statement
Identifier.
Now execute your parsed statement.
*/
OCIExecute($stmt);
/*
The statement has now been executed, but as you can see, there is no result
identifier returned from OCIExecute(). The Statement Identifer returned by
OCIParse() contains all the information Oracle will need.
Now extract the result of this query. $total_rows[0] will contain the magic
number. If there are no rows returned, display an error and exit the script.
*/
OCIFetchInto($stmt, &$total_rows);
if ( !$total_rows[0] ) {
echo “<h1>Error – no rows returned!</h1>”;
exit;
}
/*
Optional, but nice, this section of code will display a notice similar to “There
are 15 results. Now showing results 4 to 7″.
*/
$begin =($offset+1);
$end = ($begin+($limit-1));
if ($end > $total_rows[0]) {
$end = $total_rows[0];
}
echo “There are <b>$total_rows[0]</b> results.<br>n”;
echo “Now showing results <b>$begin</b> to <b>$end</b>.<br><br>n”;
/*
Time to get down to the real business. Free up the previous statement
identifier then create the main SQL statement, parse it and execute it.
Coding note: Unlike MySQL, Oracle does not support the LIMIT argument in a SQL
statement. As such, there are a few ways to select specific rows of a result
set for output. The best way is to select the results into a temporary table
and “cache” the entire result set. This approach is beyond the scope of this
tutorial. We will take the simpler approach explained just a bit further down.
*/
OCIFreeStatement($stmt);
$sql = “Select * from table_name”;
$stmt = OCIParse($conn, $sql);
if(!$stmt) {
echo “<h1>ERROR – Could not parse SQL statement.</h1>”;
exit;
}
OCIExecute($stmt);
/*
Now display the results. The easiest way to do this is to loop through the
result set. HTML can be worked into the final display, but this example is
very simple, so we won’t use any.
Coding note: As stated above, there is not LIMIT in Oracle, so what we have to
do is cycle through the entire result set pulling out only the results we want
and stopping once we have them all.
This section can be done many ways, and there may be “better” ways to actually
write the code, but I’ve found that the way it is now is not only easy to read,
but it works!
*/
$i=0;
$j=0;
while( OCIFetchInto($stmt, &$result_array) ) {
if ($i>=$offset) {
if ($j <$limit) {
for ($k=0; $k<=count($result_array); $k++) {
echo $result_array[$k].” “;
}
echo “
“;
$j++;
}
}
$i++;
}
echo “
“;
/*
The results have been displayed for the current page. Time to give the
visitor a way to hit the other pages! On with the NEXT/PREV links!
*/
// Calculate total number of pages in result
$pages = intval($total_rows[0]/$limit);
// $pages now contains total number of pages needed
// unless there is a remainder from division
if ($total_rows[0]%$limit) {
// has remainder so add one page
$pages++;
}
// Don’t display PREV link if on first page
if ($offset!=0) {
$prevoffset=$offset-$limit;
echo “<a href=”$PHP_SELF?offset=$prevoffset”><< PREV</a> n”;
}
// Now loop through the pages to create numbered links
// ex. 1 2 3 4 5 NEXT >>
for ($i=1;$i<=$pages;$i++) {
// Check if on current page
if (($offset/$limit) == ($i-1)) {
// $i is equal to current page, so don’t display a link
echo “$i “;
} else {
// $i is NOT the current page, so display a link to page $i
$newoffset=$limit*($i-1);
echo “<a href=”$PHP_SELF?offset=$newoffset”>$i</a> n”;
}
}
// Check to see if current page is last page
if (!((($offset/$limit)+1)==$pages) && $pages!=1) {
// Not on the last page yet, so display a NEXT Link
$newoffset=$offset+$limit;
echo “<a href=”$PHP_SELF?offset=$newoffset”>NEXT >></a><p>n”;
}
/*
We’re all done with Oracle, so free the last statement identifier and logoff
*/
OCIFreeStatement($stmt);
OCILogoff($conn);
?>
Everyone still here? Good. You are done! This approach can be used in many ways with more understanding of the capabilites of Oracle and PHP.
Brett Stimmerman [[email protected]]
