note 93928 deleted from function.oci-set-prefetch by sixd
| From: | sixd@php.net | Date: | Fri, 13 Nov 2009 00:46:14 +0000 |
| Subject: | note 93928 deleted from function.oci-set-prefetch by sixd | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-162842@lists.php.net to get a copy of this message | ||
Note Submitter: sixd at php dot net
----
With Oracle 11gR2, prefetching also works when fetching from REF
CURSORS and nested queries.
To use a non default prefetch count, use oci_set_prefetch. For
example, on the REF CURSOR statement handle:
<?php
$stid = oci_parse($c, "call myproc(:rc)");
$refcur = oci_new_cursor($c);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
oci_set_prefetch($refcur, 200);
oci_execute($refcur);
oci_fetch_all($refcur, $res);
var_dump($res);
?>
REF CURSOR prefetching can be used when PHP is linked with Oracle
11gR2 libraries and connected to previous database versions of the
database.
Nested cursor prefetching requires both the Oracle client libraries
linked with PHP and the database to be Oracle Database 11gR2
From PHP 5.3.2 (PHP OCI8 >= 1.4) the prefetch value may be set to 0.
Previously the lower bound was 1.
If PHP OCI8 fetches from a REF CURSOR and then passes the REF CURSOR
back to a second PL/SQL procedure for further processing, set the REF
CURSOR prefetch count to 0 to avoid rows being "lost" from the result
set.
<?php
// get the REF CURSOR
$stid = oci_parse($c, "call myproc_get_rc(:rc_out)");
$refcur = oci_new_cursor($c);
oci_bind_by_name($stid, ':rc_out', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Fetch one row and display it
oci_set_prefetch($refcur, 0); // don't prefetch rows that aren't displayed
oci_execute($refcur);
$row = oci_fetch_array($refcur);
var_dump($res);
// pass the REF CURSOR to myproc_use_rc() to do more data processing
$stid = oci_parse($c, "call myproc_use_rc(:rc_in)");
oci_bind_by_name($stid, ':rc_in', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// ...
?>