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.

Advertisement:
Advertisement: