Used to fetch consectutive sets of query results after the execution of a stored or anonymous Oracle PL/SQL block where that block returns query results with Oracle's DBMS_SQL.RETURN_RESULT PL/SQL function. This allows PL/SQL blocks to easily return query results.
The child statement can be used with any of the OCI8 fetching functions: oci_fetch(), oci_fetch_all(), oci_fetch_array(), oci_fetch_object(), oci_fetch_assoc() or oci_fetch_row()
Child statements inherit their parent statement's prefetch value, or it can be explicitly set with oci_set_prefetch().
A valid OCI8 statement identifier created by oci_parse() and executed by oci_execute(). The statement identifier may or may not be associated with a SQL statement that returns Implicit Result Sets.
Returns a statement handle for the next child statement available on statement
. Returns FALSE
when child statements do not exist, or all child statements have been returned by previous calls to oci_get_implicit_resultset().
For queries returning a large number of rows, performance can be significantly improved by increasing oci8.default_prefetch or using oci_set_prefetch().
<?php $conn = oci_connect('hr', 'welcome', 'localhost/pdborcl'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $sql = 'DECLARE c1 SYS_REFCURSOR; BEGIN OPEN c1 FOR SELECT city, postal_code FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); OPEN c1 FOR SELECT country_id FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); END;'; $stid = oci_parse($conn, $sql); oci_execute($stid); while (($stid_c = oci_get_implicit_resultset($stid))) { echo "<h2>New Implicit Result Set:</h2>\n"; echo "<table>\n"; while (($row = oci_fetch_array($stid_c, OCI_ASSOC+OCI_RETURN_NULLS)) != false) { echo "<tr>\n"; foreach ($row as $item) { echo " <td>".($item!==null?htmlentities($item, ENT_QUOTES|ENT_SUBSTITUTE):" ")."</td>\n"; } echo "</tr>\n"; } echo "</table>\n"; } // Output is: // New Implicit Result Set: // Beijing 190518 // Bern 3095 // Bombay 490231 // New Implicit Result Set: // CN // CH // IN oci_free_statement($stid); oci_close($conn); ?>
<?php $conn = oci_connect('hr', 'welcome', 'localhost/pdborcl'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $sql = 'DECLARE c1 SYS_REFCURSOR; BEGIN OPEN c1 FOR SELECT city, postal_code FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); OPEN c1 FOR SELECT country_id FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); END;'; $stid = oci_parse($conn, $sql); oci_execute($stid); $stid_1 = oci_get_implicit_resultset($stid); $stid_2 = oci_get_implicit_resultset($stid); $row = oci_fetch_array($stid_1, OCI_ASSOC+OCI_RETURN_NULLS); var_dump($row); $row = oci_fetch_array($stid_2, OCI_ASSOC+OCI_RETURN_NULLS); var_dump($row); $row = oci_fetch_array($stid_1, OCI_ASSOC+OCI_RETURN_NULLS); var_dump($row); $row = oci_fetch_array($stid_2, OCI_ASSOC+OCI_RETURN_NULLS); var_dump($row); // Output is: // array(2) { // ["CITY"]=> // string(7) "Beijing" // ["POSTAL_CODE"]=> // string(6) "190518" // } // array(1) { // ["COUNTRY_ID"]=> // string(2) "CN" // } // array(2) { // ["CITY"]=> // string(4) "Bern" // ["POSTAL_CODE"]=> // string(4) "3095" // } // array(1) { // ["COUNTRY_ID"]=> // string(2) "CH" // } oci_free_statement($stid); oci_close($conn); ?>
<?php $conn = oci_connect('hr', 'welcome', 'localhost/pdborcl'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $sql = 'DECLARE c1 SYS_REFCURSOR; BEGIN OPEN c1 FOR SELECT city, postal_code FROM locations ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); END;'; $stid = oci_parse($conn, $sql); oci_execute($stid); $stid_c = oci_get_implicit_resultset($stid); oci_set_prefetch($stid_c, 200); // Set the prefetch before fetching from the child statement echo "<table>\n"; while (($row = oci_fetch_array($stid_c, OCI_ASSOC+OCI_RETURN_NULLS)) != false) { echo "<tr>\n"; foreach ($row as $item) { echo " <td>".($item!==null?htmlentities($item, ENT_QUOTES|ENT_SUBSTITUTE):" ")."</td>\n"; } echo "</tr>\n"; } echo "</table>\n"; oci_free_statement($stid); oci_close($conn); ?>
All results from all queries are returned consecutively.
<?php $conn = oci_connect('hr', 'welcome', 'localhost/pdborcl'); if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } $sql = 'DECLARE c1 SYS_REFCURSOR; BEGIN OPEN c1 FOR SELECT city, postal_code FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); OPEN c1 FOR SELECT country_id FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); END;'; $stid = oci_parse($conn, $sql); oci_execute($stid); // Note: oci_fetch_all and oci_fetch() cannot be used in this manner echo "<table>\n"; while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) { echo "<tr>\n"; foreach ($row as $item) { echo " <td>".($item!==null?htmlentities($item, ENT_QUOTES|ENT_SUBSTITUTE):" ")."</td>\n"; } echo "</tr>\n"; } echo "</table>\n"; // Output is: // Beijing 190518 // Bern 3095 // Bombay 490231 // CN // CH // IN oci_free_statement($stid); oci_close($conn); ?>
Please login to continue.