oci_get_implicit_resultset

(PHP 5 >= 5.6.0, PHP 7, PECL OCI8 >= 2.0.0)

oci_get_implicit_resultsetReturns the next child statement resource from a parent statement resource that has Oracle Database 12c Implicit Result Sets

Description

oci_get_implicit_resultset ( resource $statement ) : resource

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().

Parameters

statement

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.

Return Values

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().

Examples

Example #1 Fetching Implicit Result Sets in a loop

<?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_cOCI_ASSOC+OCI_RETURN_NULLS)) != false) {
        echo 
"<tr>\n";
        foreach (
$row as $item) {
            echo 
"  <td>".($item!==null?htmlentities($itemENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;")."</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);

?>

Example #2 Getting child statement handles individually

<?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_1OCI_ASSOC+OCI_RETURN_NULLS);
var_dump($row);
$row oci_fetch_array($stid_2OCI_ASSOC+OCI_RETURN_NULLS);
var_dump($row);
$row oci_fetch_array($stid_1OCI_ASSOC+OCI_RETURN_NULLS);
var_dump($row);
$row oci_fetch_array($stid_2OCI_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);

?>

Example #3 Explicitly setting the Prefetch Count

<?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_c200);   // Set the prefetch before fetching from the child statement
echo "<table>\n";
while ((
$row oci_fetch_array($stid_cOCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo 
"<tr>\n";
    foreach (
$row as $item) {
        echo 
"  <td>".($item!==null?htmlentities($itemENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;")."</td>\n";
    }
    echo 
"</tr>\n";
}
echo 
"</table>\n";

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

?>

Example #4 Implicit Result Set example without using oci_get_implicit_resultset()

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($stidOCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo 
"<tr>\n";
    foreach (
$row as $item) {
        echo 
"  <td>".($item!==null?htmlentities($itemENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;")."</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);

?>

Notes

Note:

For queries returning a large number of rows, performance can be significantly improved by increasing oci8.default_prefetch or using oci_set_prefetch().