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

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

Auxiliary OCI8 Functions

There are several more functions that describe result sets and the values fetched. Here they are, directly from the on-line manual:

DESCRIPTION

int oci_num_fields ( resource statement)

oci_num_fields() returns the number of columns in the statement.

int oci_num_rows ( resource stmt)

oci_num_rows() returns number of rows affected during statement execution.

string oci_field_name ( resource statement, int field)

oci_field_name() returns the name of the field corresponding to the field number (1-based).

bool oci_set_prefetch ( resource statement [, int rows])

oci_set_prefetch--Sets number of rows to be pre-fetched

These functions, all except the last one, are used to describe results of a query execution. Information can be learned regarding how many columns were requested in the query (oci_num_fields()), the names of those columns (oci_field_name()) and how many rows have been fetched so far (oci_num_rows()).

It is important to emphasize that oci_num_rows() does not return the number of rows returned by the query, but the number of rows fetched so far. The oci_num_rows() function is the PHP implementation of the %ROWCOUNT cursor attribute in PL/SQL. To determine the number of rows in the result set, the Oracle count function should be called.

The oci_num_rows() function is pure performance improvement. It simply defines how many rows have been fetched with each trip to the database. It is the PHP implementation of the feature that Oracle Corp. calls “Oracle Array Interface” and is another performance optimization available with the OCI8 module.

Now, what can be done with functions that describe a result set?  These functions give a great deal of flexibility and can be used in a function like the following:

<?php
function displayQueryAsTable($sth,$table_attr) {
   $ncols=oci_num_fields($sth);
   ?>
   <table <?=$table_attr?>>
   <tr  align="center"
                     valign="top"
                     style="background-color: #ADD8E6; ">
   <?php
       for ($i=1;$i<=$ncols;$i++) {
       $col=oci_field_name($sth,$i); ?>
       <th><?=$col?></th>
       <?php } ?>
   </tr>
<?php
oci_set_prefetch($sth,32);
while ($row=oci_fetch_array($sth,OCI_NUM)) {
        print "<tr>\n";
            foreach($row as $val) {
           printf("<td>%s</td>\n",$val);         
            }
        print "</tr>\n";
}
print "</table>";
}
?>

This function takes a statement handle, describes it and displays it as a HTML table. All it needs is an executed statement handle. A closer look at Example 13a is merited:

Example 13a, (Revisited)

<html>
<head>
<title>Query1</title>
</head>
<body>
<center>
<?php
require_once('OCI_Session.php');
require_once('displayQueryAsTable.php');
session_start();
$dbh=$_SESSION['dbh'];
$SQL="select e.ename,e.empno,d.dname,d.loc,e.sal
      from emp e, dept d
      where e.deptno=d.deptno
      order by e.deptno,ename";
try {
      $dbh->refresh();
      if (!$dbh->db) {
         $e=$dbh->err;
         throw new Exception ('CONN:'.$e['message']);
      }
      $sth=oci_parse($dbh->db,$SQL);
      if (!$sth) {
         $e=oci_error($sth);
         throw new Exception ('PARSE:'.$e['message']);
      }
      if (!oci_execute($sth)) {
          $e=oci_error($sth);
          throw new Exception ('EXEC:'.$e['message']);
      }
}
catch (Exception $e) {
       print $e->getMessage();
}
      displayQueryAsTable($sth,'frame="border" border="3"');
?>

What is so interesting about this example? Why revisit it? Now that the new utility function is implemented, there is nothing query-specific in this example. The previous version had explicit column titles and $row array requiring knowledge of the number of columns returned by the query.

This example does not require anything. It would work for any working query substituted in the $SQL variable. A complete template is presented to produce quick, single SQL statement reports from the database, using nothing more than a cut & paste. 

Having this information available allows the user to quickly assemble little applications using only PHP and OCI examples from this book alone.


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

http://www.rampant-books.com/book_2005_2_php_oracle.htm

 

Download your Oracle scripts now:

www.oracle-script.com

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 -2016 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