oci_fetch_assoc

oci_fetch_all

Last updated: Fri, 10 Oct 2008

oci_fetch_array

(PHP 5, PECL oci8:1.1-1.2.4)

oci_fetch_array — Returns the next row from the result data as an associative or numeric array, or both

Description

array oci_fetch_array ( resource $statement [, int $mode ] )

Returns an array, which corresponds to the next result row.

For details on the data type mapping performed by the oci8 driver, see the datatypes supported by the driver

It should be mentioned here, that oci_fetch_array() is insignificantly slower, than oci_fetch_row(), but much more handy.

Parameters

statement

A valid OCI statement identifier.

statement

An optional second parameter can be any combination of the following constants:

OCI_BOTH - return an array with both associative and numeric indices (the same as OCI_ASSOC + OCI_NUM). This is the default behavior.
OCI_ASSOC - return an associative array (as oci_fetch_assoc() works).
OCI_NUM - return a numeric array, (as oci_fetch_row() works).
OCI_RETURN_NULLS - create empty elements for the NULL fields.
OCI_RETURN_LOBS - return the value of a LOB of the descriptor.

Default mode is OCI_BOTH.

Return Values

Returns an array with both associative and numeric indices, or FALSE if there are no more rows in the statement .

Note: This function sets NULL fields to the PHP NULL value.

Note: Oracle returns all field names in uppercase and associative indices in the result array will be uppercased too.

Examples

Example #1 oci_fetch_array() with OCI_BOTH example


<?php
$connection = oci_connect("apelsin", "kanistra");

$query = "SELECT id, name FROM fruits";

$statement = oci_parse ($connection, $query);
oci_execute ($statement);

while ($row = oci_fetch_array ($statement, OCI_BOTH)) {
    echo $row[0]." and ".$row['ID']." is the same<br>";
    echo $row[1]." and ".$row['NAME']." is the same<br>";
}
?>

Example #2 oci_fetch_array() with OCI_NUM example


<?php
$connection = oci_connect("user", "password");

$query = "SELECT id, name, lob_field FROM fruits";

$statement = oci_parse ($connection, $query);
oci_execute ($statement);

while ($row = oci_fetch_array ($statement, OCI_NUM)) {
    echo $row[0]."<br>";
    echo $row[1]."<br>";
    echo $row[2]->read(100)."<br>";  //this will output first 100 bytes from LOB
}
?>

Example #3 oci_fetch_array() with OCI_ASSOC example


<?php
$connection = oci_connect("user", "password");

$query = "SELECT id, name, lob_field FROM fruits";

$statement = oci_parse ($connection, $query);
oci_execute ($statement);

while ($row = oci_fetch_array ($statement, OCI_ASSOC)) {
    echo $row['ID']."<br>";
    echo $row['NAME']."<br>";
    echo $row['LOB_FIELD']."<br>";  //this will output "Object id #1"
}
?>

Example #4 oci_fetch_array() with OCI_RETURN_LOBS example


<?php
$connection = oci_connect("user", "password");

$query = "SELECT id, name, lob_field FROM fruits";

$statement = oci_parse ($connection, $query);
oci_execute ($statement);

while ($row = oci_fetch_array ($statement, (OCI_NUM+OCI_RETURN_LOBS))) {
    echo $row[0]."<br>";
    echo $row[1]."<br>";
    echo $row['LOB_FIELD']."<br>";  //this will output LOB's content
}
?>


generating dynamic drop down list 

<SELECT name="reason" id="vacation_code">

<OPTION value=0 selected>Choose </OPTION>

<?php

$query2 = "SELECT IN_NAME, IN_CODE FROM HR_IN_REASON ";

$statement2 = oci_parse ($conn, $query2);

oci_execute ($statement2);

while ($row = oci_fetch_array ($statement2, OCI_NUM)) {

 ?>

                  <option value="<?  echo $row[1]; ?>"> <? echo $row[0] ?> </option>

                  <? }

?>

                </select>

antonchanning at gmail dot com
18-Apr-2006 10:59


As Robert Hicks mentioned back in August 2004 there is an error in examples 3 and 4 of this page.  The error in example 3 is what dwhitaker and stry_cat address in their notes of 20 May 2005 and 9 June 2005 respectively.



The correct form of example 4 should read:



<?php

$connection = oci_connect("user", "password");



$query = "SELECT id, name, lob_field FROM fruits";



$statement = oci_parse ($connection, $query);

oci_execute ($statement);



while ($row = oci_fetch_array ($statement, OCI_RETURN_LOBS)) {

   echo $row[0]."<br>";

   echo $row[1]."<br>";

   echo $row['LOB_FIELD']."<br>";  //this will output LOB's content

}

?> 



This really should be corrected in the actual documentation...

stry_cat at yahoo dot com
27-Jan-2006 05:29


If you want to get both nulls and an assoc array, you have to ADD the values like this:



$row = oci_fetch_array($stmt, OCI_RETURN_NULLS + OCI_ASSOC);



This really should be noted in the text of the manual.

09-Jun-2005 05:13


OCI_BOTH is the default.



If you just need to return all fields, you can leave out OCI_NUM & OCI_ASSOC.



EXAMPLE:



<?php

$connection = ocilogon("user", "password", "dbname");



$query = "SELECT id, name, lob_field FROM fruits";



$statement = oci_parse ($connection, $query);

oci_execute ($statement);



while ($row = oci_fetch_array ($statement)) {

   $id = $row['ID'];

   $name = $row['NAME'];

   $lob_field = $row['LOB_FIELD']; 



   echo $id.' '.$name.' '.$lob_field;



}

?>

dwhitaker at dfwairport dot com
20-May-2005 04:39


Example 3 above is incorrect...



OCI_NUM should be OCI_ASSOC



So it should read something like this:



<?php

$connection = ocilogon("user", "password", "dbname");



$query = "SELECT id, name, lob_field FROM fruits";



$statement = oci_parse ($connection, $query);

oci_execute ($statement);



while ($row = oci_fetch_array ($statement, OCI_ASSOC)) {

   $id = $row['ID'];

   $name = $row['NAME'];

   $lob_field = $row['LOB_FIELD']; 



   echo $id.' '.$name.' '.$lob_field;



}

?>

robert dot hicks at gmail dot com
10-Aug-2004 02:57


OCI_NUM should be changed in the various scripts to the appropriate call to the OCI_*. For example the script to show the OCI_ASSOC call still has OCI_NUMS in it.

add a note