OCI_SET_PREFETCH(3)													       OCI_SET_PREFETCH(3)

oci_set_prefetch - Sets number of rows to be prefetched by queries

SYNOPSIS

       bool oci_set_prefetch (resource	$statement, int  $rows)

DESCRIPTION

	Sets  the number of rows to be buffered by the Oracle Client libraries after a successful query call to oci_execute(3) and for each subse-
       quent internal fetch request to the database. For queries returning a large number of rows, performance can be  significantly  improved	by
       increasing the prefetch count above the default oci8.default_prefetch value.

	Prefetching  is  Oracle's  efficient way of returning more than one data row from the database in each network request. This can result in
       better network and CPU utilization. The buffering of rows is internal to OCI8 and the behavior of  OCI8	fetching  functions  is  unchanged
       regardless of the prefetch count. For example, oci_fetch_row(3) will always return one row. The prefetch buffer is per-statement and is not
       used by re-executed statements or by other connections.

	Call oci_set_prefetch(3) before calling oci_execute(3).

	A tuning goal is to set the prefetch value to a reasonable size for the network and database to handle. For queries returning a very large
       number  of  rows, overall system efficiency might be better if rows are retrieved from the database in several chunks (i.e set the prefetch
       value smaller than the number of rows). This allows the database to handle other users' statements while the PHP script is  processing  the
       current set of rows.

	Query  prefetching was introduced in Oracle 8 i. REF CURSOR prefetching was introduced in Oracle 11 gR2 and occurs when PHP is linked with
       Oracle 11 gR2 (or later) Client libraries. Nested cursor prefetching was introduced in Oracle 11 gR2 and requires both  the  Oracle  Client
       libraries and the database to be version 11 gR2 or greater.

	Prefetching  is  not supported when queries contain LONG or LOB columns. The prefetch value is ignored and single-row fetches will be used
       in all the situations when prefetching is not supported.

	When using Oracle Database 12 c, the prefetch value set by PHP can be overridden by  Oracle's  client  oraaccess.xml  configuration  file.
       Refer to Oracle documentation for more detail.

PARAMETERS

	      o $statement
		-A valid OCI8 statement identifier created by oci_parse(3) and executed by oci_execute(3), or a REF CURSOR statement identifier.

	      o $rows
		- The number of rows to be prefetched, >= 0

RETURN VALUES

	Returns TRUE on success or FALSE on failure.

CHANGELOG

       +------------------------+---------------------------------------------------+
       |	Version 	|						    |
       |			|						    |
       |			|		     Description		    |
       |			|						    |
       +------------------------+---------------------------------------------------+
       | 5.3.2 (PECL OCI8 1.4)	|						    |
       |			|						    |
       |			|      Before this release, $rows must be >= 1.     |
       |			|						    |
       |5.3.0 (PECL OCI8 1.3.4) |						    |
       |			|						    |
       |			|  Before  this release, prefetching was limited to |
       |			| the lesser of $rows rows and 1024 * $rows  bytes. |
       |			| The byte size restriction has now been removed.   |
       |			|						    |
       +------------------------+---------------------------------------------------+
EXAMPLES

       Example #1

	      Changing the default prefetch value for a query

	      <?php

	      $conn = oci_connect('hr', 'welcome', 'localhost/XE');

	      $stid = oci_parse($conn, 'SELECT * FROM myverybigtable');
	      oci_set_prefetch($stid, 300);  // Set before calling oci_execute()
	      oci_execute($stid);

	      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, ENT_QUOTES) : "&nbsp;")."</td>
";
		  }
		  echo "</tr>
";
	      }
	      echo "</table>
";

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

	      ?>

       Example #2

	      Changing the default prefetch for a REF CURSOR fetch

	      <?php
	      /*
		Create the PL/SQL stored procedure as:

		CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
		BEGIN
		  OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
		END;
	      */

	      $conn = oci_connect('hr', 'welcome', 'localhost/XE');

	      $stid = oci_parse($conn, 'BEGIN myproc(:rc); END;');
	      $refcur = oci_new_cursor($conn);
	      oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
	      oci_execute($stid);

	      // Change the prefetch before executing the cursor.
	      // REF CURSOR prefetching works when PHP is linked with Oracle 11gR2 or later Client libraries
	      oci_set_prefetch($refcur, 200);
	      oci_execute($refcur);

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

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

	      ?>

	If  PHP  OCI8  fetches from a REF CURSOR and then passes the REF CURSOR back to a second PL/SQL procedure for further processing, then set
       the REF CURSOR prefetch count to 0 to avoid rows being "lost" from the result set. The prefetch value is the number of extra  rows  fetched
       in each OCI8 internal request to the database, so setting it to 0 means only fetch one row at a time.

       Example #3

	      Setting the prefetch value when passing a REF CURSOR back to Oracle

	      <?php

	      $conn = oci_connect('hr', 'welcome', 'localhost/orcl');

	      // get the REF CURSOR
	      $stid = oci_parse($conn, 'BEGIN myproc(:rc_out); END;');
	      $refcur = oci_new_cursor($conn);
	      oci_bind_by_name($stid, ':rc_out', $refcur, -1, OCI_B_CURSOR);
	      oci_execute($stid);

	      // Display two rows, but don't prefetch any extra rows otherwise
	      // those extra rows would not be passed back to myproc_use_rc().
	      // A prefetch value of 0 is allowed in PHP 5.3.2 and PECL OCI8 1.4
	      oci_set_prefetch($refcur, 0);
	      oci_execute($refcur);
	      $row = oci_fetch_array($refcur);
	      var_dump($row);
	      $row = oci_fetch_array($refcur);
	      var_dump($row);

	      // pass the REF CURSOR to myproc_use_rc() to do more data processing
	      // with the result set
	      $stid = oci_parse($conn, 'begin myproc_use_rc(:rc_in); end;');
	      oci_bind_by_name($stid, ':rc_in', $refcur, -1, OCI_B_CURSOR);
	      oci_execute($stid);

	      ?>

SEE ALSO

       oci8.default_prefetch ini option .

PHP Documentation Group 												       OCI_SET_PREFETCH(3)