oci_fetch_all

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

oci_fetch_allFetches multiple rows from a query into a two-dimensional array

Description

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

Parameters

statement

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

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.

Return Values

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

Examples

Example #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($itemENT_QUOTES) : "")."</td>\n";
    }
    echo 
"</tr>\n";
}
echo 
"</table>\n";

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

?>

Example #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$resnullnullOCI_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);

?>

Example #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$resnullnullOCI_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);

?>

Notes

Note:

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.

Note:

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.

Note:

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

Note:

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

See Also

Here you can write a comment


Please enter at least 10 characters.
Loading... Please wait.
* Pflichtangabe
There are no comments available yet.

PHP cURL Tutorial: Using cURL to Make HTTP Requests

cURL is a powerful PHP extension that allows you to communicate with different servers using various protocols, including HTTP, HTTPS, FTP, and more. ...

TheMax

Autor : TheMax
Category: PHP-Tutorials

Midjourney Tutorial - Instructions for beginners

There is an informative video about Midjourney, the tool for creating digital images using artificial intelligence, entitled "Midjourney tutorial in German - instructions for beginners" ...

Mike94

Autor : Mike94
Category: KI Tutorials

Basics of views in MySQL

Views in a MySQL database offer the option of creating a virtual table based on the result of an SQL query. This virtual table can be queried like a normal table without changing the underlying data. ...

admin

Autor : admin
Category: mySQL-Tutorials

Publish a tutorial

Share your knowledge with other developers worldwide

Share your knowledge with other developers worldwide

You are a professional in your field and want to share your knowledge, then sign up now and share it with our PHP community

learn more

Publish a tutorial