Oracle Pagination using PHP

2611484

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);

Christopher Jones-Oracle

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

2611484

what is the use of oci_bind_by_name()?

Christopher Jones-Oracle

See p164 of The Underground PHP and Oracle Manual | Homepage