oci_fetch_all

(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

oci_fetch_all — Fetches multiple rows from a query into a two-dimensional array

Descrição

oci_fetch_all(
    resource $statement,
    array &$output,
    int $offset = 0,
    int $limit = -1,
    int $flags = OCI_FETCHSTATEMENT_BY_COLUMN | OCI_ASSOC
): int

Fetches multiple rows from a query into a two-dimensional array. By default, all rows are returned.

This function can be called only once for each query executed with oci_execute().

Parâmetros

statement

Um identificador de declaração OCI8 válida criada por oci_parse() e executada por oci_execute() ou um identificador de declaração REF CURSOR.

output

The variable to contain the returned rows.

LOB columns are returned as strings, where Oracle supports conversion.

See oci_fetch_array() for more information on how data and types are fetched.

offset

The number of initial rows to discard when fetching the result. The default value is 0, so the first row onwards is returned.

limit

The number of rows to return. The default is -1 meaning return all the rows from offset + 1 onwards.

flags

Parameter flags indicates the array structure and whether associative arrays should be used.

**oci_fetch_all()** Array Structure Modes
Constant	Description
`OCI_FETCHSTATEMENT_BY_ROW`	The outer array will contain one sub-array per query row.
`OCI_FETCHSTATEMENT_BY_COLUMN`	The outer array will contain one sub-array per query column. This is the default.

Arrays can be indexed either by column heading or numerically. Only one index mode will be returned.

**oci_fetch_all()** Array Index Modes
Constant	Description
`OCI_NUM`	Numeric indexes are used for each column's array.
`OCI_ASSOC`	Associative indexes are used for each column's array. This is the default.

Use the addition operator "+" to choose a combination of array structure and index modes.

Oracle's default, non-case sensitive column names will have uppercase array keys. Case-sensitive column names will have array keys using the exact column case. Use var_dump() on output to verify the appropriate case to use for each query.

Queries that have more than one column with the same name should use column aliases. Otherwise only one of the columns will appear in an associative array.

Valor Retornado

Returns the number of rows in output, which may be 0 or more.

Exemplos

Exemplo #1 oci_fetch_all() example

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3');
oci_execute($stid);

$nrows = oci_fetch_all($stid, $res);

echo "$nrows rows fetched<br>\n";
var_dump($res);

// var_dump output is:
//    2 rows fetched
//    array(2) {
//      ["POSTAL_CODE"]=>
//      array(2) {
//        [0]=>
//        string(6) "00989x"
//        [1]=>
//        string(6) "10934x"
//      }
//      ["CITY"]=>
//      array(2) {
//        [0]=>
//        string(4) "Roma"
//        [1]=>
//        string(6) "Venice"
//      }
//    }

// Pretty-print the results
echo "<table border='1'>\n";
foreach ($res as $col) {
    echo "<tr>\n";
    foreach ($col as $item) {
        echo "    <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "")."</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

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

?>

Exemplo #2 oci_fetch_all() example with OCI_FETCHSTATEMENT_BY_ROW

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3');
oci_execute($stid);

$nrows = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);

echo "$nrows rows fetched<br>\n";
var_dump($res);

// Output is:
//    2 rows fetched
//    array(2) {
//      [0]=>
//      array(2) {
//        ["POSTAL_CODE"]=>
//        string(6) "00989x"
//        ["CITY"]=>
//        string(4) "Roma"
//      }
//      [1]=>
//      array(2) {
//        ["POSTAL_CODE"]=>
//        string(6) "10934x"
//        ["CITY"]=>
//        string(6) "Venice"
//      }
//    }

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

?>

Exemplo #3 oci_fetch_all() with OCI_NUM

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3');
oci_execute($stid);

$nrows = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM);

echo "$nrows rows fetched<br>\n";
var_dump($res);

// Output is:
//    2 rows fetched
//    array(2) {
//      [0]=>
//      array(2) {
//        [0]=>
//        string(6) "00989x"
//        [1]=>
//        string(4) "Roma"
//      }
//      [1]=>
//      array(2) {
//        [0]=>
//        string(6) "10934x"
//        [1]=>
//        string(6) "Venice"
//      }
//    }

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

?>

Notas

Nota:
Using offset is very inefficient. All the rows to be skipped are included in the result set that is returned from the database to PHP. They are then discarded. It is more efficient to use SQL to restrict the offset and range of rows in the query. See oci_fetch_array() for an example.

Nota:
Queries that return a large number of rows can be more memory efficient if a single-row fetching function like oci_fetch_array() is used.

Nota:
Para consultas que retornam um grande número de linhas, o desempenho pode ser melhorado significativamente aumentando oci8.default_prefetch ou usando oci_set_prefetch().

Nota:
Will not return rows from Oracle Database 12c Implicit Result Sets. Use oci_fetch_array() instead.

Veja Também

oci_fetch() - Fetches the next row from a query into internal buffers
oci_fetch_array() - Returns the next row from a query as an associative or numeric array
oci_fetch_assoc() - Returns the next row from a query as an associative array
oci_fetch_object() - Returns the next row from a query as an object
oci_fetch_row() - Returns the next row from a query as a numeric array
oci_set_prefetch() - Sets number of rows to be prefetched by queries

add a note

User Contributed Notes 2 notes

down

eustaquiorangel at gmail dot com ¶

10 years ago


Beware that only numerically indexed results will be returned if
OCI_NUM is used.

down

david at boeke dot com ¶

20 years ago


The Skip and MaxRows parameters were not added until version 4.2.1.

Previous versions of php used this syntax:



           int ocifetchstatement ( resource stmt, array &output)



The function also took a third parameter that was not documented.  ( I assume that it was a flag)

add a note