PHP

Retrieving Results of a Query to Oracle

oci_fetch_object($stmt)

You have several ways to access the return values of an SQL query, but the following functions are used most often in practice:

  • oci_fetch_assoc() returns the current row in the resultset as an associative array.

  • oci_fetch_object() returns the current row in the resultset as an object.

  • oci_fetch_row() returns the current row in the resultset as a numeric array.

  • oci_fetch_all() returns the complete resultset as an array of associative arrays. However, five parameters are required: the statement object from oci_parse(), the array that is used for the return data, the number of lines to skip, the maximum number of rows to be returned (-1 means infinite), and whether to return a numeric (OCI_NUM) or associative (OCI_ASSOC) array.

The listing in This uses a while loop and oci_fetch_object() to retrieve all data in the table.

Retrieving Data from Oracle
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year<
  /th></tr>
<?php
  if ($db = @oci_connect('scott', 'tiger', 'orcl'))
    {
    $stmt = oci_parse($db, 'SELECT * FROM quotes');
    oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
    while ($row = oci_fetch_object($stmt)) {
      printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></
   tr>',
        htmlspecialchars($row->ID),
        htmlspecialchars($row->QUOTE),
        htmlspecialchars($row->AUTHOR),
        htmlspecialchars($row->YEAR)
      );
    }
    oci_close($db);
  } else {
    echo '<tr><td colspan="4">Connection
      failed.</td></tr>';
  }
?>
</table>

Oracle always returns column names in uppercase. Therefore, you have to use uppercase object properties or uppercase associative array keys when accessing the return values of an SQL query.

by BrainBellupdated
Advertisement: