<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Fetches multiple rows from a query into a two-dimensional array</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.oci-execute.html">oci_execute</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.oci-fetch-array.html">oci_fetch_array</a></div>
<div class="up"><a href="ref.oci8.html">OCI8 Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="function.oci-fetch-all" class="refentry">
<div class="refnamediv">
<h1 class="refname">oci_fetch_all</h1>
<p class="verinfo">(PHP 5, PECL OCI8 >= 1.1.0)</p><p class="refpurpose"><span class="refname">oci_fetch_all</span> — <span class="dc-title">Fetches multiple rows from a query into a two-dimensional array</span></p>
</div>
<div class="refsect1 description" id="refsect1-function.oci-fetch-all-description">
<h3 class="title">Description</h3>
<div class="methodsynopsis dc-description">
<span class="type">int</span> <span class="methodname"><strong>oci_fetch_all</strong></span>
( <span class="methodparam"><span class="type">resource</span> <code class="parameter">$statement</code></span>
, <span class="methodparam"><span class="type">array</span> <code class="parameter reference">&$output</code></span>
[, <span class="methodparam"><span class="type">int</span> <code class="parameter">$skip</code><span class="initializer"> = 0</span></span>
[, <span class="methodparam"><span class="type">int</span> <code class="parameter">$maxrows</code><span class="initializer"> = -1</span></span>
[, <span class="methodparam"><span class="type">int</span> <code class="parameter">$flags</code><span class="initializer"> = <strong><code>OCI_FETCHSTATEMENT_BY_COLUMN</code></strong> + <strong><code>OCI_ASSOC</code></strong></span></span>
]]] )</div>
<p class="para rdfs-comment">
Fetches multiple rows from a query into a two-dimensional array.
By default, all rows are returned.
</p>
<p class="para">
This function can be called only once for each query executed
with <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span>.
</p>
</div>
<div class="refsect1 parameters" id="refsect1-function.oci-fetch-all-parameters">
<h3 class="title">Parameters</h3>
<p class="para">
<dl>
<dt>
<span class="term"><em><code class="parameter">statement</code></em></span>
<dd>
<p class="para">A valid OCI8 statement
identifier created by <span class="function"><a href="function.oci-parse.html" class="function">oci_parse()</a></span> and executed
by <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span>, or a <em>REF
CURSOR</em> statement identifier.</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">output</code></em></span>
<dd>
<p class="para">
The variable to contain the returned rows.
</p>
<p class="para">
LOB columns are returned as strings, where Oracle supports
conversion.
</p>
<p class="para">
See <span class="function"><a href="function.oci-fetch-array.html" class="function">oci_fetch_array()</a></span> for more information
on how data and types are fetched.
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">skip</code></em></span>
<dd>
<p class="para">
The number of initial rows to discard when fetching the
result. The default value is 0, so the first row onwards is
returned.
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">maxrows</code></em></span>
<dd>
<p class="para">
The number of rows to return. The default is -1 meaning return
all the rows from <em><code class="parameter">skip</code></em> + 1 onwards.
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">flags</code></em></span>
<dd>
<p class="para">
Parameter <em><code class="parameter">flags</code></em> indicates the array
structure and whether associative arrays should be used.
<table class="doctable table">
<caption><strong> <span class="function"><strong>oci_fetch_all()</strong></span> Array Structure Modes</strong></caption>
<thead>
<tr>
<th>Constant</th>
<th>Description</th>
</tr>
</thead>
<tbody class="tbody">
<tr>
<td><strong><code>OCI_FETCHSTATEMENT_BY_ROW</code></strong></td>
<td>The outer array will contain one sub-array per query
row.</td>
</tr>
<tr>
<td><strong><code>OCI_FETCHSTATEMENT_BY_COLUMN</code></strong></td>
<td>The outer array will contain one sub-array per query
column. This is the default.</td>
</tr>
</tbody>
</table>
</p>
<p class="para">
Arrays can be indexed by column heading or numerically.
<table class="doctable table">
<caption><strong> <span class="function"><strong>oci_fetch_all()</strong></span> Array Index Modes</strong></caption>
<thead>
<tr>
<th>Constant</th>
<th>Description</th>
</tr>
</thead>
<tbody class="tbody">
<tr>
<td><strong><code>OCI_NUM</code></strong></td>
<td>Numeric indexes are used for each column's array.</td>
</tr>
<tr>
<td><strong><code>OCI_ASSOC</code></strong></td>
<td>Associative indexes are used for each column's
array. This is the default.</td>
</tr>
</tbody>
</table>
</p>
<p class="para">
Use the addition operator "+" to choose a combination
of array structure and index modes.
</p>
<p class="para">
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 <span class="function"><a href="function.var-dump.html" class="function">var_dump()</a></span>
on <em><code class="parameter">output</code></em> to verify the appropriate case
to use for each query.
</p>
<p class="para">
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.
</p>
</dd>
</dt>
</dl>
</p>
</div>
<div class="refsect1 returnvalues" id="refsect1-function.oci-fetch-all-returnvalues">
<h3 class="title">Return Values</h3>
<p class="para">
Returns the number of rows in <em><code class="parameter">output</code></em>, which
may be 0 or more, or <strong><code>FALSE</code></strong> on failure.
</p>
</div>
<div class="refsect1 examples" id="refsect1-function.oci-fetch-all-examples">
<h3 class="title">Examples</h3>
<p class="para">
<div class="example" id="example-1978">
<p><strong>Example #1 <span class="function"><strong>oci_fetch_all()</strong></span> example</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$e </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$e</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">], </span><span style="color: #0000BB">ENT_QUOTES</span><span style="color: #007700">), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$nrows </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br />echo </span><span style="color: #DD0000">"</span><span style="color: #0000BB">$nrows</span><span style="color: #DD0000"> rows fetched<br>\n"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// var_dump output is:<br />// 2 rows fetched<br />// array(2) {<br />// ["POSTAL_CODE"]=><br />// array(2) {<br />// [0]=><br />// string(6) "00989x"<br />// [1]=><br />// string(6) "10934x"<br />// }<br />// ["CITY"]=><br />// array(2) {<br />// [0]=><br />// string(4) "Roma"<br />// [1]=><br />// string(6) "Venice"<br />// }<br />// }<br /><br />// Pretty-print the results<br /></span><span style="color: #007700">echo </span><span style="color: #DD0000">"<table border='1'>\n"</span><span style="color: #007700">;<br />foreach (</span><span style="color: #0000BB">$res </span><span style="color: #007700">as </span><span style="color: #0000BB">$col</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">"<tr>\n"</span><span style="color: #007700">;<br /> foreach (</span><span style="color: #0000BB">$col </span><span style="color: #007700">as </span><span style="color: #0000BB">$item</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">" <td>"</span><span style="color: #007700">.(</span><span style="color: #0000BB">$item </span><span style="color: #007700">!== </span><span style="color: #0000BB">null </span><span style="color: #007700">? </span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$item</span><span style="color: #007700">, </span><span style="color: #0000BB">ENT_QUOTES</span><span style="color: #007700">) : </span><span style="color: #DD0000">""</span><span style="color: #007700">).</span><span style="color: #DD0000">"</td>\n"</span><span style="color: #007700">;<br /> }<br /> echo </span><span style="color: #DD0000">"</tr>\n"</span><span style="color: #007700">;<br />}<br />echo </span><span style="color: #DD0000">"</table>\n"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1979">
<p><strong>Example #2 <span class="function"><strong>oci_fetch_all()</strong></span> example with <strong><code>OCI_FETCHSTATEMENT_BY_ROW</code></strong></strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$e </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$e</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">], </span><span style="color: #0000BB">ENT_QUOTES</span><span style="color: #007700">), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$nrows </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$res</span><span style="color: #007700">, </span><span style="color: #0000BB">null</span><span style="color: #007700">, </span><span style="color: #0000BB">null</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_FETCHSTATEMENT_BY_ROW</span><span style="color: #007700">);<br /><br />echo </span><span style="color: #DD0000">"</span><span style="color: #0000BB">$nrows</span><span style="color: #DD0000"> rows fetched<br>\n"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Output is:<br />// 2 rows fetched<br />// array(2) {<br />// [0]=><br />// array(2) {<br />// ["POSTAL_CODE"]=><br />// string(6) "00989x"<br />// ["CITY"]=><br />// string(4) "Roma"<br />// }<br />// [1]=><br />// array(2) {<br />// ["POSTAL_CODE"]=><br />// string(6) "10934x"<br />// ["CITY"]=><br />// string(6) "Venice"<br />// }<br />// }<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1980">
<p><strong>Example #3 <span class="function"><strong>oci_fetch_all()</strong></span> with <strong><code>OCI_NUM</code></strong></strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$e </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$e</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">], </span><span style="color: #0000BB">ENT_QUOTES</span><span style="color: #007700">), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'SELECT POSTAL_CODE, CITY FROM locations WHERE ROWNUM < 3'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$nrows </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$res</span><span style="color: #007700">, </span><span style="color: #0000BB">null</span><span style="color: #007700">, </span><span style="color: #0000BB">null</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_FETCHSTATEMENT_BY_ROW </span><span style="color: #007700">+ </span><span style="color: #0000BB">OCI_NUM</span><span style="color: #007700">);<br /><br />echo </span><span style="color: #DD0000">"</span><span style="color: #0000BB">$nrows</span><span style="color: #DD0000"> rows fetched<br>\n"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Output is:<br />// 2 rows fetched<br />// array(2) {<br />// [0]=><br />// array(2) {<br />// [0]=><br />// string(6) "00989x"<br />// [1]=><br />// string(4) "Roma"<br />// }<br />// [1]=><br />// array(2) {<br />// [0]=><br />// string(6) "10934x"<br />// [1]=><br />// string(6) "Venice"<br />// }<br />// }<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
</div>
<div class="refsect1 notes" id="refsect1-function.oci-fetch-all-notes">
<h3 class="title">Notes</h3>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">
Using <em><code class="parameter">skip</code></em> 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 <span class="function"><a href="function.oci-fetch-array.html" class="function">oci_fetch_array()</a></span> for
an example.
</p>
</p></blockquote>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">
Queries that return a large number of rows can be more memory
efficient if a single-row fetching function
like <span class="function"><a href="function.oci-fetch-array.html" class="function">oci_fetch_array()</a></span> is used.
</p>
</p></blockquote>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">For
queries returning a large number of rows, performance can be
significantly improved by
increasing <a href="oci8.configuration.html#ini.oci8.default-prefetch" class="link">oci8.default_prefetch</a>
or using <span class="function"><a href="function.oci-set-prefetch.html" class="function">oci_set_prefetch()</a></span>.
</p>
</p></blockquote>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">
Will not return rows from Oracle Database 12<em class="emphasis">c</em>
Implicit Result Sets. Use <span class="function"><a href="function.oci-fetch-array.html" class="function">oci_fetch_array()</a></span>
instead.
</p>
</p></blockquote>
</div>
<div class="refsect1 seealso" id="refsect1-function.oci-fetch-all-seealso">
<h3 class="title">See Also</h3>
<p class="para">
<ul class="simplelist">
<li class="member"> <span class="function"><a href="function.oci-fetch.html" class="function" rel="rdfs-seeAlso">oci_fetch()</a> - Fetches the next row from a query into internal buffers</span></li>
<li class="member"> <span class="function"><a href="function.oci-fetch-array.html" class="function" rel="rdfs-seeAlso">oci_fetch_array()</a> - Returns the next row from a query as an associative or numeric array</span></li>
<li class="member"> <span class="function"><a href="function.oci-fetch-assoc.html" class="function" rel="rdfs-seeAlso">oci_fetch_assoc()</a> - Returns the next row from a query as an associative array</span></li>
<li class="member"> <span class="function"><a href="function.oci-fetch-object.html" class="function" rel="rdfs-seeAlso">oci_fetch_object()</a> - Returns the next row from a query as an object</span></li>
<li class="member"> <span class="function"><a href="function.oci-fetch-row.html" class="function" rel="rdfs-seeAlso">oci_fetch_row()</a> - Returns the next row from a query as a numeric array</span></li>
<li class="member"> <span class="function"><a href="function.oci-set-prefetch.html" class="function" rel="rdfs-seeAlso">oci_set_prefetch()</a> - Sets number of rows to be prefetched by queries</span></li>
</ul>
</p>
</div>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.oci-execute.html">oci_execute</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.oci-fetch-array.html">oci_fetch_array</a></div>
<div class="up"><a href="ref.oci8.html">OCI8 Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
