oci_fetch_all
(PHP 5, PHP 7, PECL OCI8 >= 1.1.0)
oci_fetch_all — Fetches multiple rows from a query into a two-dimensional array
Description
$statement
, array &$output
[, int $skip
= 0
[, int $maxrows
= -1
[, int $flags
= OCI_FETCHSTATEMENT_BY_COLUMN
+ OCI_ASSOC
]]] ) : intFetches 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.
-
skip
-
The number of initial rows to discard when fetching the result. The default value is 0, so the first row onwards is returned.
-
maxrows
-
The number of rows to return. The default is -1 meaning return all the rows from
skip
+ 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.
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($item, ENT_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, $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);
?>
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, $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);
?>
Notes
Note:
Using
skip
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
- 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
English translation
You have asked to visit this site in English. For now, only the interface is translated, but not all the content yet.If you want to help me in translations, your contribution is welcome. All you need to do is register on the site, and send me a message asking me to add you to the group of translators, which will give you the opportunity to translate the pages you want. A link at the bottom of each translated page indicates that you are the translator, and has a link to your profile.
Thank you in advance.
Document created the 30/01/2003, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/php-rf-oci-fetch-all.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.