Using Various Database Functions with PHP`s Oracle Functionality

(Page 1 of 2 )

In this conclusion to a seven-part article series, you'll learn how to view table columns and column characteristics, among other things, in Oracle DB XE using PHP. This article is excerpted from chapter 32 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Viewing Table Columns and Column Characteristics

Several functions are available for retrieving information about the fields in a given table: oci_field_name(), oci_field_type(), oci_field_size(), oci_field_precision(), and oci_field_scale(). All of these functions are introduced in this section with a short example and an alternative way to retrieve this table metadata.

oci_field_name()

oci_field_name() returns the name of the field from the SQL statement stmt corresponding to the field number field_offset (starting with 1); here is the syntax:

string oci_field_name (resource stmt, int field_offset)

oci_field_type()

oci_field_type() returns the column type--for example, VARCHAR2, NUMBER, or CHAR. The syntax is as follows:

string oci_field_type (resource stmt, int field_offset)

oci_field_size()

oci_field_size() returns the size of the column in bytes: the number of bytes required to store the value in the column. Here is the syntax:

string oci_field_size (resource stmt, int field_offset)

oci_field_precision()

oci_field_precision() applies to NUMBER or FLOAT columns only. The syntax is as follows:

string oci_field_precision (resource stmt, int field_offset)

It returns the number of significant digits stored for a NUMBER; for FLOAT, the precision is the number of significant digits and the scale is -127 (see oci_field_scale()). If the precision is zero, the column is defined as NUMBER with a default precision of 38.

oci_field_scale()

oci_field_scale() applies to NUMBER or FLOAT columns only. The syntax is as follows:

string oci_field_scale (resource stmt, int field_offset)

It stores the number of significant digits to the right of the decimal point; for FLOAT, the scale is always -127.

Using these five functions, you can use the script in Listing 32-11 to retrieve this metadata.

Listing 32-11. Querying Table Characteristics (query_table_info.php)

<?php
   $c = oci_connect('hr', 'hr', '//localhost/xe');
   $table_name = 'LOCATIONS';
   $s = oci_parse($c,'select * from ' . $table_name);
   oci_execute($s);

   echo "<b>Table: </b>" . $table_name . "<br /><br />";
   echo "<table border="1">";
   echo "<tr>";
   echo "<th>Name</th>";
   echo "<th>Type</th>";
   echo "<th>Size</th>";
   echo "<th>Precision</th>";
   echo "<th>Scale</th>";
   echo "</tr>";

   $ncols = oci_num_fields($s);

   for ($i = 1; $i <= $ncols; $i++) {
       $column_name = oci_field_name($s, $i);
       $column_type = oci_field_type($s, $i);
       $column_size = oci_field_size($s, $i);
       $column_prec = oci_field_precision($s, $i);
       $column_scale = oci_field_scale($s, $i);

       echo "<tr>";
       echo "<td>$column_name</td>";
       echo "<td>$column_type</td>";
       echo "<td>$column_size</td>";
       echo "<td>$column_prec</td>";
       echo "<td>$column_scale</td>";
       echo "</tr>";
   }

    echo "</table>\n";
    oci_free_statement($s);

    oci_close($c);
?>

Figure 32-4 shows what the output of the script looks like for the LOCATIONS table.

Figure 32-4.  Retrieving metadata using OCI8 calls in a PHP script

If you do not need to incorporate the metadata information into your PHP application, you can easily retrieve the tables characteristics using the Oracle Database XE Web interface's object browser in Figure 32-5.

Figure 32-5.  Retrieving metadata using the Oracle Database XE Web interface

Next: Using Other Database Functions >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus