Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Oracle Pagination using PHP
I have a PHP script that display all data dynamically from an Oracle table with pagination. But I'm getting an error: Warning: oci_fetch_array() [function.oci-fetch-array]: ORA-01002: fetch out of sequence.
How to fix this?
Here's my PHP script
$sqlQuery = "SELECT * FROM table";
$objParse = oci_parse($conn, $sqlQuery);
oci_execute ($objParse,OCI_DEFAULT);
//Pagination
$Per_Page = 5;
$Num_Rows = oci_fetch_all($objParse, $Result);
$Page = $_GET["Page"];
if(!isset($_GET["Page"])){
$Page=1;
} else {
$Page=$_GET["Page"];
}
$Prev_Page = $Page-1;
$Next_Page = $Page+1;
$Page_Start = (($Per_Page*$Page)-$Per_Page);
if($Num_Rows<=$Per_Page){
$Num_Pages =1;
} else if(($Num_Rows % $Per_Page)==0){
$Num_Pages =($Num_Rows/$Per_Page) ;
} else {
$Num_Pages =($Num_Rows/$Per_Page)+1;
$Num_Pages = (int)$Num_Pages;
}
$Page_End = $Per_Page * $Page;
if ($Page_End > $Num_Rows){
$Page_End = $Num_Rows;
}
//Fetch the column name
echo "<div class=\"datatable\">";
echo "<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">\n";
$ncols = oci_num_fields($objParse);
echo "<thead>";
echo "<tr class=\"title\">\n";
for ($i = 1; $i <= $ncols; ++$i){
$colname = oci_field_name($objParse, $i);
echo " <th><b>".htmlentities($colname, ENT_QUOTES)."</b></th>\n";
}
echo "<th><b>Action</b></th>";
echo "</tr>\n";
echo "</thead>";
for($i=$Page_Start;$i<$Page_End;$i++){
while (($row = oci_fetch_array($objParse, OCI_ASSOC+OCI_RETURN_NULLS)) != false){
echo "<tbody>\n";
echo "<tr>\n";
foreach ($row as $item){
echo "<td>".($item !== null ? htmlentities($item, ENT_QUOTES):" ")."</td>\n";
}
echo "<td><a href='pagination_oracle2.php?EmpID=".$row['EMPLOYEE_ID'][$i]."'>Edit</a></td>";
echo "</tr>\n";
echo "</tbody>\n";
}
}
echo "</table>\n";
?>
<div class="pagination">
Total: <?= $Num_Rows;?> | Record: <?php echo $Num_Pages;?> |
<br>Page:
<?
if($Prev_Page){
echo "<a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> ";
}
for($i=1; $i<=$Num_Pages; $i++){
if($i != $Page){
echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]";
} else {
echo "<b> $i </b>";
}
}
if($Page!=$Num_Pages){
echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next >></a> ";
echo "</div>";
}
oci_close($conn);
Answers
-
I didn't debug all the problems, but one main issue is the way
oci_fetch_all() is initially used to get all the rows. When the
script later calls oci_fetch_array() there is nothing to fetch.
It's too expensive to fetch all the rows only to show one section.
What if the table is huge? You have to architect the solution
under the assumption that you don't know how many rows there
are.
There are two parts to a solution. Look at "Limiting Rows and
Creating Paged Datasets" on p 181 of
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html
If you have Oracle 12c you can use the new, simpler method shown in
the section on the "row_limiting_clause" in the SELECT
documentation http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
An example is:
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 15 ROWS ONLY;
Which ever SQL statement you decide on, the app should use
oci_bind_by_name() for the start and end row values (or page size).
The next part of the solution is to calculate those start and end
rows. There are various solutions out on the web. One is in
http://docs.oracle.com/cd/E17781_01/appdev.112/e18555/ch_five_emp_data.htm#TDPPH159
This general solution in PHP suffers from one main problem: if the
table data is changed by a third party, then the pagination might get
thrown off, since it uses a simple numeric offset from the start of
the table each time a new page is shown. You might be so used to this
behavior that you don't think it is a problem. If you want to resolve
this, you can look at using Flashback
http://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01003
-
what is the use of oci_bind_by_name()?
-