Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Oracle Tips by Burleson 

Auxiliary OCI8 Functions

When a query is executed, Oracle creates a result set. This result set has to be transferred to the executing script by fetching it. This section explains how to retrieve and describe result sets. In Example 16, the only function called to do a fetch is oci_fetch($usr); in which $usr is a statement handle. The oci_fetch() function is used to illustrate a call to oci_define(). There are several more functions to facilitate fetching the data from an Oracle database to the program and oci_fetch_array() is the most frequently used one.

This function is shown in Example 15; the results of the query are fetched using the oci_fetch_array function. The statement block in question is this one:

   while ($row=oci_fetch_array($sth)) {
          echo $row[0],"\t",$row[1],"\t",$row[2],
               "\t",$row[3],"\n"; }

No define step has ever been done in Example 15 and the function used, oci_fetch_array(), returned an array named $row. The oci_fetch_array() function can return either a numeric array, an associative array (hash) or both.  The default is to return both. Here is the description of the oci_fetch_aray() from the manual:


array oci_fetch_array ( resource statement [, int mode])

Returns an array, which corresponds to the next result row or in FALSE case of error or there is no more rows in the result.

oci_fetch_array() returns an array with both associative and numeric indices.

Note: This function sets NULLfields to PHP NULL value.

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.

It should be mentioned here, that oci_fetch_array() is insignificantly slower than oci_fetch_row(), but much more handy. It must also be noted that Oracle returns all field names in uppercase and associative indices in the result array are uppercased too.

The remark about being slower than oci_fetch_row() also applies to the bare bones oci_fetch().  There are separate functions to fetch the next row into an associative array, a numeric array or an object. Not surprisingly, these functions are called oci_fetch_assoc(), oci_fetch_row() and  oci_fetch_object(). For the query “select * from dept”, the oci_fetch_object() function returns the next row as an object belonging to the class defined as follows:

           class  row {
           public $DEPTNO,
           public $DNAME,
           public $LOC }

In other words, instead of fetching into an array, the row is packaged into an object and accessed correspondingly by using the OO syntax, like this: $d->DEPTNO, $d->DNAME and $d->LOC. Fetching rows into objects is used relatively infrequently in real world database applications. The call that is most frequently used is oci_fetch_array() which is completely sufficient for most applications, including the analyze_schema.php from the example 16.  Despite the numerous attempts of blending  databases and object orientation,  these two paradigms are still separated by a wide gap. Things like object-relational features of Oracle RDBMSand pure object databases have relatively few followers, compared to the number of users and applications that use databases in the traditional way.

The oci_fetch_aray() function returns each row as an array, both the associative array and an array indexed by number. This means that if the query is asking for N columns, the oci_fetch_array() will contain 2*N elements. If one or another is desired, OCI_ASSOC or OCI_NUM flags should be used. The default is both. This is really important if correct results are wanted when looping through the fetched row by using the “foreach” statement.

See code depot for complete scripts

The above book excerpt is from:

Easy Oracle PHP

Create Dynamic Web Pages with Oracle Data

ISBN 0-9761573-0-6   

Mladen Gogala


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter