Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 32

Now that you have a good understanding of Oracle Database XE’s architecture, column datatypes, basic SQL commands, and security methods, it’s time to leverage these database features within
your PHP applications by using the Oracle OCI8 extension. In Chapter 24, we introduced PDO, a database-independent abstraction layer. In contrast, however, the Oracle OCI8 extension provides you with access to most, if not all, Oracle Database XE features with a high level of performance compared to other extensions. If your PHP applications will access Oracle databases exclusively, OCI8 is your best choice.
In this chapter, you will learn how to perform database queries and DML (Data Manipulation Language) functions, such as table INSERT, DELETE, UPDATE, and SELECT, using PHP function calls. In addition, we show you how to extract database metadata using PHP functions and SELECT statements against database data dictionary views.

Prerequisites

The primary prerequisite for using OCI8 is to configure your installation of PHP to use it. As we showed you in Chapter 27 in the section on configuring Oracle and PHP, locate the PHP configura- tion file php.ini you created in Chapter 2 and locate this line in the file itself:

;extension=php_oci8.dll

Remove the semicolon at the beginning of the line and save the file in its original location. Restart the Apache HTTP server on your workstation, and the OCI8 extensions are available to all of your PHP applications.

■Note In PHP 5, the OCI8 extension function names are more standardized; for example, the PHP 4 version of
OCILogin() is now oci_connect() in PHP 5. The old names still exist as aliases, but you are highly encouraged to use the new naming conventions in all of your applications.

Using Database Connections

Before you can do anything with the database, you must connect to it first and provide the required authentication information and database name. When you installed the database in Chapter 27, you ran the script in Listing 32-1 to test connectivity with the database.

565

Listing 32-1. PHP Code to Test Oracle Connectivity

<?php
if ($conn = oci_connect('system', 'yourpassword', '//localhost/xe')) { print 'Successfully connected to Oracle Database XE!'; oci_close($conn);
}
else {
$errmsg = oci_error();
print 'Oracle connect error: ' . $errmsg['message'];
}
?>

The call to oci_connect() establishes the connection to the database. In all future database requests,
you use the variable $conn to reference the established connection. You also have three different options for specifying the target database in your connection request; we’ll cover these options after reviewing the connection types. Finally, you’ll most likely want to know how to close a connection, so we’ll tell you how, as well as what happens behind the scenes.

Connecting to the Database

The call oci_connect() in Listing 32-1 establishes a standard connection. The other two types of connec- tions are unique connections and persistent connections. We show you the differences between these connection types in the following sections.

Standard Connections

You use oci_connect() to create a standard connection. Within a PHP script, if you call oci_connect() more than once with the same username and database name, you get a pointer to the same connection. This helps to minimize the number of required dedicated connections on the Oracle server; even if you are using a connection pooler, this method reduces the resource load on the server. Here is the syntax for a standard connection:

oci_connect($username, $password, $databasename);

The question you may ask is, “Isn’t that a poor programming practice to open the same database connection in multiple places in your PHP script?” This is true in general, but your PHP script, for example, may request multiple database resources after prompting the user for more than one user- name and password. As a result, you may call oci_connect() in more than one place in your script. If the database name and username are the same, oci_connect() will automatically use the same pointer, saving resources and the additional time it takes to establish another database connection.

Unique Connections

In contrast to oci_connect(), you use oci_new_connect() to create a unique connection; it will always request a new database connection. This method is useful in situations where you want to perform database operations independently from other database operations; for example, one connection may be processing transactions using table inserts, updates, and deletes while the other connection may be performing report queries. The transaction processing connection may commit or roll back the transaction with no effect on the report query connection; using the same connection for both types of connections may produce inconsistent results for the report query.
The syntax for oci_new_connect() is as follows:

oci_new_connect($username, $password, $databasename);

Persistent Connections

In contrast to oci_connect() and oci_new_connect(), you can use oci_pconnect() to create a persistent connection. Persistent connections do not automatically close at the end of a PHP script. Other scripts initiated from the same Web server or middleware server user session are free to use the connection as well as another invocation of the script that originally created the connection. The syntax for oci_pconnect() is as follows:

oci_pconnect($username, $password, $databasename);

You can also set limits on the number of active persistent connections, as well as automatically expire persistent connections after a period of time by defining variables in the file php.ini. To change the number of persistent connections to 20, use oci8.max_persistent in php.ini:

oci8.max_persistent=20

A value of -1 (the default) places no limits on the number of persistent connections. Similarly, to set a time-out value of 100 seconds for persistent connections, use oci8.persistent_timeout:

oci8.persistent_timeout=100

Database Connection Strings

There are three different types of connection strings: easy connect, database alias, and full database connection. A connection string is a set of one or more parameters that define the database name, server, network protocol, and port number of the database you wish to connect to. The type of connec- tion string you use depends on whether you have Oracle Net configuration files already in place on your server and whether you want to specify some of the more advanced connection parameters.

Using Easy Connect Strings

As the name implies, the easy connect string is the easiest to use; in most cases, all you need to specify is the username, password, and a connection string that may look familiar if you use JDBC to access a database from a Java application. The syntax of an easy connect string is as follows:

[//]hostname[:port][/service_name]

If you are accessing a database on the same host computer as your Web server, you can use localhost for the hostname parameter; otherwise, you use the hostname you see in your /etc/hosts file on Linux. You can also get the hostname of your computer by using the uname –n command. The port parameter defaults to 1521, which is the default port for any Oracle installation. This parameter may not be 1521 when you have more than one database on your host computer. Similarly, the service_name parameter defaults to the name of the only database installed on the host computer, which in the case of Oracle Database XE is XE. As a result, the oci_connect() call in Listing 32-1 can be further abbreviated as

oci_connect('system', 'yourpassword', 'localhost')

since the leading // is optional as well.

Using a Database Alias for Connection Strings

If you have a tnsnames.ora file on your client, or use Oracle Internet Directory (OID) in your environ- ment (OID is beyond the scope of this book), you can use an alias for your service_name parameter that you store in tnsnames.ora or OID. In Listing 32-2, you see a sample tnsnames.ora file with a total of seven connections: three from the default Oracle Database XE installation, and four others added

to connect to a remote Oracle Database 10g Real Applications Cluster (RAC) database. For a Linux installation of Oracle Database XE, you can find the file tnsnames.ora in $ORACLE_HOME/network/admin.

Listing 32-2. Sample tnsnames.ora File

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ath4800)(PORT = 1521)) (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc) (PRESENTATION = RO)
)
)

ORACLR_CONNECTION_DATA = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = CLRExtProc) (PRESENTATION = RO)
)
)

RACI2 = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = voc2i.sample.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = raci.world) (INSTANCE_NAME = raci2)
)
)

RACI1 = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = voc1i.sample.com)(PORT = 1521))
)

(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = raci.world) (INSTANCE_NAME = raci1)
)
)
RACI = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = voc1i.sample.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = voc2i.sample.com)(PORT = 1521)) (LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = raci.world)
)
)

RACSVC = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = voc1i.sample.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = voc2i.sample.com)(PORT = 1521)) (LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = raci.world) (FAILOVER_MODE =
(TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)
)
)
)

If the host machine running PHP is using the tnsnames.ora file in Listing 32-2, this PHP code will connect to the database as well:

oci_connect('system', 'yourpassword', 'xe');

If your PHP application will connect to the RAC database on the server sample.com, you could connect using any one of these four connect requests using tnsnames.ora entries:

oci_connect('hr', 'hr', 'racsvc'); oci_connect('hr', 'hr', 'raci'); oci_connect('hr', 'hr', 'raci1'); oci_connect('hr', 'hr', 'raci2');

Which one you use depends on your failover and application requirements.
These are the same alias names you can use in SQL*Plus or SQL command line; as with the oci_connect call, SQL*Plus defaults to XE (Oracle Database XE) if you are running SQL*Plus on the same host machine as the database.

Using Full Database Connection Strings

If you do not have a tnsnames.ora file on your host machine, and you want to connect to Oracle Database XE on a remote machine, you can use the same connection information you would use in a tnsnames.ora file. In this PHP code snippet, you connect to an XE database on the server sample2.com:

$dbci =
'
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sample2.com)(PORT = 1521)) (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
';

$conn = oci_connect('hr', 'hr', $dbci);

This syntax gives you the flexibility to specify many other network settings not available with the easy connect string syntax.

Disconnecting from the Database

If you use oci_connect() or oci_new_connect(), the database connection is automatically closed when your PHP script terminates. If you want to close the connection early and free up unneeded resources, you can explicitly close connections using oci_close() as follows:

$rc = oci_close($conn);

The variable $rc returns TRUE if the connection exists and is closed successfully, and FALSE
otherwise.
In contrast, oci_close() won’t close a connection opened with oci_pconnect(); idle connec- tions opened with oci_pconnect() can be set to expire. You can expire persistent connections using the php.ini parameter oci8.persistent_timeout described earlier in this chapter in the section “Persistent Connections.”

Retrieving and Modifying Data

In the following sections, we give you the basics of using SQL SELECT statements as well as the other DML commands INSERT, UPDATE, and DELETE. Now that you already know how to perform SELECT and DML commands at the SQL*Plus prompt, we’ll show you how to perform them using PHP OCI8 calls.
Here are the five steps that OCI8 uses to process a SQL statement:

Parse: Prepare the statement for execution. Oracle checks for syntactic correctness, whether the objects referenced in the statement exist and are accessible. If there is a syntax error or some other problem, Oracle does not return an error code in this step; you receive the error code in the execute phase.

Bind: Bind data values to variables in the SQL statement, usually for performance or security reasons.

Define: Specify the PHP variables to store the results; this is rarely used, and the preferred method uses the ..._fetch_...() functions.

Execute: Send the SQL command to Oracle for processing and buffer the results.

Fetch: Retrieve results from the database using calls such as oci_fetch_array().

Some of these steps are optional. For example, if you are not using bind variables, you will not need a bind step. In addition, you use the fetch step with a SELECT statement but not an INSERT state- ment because the fetch step retrieves rows from a table, and an INSERT statement does not. We define these phases further in the following sections along with the PHP OCI8 functions you use to perform each of these steps.

Preparing, Binding, and Executing Statements

Before you can run a query against your Oracle database, you must first parse the query. You use oci_parse() to prepare the query; oci_bind_by_name() to optionally bind PHP variables to SQL vari- ables; and then oci_execute() to run the query.

oci_parse()

You parse a query by making a call to oci_parse, with a syntax as follows:

resource oci_parse ( resource connection, string query )

After you establish a connection to the database, you use oci_parse() to prepare the SQL statement for execution and very basic validations such as correctly quoted strings. Determining the existence of the referenced objects and verifying that the syntax of the query is correct and whether the user has the privileges to access the objects in the SQL statement doesn’t occur until the execution phase.

oci_bind_by_name()

Binding a variable is simply a variable substitution at run time. In other words, your SQL text contains a placeholder, and you substitute an actual value for the placeholder using oci_bind_by_name with this syntax:
bool oci_bind_by_name ( resource statement, string query, mixed variable, [, int maxlength [, int type]])
Binding variables in OCI8 has two distinct advantages: security and performance. Using bound variables prevents SQL injection attacks. Your PHP code has control over the SQL statement executed by ensuring that user input maps to constants in the WHERE clause rather than column names in the SELECT clause, for example. Performance of subsequent executions of SQL statements with bind variables is improved because the parse phase only needs to occur once.
You bind values to variables in the SQL statement for variable names preceded by a colon. We’ll show you an example after we introduce oci_execute(). You use the optional maxlength parameter to provide a maximum length for a value returned from PL/SQL procedures and functions; other- wise, maxlength defaults to the current length of the bound PHP variable. You use the other optional parameter, type, for abstract datatypes such as LOBs (large objects).

oci_execute()

The oci_execute() function submits the SQL statement to Oracle for execution. You fetch the results of the query using one of the many ..._fetch_...() functions we present in the following sections. Here is the syntax:

bool oci_execute ( resource statement [, int mode] )

In a default installation of Oracle Database XE, you get several sample schemas for training and testing and that highlight various Oracle features. The HR schema’s table LOCATIONS contains a list of the cities and countries where the company does business; here is a list of the columns in the LOCATIONS table:

SQL> describe locations

Name Null? Type
------------------------------- -------- ----------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2)

In this example, we’ll check the HR user's LOCATIONS table for cities whose name begins with B or
S, and give you a sneak peek at the oci_fetch_array() function in Listing 32-3.

Listing 32-3. Retrieving City and Province Information from the LOCATIONS Table

<?php
$c = oci_connect('hr', 'hr', '//localhost/xe');
$s = oci_parse($c,
"select city, state_province from locations where city like :city_prefix");
$cp = 'B%'; // Cities beginning with B oci_bind_by_name($s, ':city_prefix', $cp); oci_execute($s);
echo 'Cities beginning with ' . $cp . '<br /><br />';
while ($res = oci_fetch_array($s)) {
echo $res['CITY'] . ', ' . $res['STATE_PROVINCE'] . "<br />";
}
echo '<br /><br />';
$cp = 'S%'; // Cities beginning with S oci_bind_by_name($s, ':city_prefix', $cp); oci_execute($s);
echo 'Cities beginning with ' . $cp . '<br /><br />';
while ($res = oci_fetch_array($s)) {
echo $res['CITY'] . ', ' . $res['STATE_PROVINCE'] . "<br />";
}

oci_close($c);
?>

Here is the output you see from executing this script:

Cities beginning with B%

Beijing, Bern, BE
Bombay, Maharashtra

Cities beginning with S%

Sao Paulo, Sao Paulo Seattle, Washington Singapore,
South Brunswick, New Jersey
Southlake, Texas Stretford, Manchester Sydney, New South Wales

The percent sign, %, is a wildcard character that matches zero or more occurrences of any char- acter. In this example, S% would even match a city name of S, however unlikely it is that there is a city with that name. Notice that we did not have to execute oci_parse() more than once.

Retrieving Table Rows

If you have any background in SQL, you already know that the SELECT statement retrieves rows from a table. This section introduces many of the functions that you will (or at least should) become most familiar with when using the Oracle OCI8 PHP extension because these functions play an important role in retrieving the data returned from a SELECT query.
All OCI8 functions are very thoroughly documented in the PHP OCI8 Reference Manual at http://php.net/manual/en/ref.oci8.php. In this section, we give you a brief overview of each key function and show you a few different ways to use these functions to retrieve rows from the database.

oci_fetch_array()

The oci_fetch_array() function retrieves each row of the statement as an associative array, a numeri- cally indexed array, or both. Here is the syntax:

array oci_fetch_array (resource statement [,int result_type])

By default, it retrieves both arrays; you can modify this default behavior by passing one of the following values in as the result_type:

OCI_ASSOC: Returns the row as an associative array, with the key represented by the field name and the value by the field contents. Using this option is equivalent to using oci_fetch_assoc().

OCI_NUM: Returns the row as a numerically indexed array, with the ordering determined by the ordering of the field names as specified within the array. If an asterisk is used (signaling the query to retrieve all fields), the ordering will correspond to the field ordering in the table defini- tion. Designating this option results in oci_fetch_array() operating in the same fashion as oci_fetch_row().

OCI_BOTH: Returns the row as both an associative and a numerically indexed array. Therefore, each field could be referred to in terms of its index offset and its field name. This is the default behavior.

OCI_RETURN_NULLS: Creates empty elements for columns with NULL values.

OCI_RETURN_LOBS: Returns the value of a LOB instead of just the pointer.

Note that you can combine these constants to specify more than one option:

$res = oci_fetch_array($s, OCI_ASSOC + OCI_RETURN_NULLS);

The example in Listing 32-3 uses oci_fetch_array() without the second parameter so defaults to OCI_BOTH. Therefore, the echo statement will produce the same results if you use a numeric index for each column value:

echo $res[0] . ', ' . $res[1] . "<br />";

The type of array you use depends on your programming style. If you always return both types of arrays, your script’s memory requirements are a bit higher but in practice shouldn’t require a server memory upgrade.

oci_fetch_assoc()

This function oci_fetch_assoc() operates identically to oci_fetch_array() when OCI_ASSOC is passed in as the result_type parameter; here is the syntax:

array oci_fetch_assoc (resource statement)

oci_fetch_row()

This function operates identically to oci_fetch_array() when OCI_NUM is passed in as the result_type
parameter. Here is the syntax:

array oci_fetch_row (resource statement)

oci_fetch_object()

This function also returns rows from an Oracle table just like oci_fetch_array(), except that an object is returned rather than an array. The syntax is as follows:

object oci_fetch_object (resource statement)

Consider the following revision to the example in Listing 32-3 to oci_fetch_array():

<?php
$c = oci_connect('hr', 'hr', '//localhost/xe');
$s = oci_parse($c,
"select city, state_province from locations where city like :city_prefix");
$cp = 'B%'; // Cities beginning with B oci_bind_by_name($s, ':city_prefix', $cp); oci_execute($s);
echo 'Cities beginning with ' . $cp . '<br /><br />';
while ($res = oci_fetch_object($s)) {
echo $res->CITY . ', ' . $res->STATE_PROVINCE . "<br />";
}
oci_close($c);
?>

Notice that the object references use uppercase for the Oracle column names; $res->city does
not exist. Unless you create the table with lowercase column names enclosed in double quotes, all
Oracle column names are uppercase.
The output of this example is identical to the output of the example in Listing 32-3. The differ- ence in memory requirements and execution speed is insignificant; your programming style and coding requirements dictate which form of oci_fetch_... you use.

oci_fetch_all()

As the name implies, oci_fetch_all() retrieves all the rows from a database query at once; here is the syntax:

int oci_fetch_all ( resource statement,
array &output [, int skip [, int maxrows [, int flags]]] )

This form of oci_fetch_... can be useful in an environment where you have enough memory to hold the entire results of the query and you need to have the entire result set available before you can perform an aggregate operation for your users, for example.
The optional parameters skip and maxrows define the number of rows to skip and the total number of rows to return, respectively. The flags parameter can contain OCI_NUM and OCI_ASSOC just as oci_fetch_array() does.

Inserting Rows

Inserting data into the database is carried out very much in the same fashion as retrieving information, except that the query often contains variable data. Following an example is the best way to learn this process. Suppose that your company’s inventory specialist requires a means for inserting new product information from anywhere. Not surprisingly, the most efficient way to do so is to provide him with a Web interface. Figure 32-1 depicts this Web form for which the source code, called insert_location.php, is provided in Listing 32-4 with a new location ready to insert into the database.

Figure 32-1. The location insertion form

Listing 32-4. Location Insertion Form Code (insert_location.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<p>Add a Location to the LOCATIONS table.</p>
<p>
Location ID Number:<br />
<input type="text" name="LocationID" size="4" maxlength="4" value="" />
</p>

<p>
Street Address:<br />
<input type="text" name="StreetAddress" size="40" maxlength="40" value="" />
</p>
<p>
Postal Code:<br />
<input type="text" name="PostalCode" size="12" maxlength="12" value="" />
</p>
<p>
City:<br />
<input type="text" name="City" size="30" maxlength="30" value="" />
</p>
<p>
State or Province:<br />
<input type="text" name="StateOrProvince" size="25" maxlength="25" value="" />
</p>
<p>
Country Code:<br />
<input type="text" name="CountryCode" size="2" maxlength="2" value="" />
</p>
<p>
<input type="submit" name="submit" value="Submit!" />
</p>
</form>

Listing 32-5 contains the source code for the database insertion logic.

Listing 32-5. Inserting Form Data into an Oracle Table (db_insert_location.php)

<?php
// If the submit button has been pressed... if (isset($_POST['submit']))
{

// Connect to the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

// Retrieve the posted new location information.
$LocationID = $_POST['LocationID'];
$StreetAddress = $_POST['StreetAddress'];
$PostalCode = $_POST['PostalCode'];
$City = $_POST['City'];
$StateOrProvince = $_POST['StateOrProvince'];
$CountryCode = $_POST['CountryCode'];

// Insert the location information into the LOCATIONS table
$s = oci_parse($c, "insert into locations
(location_id, street_address, postal_code, city, state_province, country_id)
values ($LocationID, '$StreetAddress', '$PostalCode',
'$City', '$StateOrProvince', '$CountryCode')");

$result = oci_execute($s);

// Display an appropriate message on either success or failure if ($result)
{
echo "<p>Location successfully inserted!</p>";
oci_commit($c);
}
else
{
echo "<p>There was a problem inserting the location!</p>";
var_dump(oci_error($s));
}

oci_close($c);
}

// Include the insertion form include "insert_location.php";

?>

Note the use of the include directive in the file db_insert_location.php. You can place all of the
code in one file but splitting it up this way makes code maintenance easier and facilitates code reuse. Notice the oci_commit() call; this option permanently saves the inserted row, although in this example, calling oci_close() also saves the inserted row. We talk more about transactions in Chapter 33.
Querying the table from the SQL Commands page within the Oracle Database XE Web interface, you can see in Figure 32-2 that the new row exists in the LOCATIONS table.

Figure 32-2. Updated contents of the LOCATIONS table

Modifying Rows

Data modification is ultimately the product of three actions: the first provides the user with the means for selecting target data for modification, the second provides the user with an interface for modifying the data, and the third carries out the modification request. Target selection can take place via a variety of interfaces: radio buttons, checkboxes, selectable lists—you name it.
Listing 32-6 offers a simple form to fulfill the first two actions in the context of modifying the corporate branch office location data—selecting the location to be modified, as well as providing the modifications. Along with the location ID, the user can specify the new postal code for the selected location.

Listing 32-6. Selecting the Location Number (update_location.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<p>Modify a Location's Postal Code in the LOCATIONS table.</p>
<p>
Location ID Number:<br />
<input type="text" name="LocationID" size="4" maxlength="4" value="" />
</p>
<p>
New Postal Code:<br />
<input type="text" name="PostalCode" size="12" maxlength="12" value="" />
</p>
<p>
<input type="submit" name="submit" value="Submit!" />
</p>
</form>

Listing 32-7 contains the database update step invoked once the location ID has been selected for modification. This code is similar in many ways to the database insert step in Listing 32-5.

Listing 32-7. Modifying Location Postal Code (db_update_location.php)

<?php
// If the submit button has been pressed... if (isset($_POST['submit']))
{

// Connect to the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

// Retrieve the posted existing location information
// and new Postal Code.
$LocationID = $_POST['LocationID'];
$PostalCode = $_POST['PostalCode'];

// Update the Postal Code information into the LOCATIONS table
$s = oci_parse($c, "update locations
set postal_code = '$PostalCode' where location_id = $LocationID");

$result = oci_execute($s);
$rows_affected = oci_num_rows($s);

// Display an appropriate message if ($result)
{
echo "<p>Postal Codes updated: " . $rows_affected . "</p>";
oci_commit($s);
}
else
{
echo "<p>There was a problem updating the Postal Code!</p>";
var_dump(oci_error($s));
}

oci_close($c);
}

// Include the insertion form include "update_location.php";

?>

When the user presses the Submit button, the form displays the number of rows modified,
either zero or one. If the user enters an invalid location ID, zero rows are updated, and if the location ID exists, one row is updated. Since the location ID is the LOCATIONS table’s primary key, there will be only one row in the table with a given location ID.
The function oci_num_rows() returns the number of rows affected by a SQL statement. This applies to all SQL statements except for SELECT. We tell you more about oci_num_rows() later in the section “Counting Rows Selected or Affected.”
Keep in mind that this is just one of a vast number of ways you can go about modifying data within a PHP script. Also, for the sake of illustrating only the concept of updating rows in the data- base, all code required for sanity checking the product information is omitted. Such controls are central to ensuring proper functionality of this (or any other) mechanism used for updating database information.

Deleting Rows

Like data modification, data deletion is a three-step process involving target data selection, the deletion request, and the delete operation itself. You can use many different form types to delete items, such as multivalued form components introduced in Chapter 13. In this example, however, we use the code examples in Listings 32-6 and 32-7 as the basis for the delete version of the scripts in Listings 32-8 and 32-9.

Listing 32-8. Selecting the Location Number for Deletion (delete_location.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<p>Delete a Location from the LOCATIONS table.</p>
<p>
Location ID Number:<br />
<input type="text" name="LocationID" size="4" maxlength="4" value="" />
</p>
<p>
<input type="submit" name="submit" value="Submit!" />
</p>
</form>

Listing 32-9 contains the database delete step invoked once the location ID has been selected for deletion.

Listing 32-9. Deleting a Location (db_delete_location.php)

<?php
// If the submit button has been pressed... if (isset($_POST['submit']))
{

// Connect to the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

// Retrieve the posted existing location information
// and delete the row.
$LocationID = $_POST['LocationID'];

// Update the Postal Code information into the LOCATIONS table
$s = oci_parse($c, "delete from locations
where location_id = $LocationID");

$result = oci_execute($s);
$rows_affected = oci_num_rows($s);

// Display an appropriate message if ($result)
{
echo "<p>Locations deleted: " . $rows_affected . "</p>";
oci_commit($s);
}
else
{
echo "<p>There was a problem deleting a location!</p>";
var_dump(oci_error($s));
}

oci_close($c);
}

// Include the deletion form include "delete_location.php";

?>

As you can see, the deletion process is like all the other processes described thus far. Note that
we use oci_num_rows() to provide feedback to the user that the row or rows in question are properly deleted. If this function returns 0, no rows were found; if it returns -1, an error occurred. Otherwise, it returns the total number of rows affected by the DELETE command, which in this situation should always be 1. You’ll learn more about this function in the next section.

Counting Rows Selected or Affected

You’ll often want to be able to determine the number of rows returned by a SELECT query, or the number of rows affected by an INSERT, an UPDATE, or a DELETE statement. The oci_num_rows() func- tion is available for doing this, with some caveats.
It takes as input one parameter: the pointer to the parsed query, stmt. Here is the syntax:

int oci_num_rows (resource stmt)

For example, here is how you can retrieve rows and provide a count of them:

$s = oci_parse($c,
"select city, state_province from locations where city like :city_prefix");
$cp = 'B%'; // Cities beginning with B oci_bind_by_name($s, ':city_prefix', $cp); oci_execute($s);
echo 'Cities beginning with ' . $cp . '<br /><br />';
while ($res = oci_fetch_array($s)) {
echo $res[0] . ', ' . $res[1] . "<br />";
}
echo '<br />Number of rows retrieved: ' . oci_num_rows($s) . '<br />';

Here is the output from this code snippet:

Cities beginning with B%

Beijing, Bern, BE
Bombay, Maharashtra

Number of rows retrieved: 3

Keep in mind that oci_num_rows() produces the total count from a select query only if the result set is exhausted after repeated ..._fetch_...() requests. If you want to know the row count without explicitly retrieving all rows, use select count(*)... to retrieve a single row result set containing the total row count.

Retrieving Database Metadata

Most of the time you want to extract, insert, or update the information from the tables in your data- base; other times you want to know what tables you have. In the following sections, we show you how to retrieve information about your installation of Oracle Database XE, the tables in a schema, and the column characteristics of those tables.
Other databases such as MySQL validate a user account and selectively provide access to groups of tables called databases. Alternatively, Oracle Database XE treats a database as a group of users (schemas), each of which owns database tables. Once Oracle Database XE authenticates a user, you can query data dictionary tables to retrieve information about users and the associated tables.
Therefore, there aren’t too many OCI8 functions that retrieve information about the database itself and the tables that reside on an Oracle Database XE server. Instead, we’ll show you how to retrieve information on database users and database characteristics using the database’s views and tables that contain metadata.

Viewing Database Characteristics

As you may remember from Chapter 28, there is a subtle but clear distinction between a database and an instance. The database consists of the files on disk. These files store the data itself, the state of the database in the control file, and changes to the database’s data in redo log files. The instance, on the other hand, refers to the Oracle memory processes and memory structures that reside in your server’s memory and accesses the database stored in the disk files. For most databases, including Oracle Database XE, there is one and only one instance for each database. For those versions of Oracle Database 10g that support RAC, you can have more than one instance per database.
To retrieve information about the database, you can query the dynamic performance view V$DATABASE; similarly, you can query the dynamic performance view V$INSTANCE to retrieve informa- tion about the instance. Listing 32-10 shows you how to retrieve key informational columns from these two views, and Figure 32-3 shows you the results. We discuss dynamic performance views (views that start with V$) in Chapter 35.

Listing 32-10. Querying Database and Instance Characteristics (query_db_info.php)

<?php
$c = oci_connect('rjb', 'rjb', '//localhost/xe');
$s = oci_parse($c,'select name, created, log_mode, open_mode from v$database');
oci_execute($s);
echo '<b>Database Characteristics:</b><br /><br />';

$res = oci_fetch_array($s);
echo 'Database Name: ' . $res['NAME'] . '<br />' ; echo 'Created: ' . $res['CREATED'] . '<br />' ; echo 'Log Mode: ' . $res['LOG_MODE'] . '<br />' ;
echo 'Open Mode: ' . $res['OPEN_MODE'] . '<br />' ;

echo '<br /><br />';
$s = oci_parse($c,'select instance_name, host_name, version, startup_time, status, edition from v$instance');
oci_execute($s);
echo '<b>Instance Characteristics:</b><br /><br />';

$res = oci_fetch_array($s);
echo 'Instance Name: ' . $res['INSTANCE_NAME'] . '<br />' ;
echo 'Host Name: ' . $res['HOST_NAME'] . '<br />' ;
echo 'Version: ' . $res['VERSION'] . '<br />' ;
echo 'Startup Time: ' . $res['STARTUP_TIME'] . '<br />' ;
echo 'Status: ' . $res['STATUS'] . '<br />' ;
echo 'Edition: ' . $res['EDITION'] . '<br />' ;

oci_close($c);
?>

Figure 32-3. Database and instance metadata

Viewing User Tables

There is no explicit OCI8 function to view the list of the tables you own in a database schema. Instead, you can access the data dictionary metadata view USER_TABLES just as you would access any other table from a PHP script. Here is what the table USER_TABLES looks like:

SQL> describe user_tables

Name Null? Type
----------------------------------------- -------- --------------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER
. . .
MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) DROPPED VARCHAR2(3)

SQL> select table_name, status from user_tables;

TABLE_NAME STATUS
------------------------------ -------- REGIONS VALID LOCATIONS VALID DEPARTMENTS VALID JOBS VALID EMPLOYEES VALID JOB_HISTORY VALID COUNTRIES VALID

7 rows selected.

SQL>

The data dictionary view ALL_TABLES takes it a bit further: it shows all tables that you own plus any tables you can access in other schemas. The structure of ALL_TABLES is the same as USER_TABLES except that ALL_TABLES has an OWNER column. We discuss data dictionary views in more detail in Chapter 35.

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 table’s 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

Using Other Database Functions

Numerous system-related functions are also available and capable of providing valuable informa- tion about Oracle Database XE server threads, status, connection types, and client and server versions. Each of these functions is introduced in this section.

oci_error()

This function returns an associative array of error messages generated by the last OCI8 function, or returns an empty array if no error occurred. Here is the syntax:

array oci_error ([resource source])

If the optional source parameter is included, the most recently occurring error emanating from that identifier will be used. Do not provide the source parameter for oci_connect() errors.
In Listing 32-1, we showed you a simple example of error handling for the oci_connect() call:

$errmsg = oci_error();
print 'Oracle connect error: ' . $errmsg['message'];

We assign the output of the oci_error() call to the array $errmsg and return only the error message itself.
For handling error conditions for other OCI8 calls, provide the appropriate resource handle. For a SQL parsing error, use the connection handle. For example, your SQL command has a syntax error, and you want to catch any errors after you attempt to parse it:

$s = oci_parse($conn, "select ' from missing_table");
if (!$s)
{
$par = oci_error($conn);
print $par['message'];
}

else
...

Executing the code generates this output:

ORA-01756: quoted string not properly terminated

all:

If you’re not sure what the other elements in the array are, you can use var_dump() to get them

$s = oci_parse($conn, "select ' from missing_table");
if (!$s)
{
$par = oci_error($conn);
var_dump($par);
}
else
...

Running this code again, you get this output:

array(4) { ["code"]=> int(1756)
["message"]=> string(48) "ORA-01756: quoted string not properly terminated" ["offset"]=> int(0) ["sqltext"]=> string(0) "" }

As you can see, the variable $par['code'] will provide the Oracle error code when parsing the SQL statement that caused the parse error. The variable $par['sqltext'] contains the SQL statement itself when Oracle returns an error from the oci_execute() call.
Once the oci_parse() call succeeds after you fix the problem with the single quote, you attempt to execute the statement with oci_execute() and check for errors again, as in this example:
$s = oci_parse($conn, "select * from missing_table");
if (!$s)
{
$par = oci_error($conn);
var_dump($par);
}
$r = oci_execute($s);
if (!$r)
{
$exe = oci_error($s);

var_dump($exe);
}

Since the table referenced by the SELECT statement does not exist, var_dump() gives you this:

array(4) { ["code"]=> int(942)
["message"]=> string(39) "ORA-00942: table or view does not exist" ["offset"]=> int(14) ["sqltext"]=> string(27) "select * from missing_table" }

Notice that $exe['sqltext'] now gives you the full text of the SQL command and that
$exe['offset'] gives you the character position within the SQL command of the offending text, which in this case is a missing table.

oci_password_change()

As the name implies, you can change the password for any username as long as you provide the correct old password. Here is the syntax:

bool oci_password_change (resource connection, string username, string old_password, string new_password)
The length, format, and mix of characters in the new password must follow the same rules as if you changed your password at the SQL*Plus prompt using the ALTER USER command. The call returns TRUE if it succeeds; if the call fails, it returns FALSE.

Summary

As a DBA and PHP developer, you’re now bilingual: you can access Oracle Database XE from both SQL*Plus and PHP applications using OCI8. We showed you how to connect to and disconnect from the database, how to retrieve and modify rows in a database table, and how to retrieve other data- base metadata. For any database operations, you may get error conditions (usually out of your control), so we showed you how to trap and handle those errors gracefully with minimal impact to your appli- cation’s users. Finally, we showed you the other functions available in the OCI8 extension that you may not need very often, but when you do need them, they’re there.
In the next chapter, we investigate some of the finer points of using PHP and Oracle SQL in a
transactional environment—in other words, grouping DML statements so that they all succeed or all fail as a unit.