|
oci_fetch_all
Fetches multiple rows from a query into a two-dimensional array
Description
int oci_fetch_all( resource $statement , array &$output , int $offset = 0, int $limit = -1, int $flags = OCI_FETCHSTATEMENT_BY_COLUMN | OCI_ASSOC )
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($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 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
- oci_fetch
- oci_fetch_array
- oci_fetch_assoc
- oci_fetch_object
- oci_fetch_row
- oci_set_prefetch
|