Like any other computer program, an SQL statement has to be compiled (parsed) before it can be executed. When a new SQL statement is parsed, Oracle creates the parse tree and execution plan for that statement and then caches this information in the shared pool portion of the system global area (SGA) memory, as an item usually called a shared SQL area. Oracle reuses a shared SQL area for similar SQL statements issued against the database, thus using the cached information instead of generating it from scratch.

Once Oracle has the shared SQL area for the statement being processed, it checks the buffer cache, searching for the required data blocks.

Upon failure to find these blocks in the buffer cache, Oracle reads them from the appropriate database files and then loads the retrieved data blocks into the buffer cache, which is, of course, an expensive operation in terms of CPU time and physical I/Os.

The following figure depicts the process of SQL statement processing diagrammatically.

It is important to understand that the parsing information held in the shared pool and the data blocks kept in the buffer cache are shared across all database sessions, regardless of the database schema under which a certain session is created, thus allowing multiple applications and multiple users to use cached data. To understand this better, consider the following example.

Suppose an application, which connects to the database using, say, usr1 database schema, issues an SQL query. The database server in turn processes the query, caching the generated execution plan and parse tree for the query in the shared pool and the data blocks retrieved from the data file or files in the buffer cache. Then, another application, when connected to the database through, say, the usr2 schema, issues the same query. This time, Oracle will not generate the execution plan and parse tree for the query again and nor will it retrieve the appropriate data blocks from the database files. Instead, it will reuse the data held in the SGA, specifically, in the shared pool and in the buffer cache, which was loaded there when processing the query issued by the first application.

Another important point to note here is that Oracle can reuse parsing information for similar statements. You might be wondering which statements Oracle considers to be similar when it comes to sharing parsing information. Well, Oracle will use the same shared SQL area for two SQL statements if their SQL texts are identical, including white spaces and case. Consider the following statement:

SELECT * FROM users WHERE usrid=:id

As you can see, this statement contains a placeholder in the WHERE clause, thus allowing the application to supply a particular value for the :id placeholder at run time. From the user's perspective, the above SQL string can be used to issue similar statements that differ by the user ID specified in the WHERE clause at run time. However, to Oracle, all those statements based on the above one are identical, at least at the stage of determining whether the same shared SQL area can be used for each of them.

As you have no doubt guessed, an efficient way to reduce the number of parses required is to use bind variables in SQL and PL/SQL statements, rather than using string literals. What this means in practice is that you should use placeholders in SQL and PL/SQL statements to mark where actual data must be supplied at run time.

For example, Oracle will use the same parsing information for the query used in the code snippet below, regardless of the actual value of the $usrno bind variable passed to the getUser function as the parameter.

<?php
function getUser($usrno)
{
...
$sql = 'SELECT * FROM users WHERE usrid=:id';
$stmt = oci_parse($dbConn, $sql);
oci_bind_by_name($stmt,":id", $usrno);
if(!oci_execute($stmt))
{
$err = oci_error($stmt);
trigger_error($err['message'], E_USER_ERROR);
}
...
}
?>

When using the PEAR::DB package for interacting with the database, the above snippet might look like this:

<?php
function getUser($usrno)
{
...
$sql ='SELECT * FROM orders WHERE usrid=?';
$res =$db->query($sql, $usrno);
if (PEAR::isError($res)) {
die($res->getMessage());
}
...
}
?>

Looking through the above snippets, you might come to the conclusion that they both issue the same SQL statement. This is not the case, however. While the first code snippet issues exactly the same query as you see in the code:

SELECT * FROM users WHERE usrid=:id

The second one, which is based on using PEAR:DB, will convert the SQL before passing it to the database, generating the following query:

SELECT * FROM users WHERE usrid=:bind0

Although the :bind0 and :id placeholders are of the same data type, they differ in name, and, therefore, Oracle cannot use the same shared SQL area when processing the above two statements.

As mentioned above, PEAR::DB implicitly names placeholders using the prefix bind followed by a number starting with 0. If you're using more than one bind variable in a statement, you should pass an array of the actual values for the placeholders to the query method, and PEAR::DB in turn will rename each placeholder to the appropriate name before sending the statement to the database server for processing. Take a look at the following snippet:

<?php
function getUserOrders($usrno, $status)
{
...
$sql ='SELECT * FROM users WHERE (usrid=?)AND(status=?)';
$params =array($usrno, $status);
$res =$db->query($sql, $params);
if (PEAR::isError($res)) {
die($res->getMessage());
}
...
}
?>

After processing the SQL in this snippet, PEAR::DB will produce the following statement:

SELECT * FROM users WHERE (usrid=:bind0)AND(status=:bind1);

It is interesting to note that the same statement will be generated by PEAR::DB even if you change the names of the $usrno and $status variables in the snippet.

Continuing with the example discussed in the preceding chapter, you might, for example, want to add some information about the most recently placed order to the page where users view their orders, so that each user can see who placed the most recent order and when it happened. Since this information is supposed to be shared across all database sessions and all users, it would be a good idea to cache it in a global context in the SGA memory.

To build the example discussed in this section, you need to have some data structures and PHP scripts defined in the preceding chapters. At a minimum, you must have created the items listed in the following table.

As you can see, most of the items listed in the table are either data structures or PHP scripts created in Chapter 6. So, before moving on, it is highly recommended that you read Chapter 6 and build the sample application discussed in that chapter, rather than simply creating the data structures and PHP scripts listed in the table.

Once you have all these objects created, you can proceed to the sample discussed in this section. To start with, you need to create a global context. You might do that by issuing the following SQL commands from SQL*Plus:

CONN /as sysdba
CREATE CONTEXT cxt_ord USING sec_adm.cxt_ord_pkg ACCESSED GLOBALLY;

By specifying the sec_adm.cxt_ord_pkg in the USING clause of the CREATE CONTEXT statement, you tell Oracle that only subprograms in the sec_adm.cxt_ord_pkg package can be used to manipulate the attributes of the application context created here.

By including ACCESSED GLOBALLY clause, you specify that attributes of the application context can be shared across all database sessions.

Once a global context is created, you need to define the package specified in the USING clause of the CREATE CONTEXT statement to manipulate the data held in the context.

By issuing the following statements you create the cxt_ord_pkg package in the sec_adm schema to manipulate values in the cxt_ord global context.

CONN /as sysdba

Since the cxt_ord_pkg.getRecentOrder getter function declared a bit later issues a query against the usr.accounts and usr.orders tables, you first have to grant SELECT privileges on these tables to the sec_adm schema.

GRANT SELECT ON usr.accounts TO sec_adm;

Next, you grant the SELECT privilege on the usr.orders table:

GRANT SELECT ON usr.orders TO sec_adm;

The next step is to create the cxt_ord_pkg package in the sec_adm schema.

CREATE OR REPLACE PACKAGE sec_adm.cxt_ord_pkg
AS
TYPE ord_rec_typ IS RECORD (
EMPNAME VARCHAR2(20),
ORDDATE DATE);
TYPE ord_rec_set IS TABLE OF ord_rec_typ;
PROCEDURE setRecentOrder(empname VARCHAR2,orddate DATE);
FUNCTION getRecentOrder RETURN ord_rec_set PIPELINED;
END;
/

In this example, you set up only two attributes for the cxt_ord global context, namely empname and orddate. So, you pass these two parameters to the setRecentOrder setter procedure.

You also declare the getRecentOrder package function that will query the cxt_ord context, retrieving information about the most recent order placed by the current user. If the cxt_ord context's attributes are not set, it retrieves this information from the usr.orders table, and then calls the setRecentOrder package procedure to set the attributes of the cxt_ord context to appropriate values.

Next, you create the body for the sec_adm.cxt_ord_pkg package:

CREATE OR REPLACE PACKAGE BODY sec_adm.cxt_ord_pkg IS
PROCEDURE setRecentOrder(empname VARCHAR2,orddate DATE) IS
BEGIN
DBMS_SESSION.SET_CONTEXT(
NAMESPACE => 'cxt_ord',
ATTRIBUTE => 'empname',
VALUE => empname
);
DBMS_SESSION.SET_CONTEXT(
NAMESPACE => 'cxt_ord',
ATTRIBUTE => 'orddate',
VALUE => orddate
);
END;
FUNCTION getRecentOrder RETURN ord_rec_set PIPELINED IS
ord_rec ord_rec_typ;
BEGIN
IF SYS_CONTEXT('cxt_ord', 'empname') IS NULL THEN
SELECT * INTO ord_rec FROM
(SELECT full_name as empname, orddate FROM usr.orders, usr.accounts WHERE empno=usr_id ORDER BY orddate DESC)
WHERE rownum=1;
setRecentOrder(ord_rec.empname, ord_rec.orddate);
ELSE
ord_rec.empname:= SYS_CONTEXT('cxt_ord', 'empname'),
ord_rec.orddate:= SYS_CONTEXT('cxt_ord', 'orddate'),
END IF;
PIPE ROW(ord_rec);
RETURN;
END;
END;
/

In the setRecentOrder procedure, you set up attributes within the context so that their values are available for all database sessions and all database schemas. In the getRecentOrder function, you start by checking to make sure that these values have been set up. For simplicity, in this example you only check to see if the empname attribute is set.

To access attributes of an application context, you use SYS_CONTEXT function, passing the name of the context as the first parameter and the name of the attribute as the second.

If the attributes in the context are not set, you retrieve the information about the most recent order from the usr.orders table. Normally, it happens the first time the getRecentOrder function is invoked.

In this example, when designing the query that will retrieve the row representing the most recently placed order, you rely on the fact that Oracle considers a later date greater than an earlier one. So, the query used here retrieves the first row from the subquery that selects all order records, sorting them by the orddate column in descending order.

Finally, you need to grant the EXECUTE privilege on sec_adm.cxt_ord_pkg TO app_conn. This can be done as follows:

GRANT EXECUTE ON sec_adm.cxt_ord_pkg TO app_conn;

Now that you have the sec_adm.cxt_ord_pkg package created, you can turn back to the appPageEmpOwnOrders.php script created as described in the Implementing Row-Level Security with Views section in Chapter 6, and modify it so that the updated script produces a page that will display not only the information about the orders placed by the current user but also some information on the most recent order, whoever placed it.

The following figure illustrates what this page might look like.

The following listing contains the source code for the appPageEmpOwnOrders.php script. The updated script will produce a page that might look like the previous figure:

<?php
//File: appPageOrdersCxt.php
require_once "testAuthor.php";
$ordersPage='"'."appPageEmpOrders.php".'"';
$rslt =$dbh->query("SELECT * FROM TABLE(sec_adm.cxt_ord_pkg.getRecentOrder)");

if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
$cxt =$rslt->fetchRow(DB_FETCHMODE_ASSOC);
print '<font face="Arial">';
print "The most recent order was placed by ".$cxt['EMPNAME']. " on ".$cxt['ORDDATE'];
$rslt =$dbh->query("SELECT ordno, empname, TO_CHAR(orddate, 'DD-MON-YYYY') orddate, total FROM usr.orders_emp_v");
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
print "<h3>List of orders placed by ". $a->getAuthData('FULL_NAME')."</h3>";
print '<table border="1" cellpadding="5">';
print '<tr>';
print '<th>Order No</th><th>Employee</th><th>Order Date</th><th>Order Total</th>';
print '</tr>';
while($row = $rslt->fetchRow(DB_FETCHMODE_ASSOC)){
print '<tr>';
print '<td>'.$row['ORDNO']. '</td><td>'.$row['EMPNAME'].'</td><td>'.$row['ORDDATE']. '</td><td align="right">'.number_format($row['TOTAL'], 2).'</td>';
print '</tr>';
};
print '</table>';
print '<br/>';
print "Click <a href=".$ordersPage.">here</a> to see all orders placed by all employees";
print '</font>';
?>

In the above script, you obtain information about the most recent order. For this, you query the sec_adm.cxt_ord_pkg.getRecentOrder table function like a regular database table. This query is highlighted in the above script (see previous page).

Once you know who placed the most recent order and when it happened, you can display this information.

Next, you can use the same PEAR::DB object to obtain records representing orders placed by the current user.

As mentioned earlier, the sec_adm.cxt_ord_pkg.getRecentOrder function is responsible for initializing the cxt_ord global context. The stage of initialization takes place when the above function is called for the first time. However, later, when a new order is submitted, you will need to reset the values in the context to appropriate values.

Since, for security reasons, the app_conn database schema through which the sample application connects to the database has no rights to access the usr.orders table, you first have to decide which mechanism to use when it comes to submitting a new order. A common approach to this problem is to create a stored subprogram through which the application will insert new records into the table.

By issuing the statements in the following listing, you create the newOrder procedure that will be used for inserting new records into the usr.orders table, and grant the EXECUTE privilege on this procedure to the app_conn schema.

CONN /as sysdba
CREATE OR REPLACE PROCEDURE usr.newOrder(
ordno NUMBER,
empno VARCHAR2,
total NUMBER)
IS
BEGIN
INSERT INTO usr.orders VALUES(ordno, empno, SYSDATE, total);
END;
/
GRANT EXECUTE ON usr.newOrder TO app_conn;

For simplicity, the procedure shown in the listing simply issues an INSERT statement against the usr.orders table.

Once you have the newOrders procedure created, you can move on to creating a script that will use this procedure to insert new rows into the usr.orders table.

The following figure illustrates a mock-up of the input form that might be used for entering data for a new order.

When executed, the newOrder.php script shown below produces a page containing the input form as shown in the above figure. Upon submission of the form, the script calls the newOrder procedure defined in the preceding listing, which in turn issues an INSERT statement against the orders table.

<?php
//File: newOrder.php
require_once "testAuthor.php";
require_once "HTML/QuickForm.php";
$form = new HTML_QuickForm('newOrderForm'),
$form->setDefaults(
array(
'empno' => $username
)
);
$form->addElement('text', 'ordno', 'Enter order number:', array('size' => 10, 'maxlength' => 10));
$form->addElement('text', 'empno', 'Your account name:', array('size' => 20, 'readonly'));
$form->addElement('text', 'total', 'Enter order total:', array('size' => 20, 'maxlength' => 20));
$form->addElement('submit', 'submit', 'Send'),
// Define validation rules
$form->addRule('ordno', 'Please enter order number', 'required', null, 'client'),
$form->addRule('total', 'Please enter order total', 'required', null, 'client'),
if(isset($_POST['submit'])) {
$arr_ord =array(
$form->getSubmitValue('ordno'),
$form->getSubmitValue('empno'),
$form->getSubmitValue('total')
);
$rslt =$dbh->query("BEGIN usr.newOrder(?,?,?); END;",$arr_ord);
if (PEAR::isError($rslt)) {
die($rslt->getMessage());
}
$f_name =$a->getAuthData('FULL_NAME'),
$rslt =$dbh->query("BEGIN sec_adm.cxt_ord_pkg.setRecentOrder(?,SYSDATE); END;",$f_name);
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
print "<h4>You just placed a new order!</h4>";
} else{
$form->display();
}
?>

Now, let's break into the above script and look at how it works, step by step.

By including the testAuthor.php script discussed in the Protecting Resources Based on Information about the Authenticated User section in Chapter 6, you secure the page produced by the script shown in the above listing, which guarantees that only authenticated users will be able to access the input form on this page.

In this example, you create the input form for entering a new order, using the HTML_QuickForm PEAR package. In Chapter 5 Object-Oriented Approach, you saw an example of using PEAR::HTML_QuickForm when building a custom login form to be used with a class based on PEAR::Auth.

Next, you set the default value for the empno input element of the HTM_QuickForm form created.

Once the form object is created, you can add elements to the form. In particular, you add three input boxes that will be used for entering data for a new order. Note that the empno input box element is defined as readonly, which means that the user will not be able to change its default value.

Next, with the help of the addRule method, you add validation rules to the form. The rules specified here tell the form to verify whether the ordno and total input boxes are filled, upon submission of the form.

Then, you check to see if the form has been submitted. It is important to note that if at least one of the validation rules defined earlier in this script is not satisfied, so validation fails, the form is displayed again along with an error message.

Once the form is submitted, you can retrieve the submitted elements' values, by using the getSubmitValue method. In this example, you save the entered data in an array, which is then passed to the query method as the second parameter.

You issue the PL/SQL statement through which you call the usr.newOrder procedure defined as described earlier in this section. Note the use of placeholders in the parameter list of the procedure. You supply particular values to these placeholders during execution, through the $arr_ord array.

If the PL/SQL statement issued within the script fails, you issue an error message and stop execution with the help of the die function.

If the PL/SQL statement is executed successfully, you obtain the full name of the currently authenticated user. For this, you call the getAuthData method of the PEAR::Auth object created in the included testAuthor.php script, specifying the FULL_NAME as the parameter.

Then, you reset the cxt_ord global context created as described in the Manipulating Data Held in a Global Context section earlier, so that it contains information about the order that was just inserted. To do this, you issue the PL/SQL statement that calls the setRecentOrder setter method of the sec_adm.cxt_ord_pkg package created as described in the Manipulating Data Held in a Global Context section earlier in this chapter.

Now that you know how the newOrder.php script works, it is time to put it into action. When you run the script, you first will be prompted to enter a user name and password to get authenticated. You might, for example, use maya/mayapwd account/password combination. After you are successfully authenticated, you should see the input form for entering a new order, like the one as shown in the previous figure. In the form, simply enter the order number and total for the order and then click the Send button. As a result, a new record should be inserted in the usr.orders table and you should see a message telling you about this.

Once a new record has been inserted into the usr.orders table, you might run the appPageOrdersCxt.php script discussed in the Manipulating Data Held in a Global Context section again in order to make sure that everything works as expected. This time, the message displayed below the welcome message on the page should contain information about the order that was just inserted.

Before you can start caching database result sets on the web server side, you first have to decide which caching strategy best fits your needs.

A time -triggered caching approach, which is commonly used in caching systems, implies that the cache is regenerated each time an expiry timestamp is reached. While such a caching system can be easily implemented, it is not always a satisfactory solution for those applications where using up-to-date database result sets is crucial.

In such cases, you need to employ a mechanism that will notify your application each time a change occurs to the database data the application is interested in caching, so that the application can reconcile cached, out-of-date data with the database. You can achieve this functionality with Database Change Notification, a new Oracle Database 10g Release 2 feature.

The rest of this section discusses how to implement a time triggered-caching system on the web server side with the help of the PEAR::Cache_Lite package. In the following sections, you will see how to use the Database Change Notification feature to create a notification-based caching system.

Turning back to the appPageOrdersCxt.php script discussed earlier in this chapter, you might want to update it so that it caches the information on the orders retrieved from the database in a local cache of the web server, thus reducing the overhead associated with frequent database server requests.

One way to achieve this is to encapsulate the code responsible for retrieving information about orders from the database into a separate function and then cache the results of this function, thus taking advantage of so-called caching functions calls. For example, you might create the getEmpOrders function that will query the database and retrieve the orders for the currently authenticated user, returning this information to the caller. After you create the getEmpOrders function, you can

rewrite the appPageOrdersCxt.php script so that it calls getEmpOrders and caches its results in the local cache of the web server.

This can be depicted diagrammatically, as shown in following figure

The following listing shows the source code for the script containing the getEmpOrders function, which returns information about the orders of the current user, whose results you are going to cache on the web server.

<?php
//File: getEmpOrders.php
function getEmpOrders($dsn, $user) {
$db = DB::connect($dsn);
if(DB::isError($db)) {
die($db->getMessage());
}
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$rs =$db->getAll("SELECT ordno, empname, TO_CHAR(orddate, 'DD-MON-YYYY') orddate, total FROM usr.orders_emp_v");
if (PEAR::isError($rs)) {
print $rs->getMessage();
}
return($rs);
}
?>

It is interesting to note that the second parameter passed to the getEmpOrders function is not referenced within the function code. You have to use this parameter, which contains the account name of the current user, in order to make it possible for the caching system to distinguish between the calls of getEmpOrders made to obtain the orders placed by different users.

In this script, by setting the fetch mode to DB_FETCHMODE_ASSOC, you tell the PEAR::DB object to return the resulting data as an associative array.

Next, you use the getAll method to run the query and retrieve all the data as an array, thus eliminating the need to make a separate call to a fetching method.

Now you can turn back to the appPageOrdersCxt.php script and update it to use getEmpOrders, caching its results on the web server. The following listing contains the source code for such a revision.

<?php
//File: appPageOrdersCache.php
require_once "testAuthor.php";
require_once "getEmpOrders.php";
require_once 'Cache/Lite/Function.php';
$options = array(
'cacheDir' => '/tmp/',
'lifeTime' => 300
);
$ordersPage='"'."appPageEmpOrders.php".'"';
$rslt =$dbh->query("SELECT * FROM TABLE(sec_adm.cxt_ord_pkg.getRecentOrder)");
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
$cxt =$rslt->fetchRow(DB_FETCHMODE_ASSOC);
print '<font face="Arial">';
print "The most recent order was placed by ".$cxt['EMPNAME']." on ".$cxt['ORDDATE'];
$cache = new Cache_Lite_Function($options);
if ($rslt = $cache->call('getEmpOrders', $dbh->dsn, $username)){
print "<h3>List of orders placed by ". $a->getAuthData('FULL_NAME')."</h3>";
print '<table border="1" cellpadding="5">';
print '<tr>';
print '<th>Order No</th><th>Employee</th><th>Order Date</th><th>Order Total</th>';
print '</tr>';
foreach ($rslt as $row) {
print '<tr>';
print '<td>'.$row['ORDNO']. '</td><td>'.$row['EMPNAME'].'</td><td>'.$row['ORDDATE']. '</td><td align="right">'.number_format($row['TOTAL'], 2).'</td>';
print '</tr>';
};
print '</table>';
} else {
print "Some problem occurred while getting orders!
";
$cache->drop('getEmpOrders', $dbh->dsn, $username);
}
print '<br/>';
print "Click <a href=".$ordersPage.">here</a> to see all orders placed by all employees";
print '</font>';
?>

In this example, you cache results returned by the getEmpOrders function with the help of the Cache_Lite_Function class from the PEAR package. So, before you can run the script defined in the listing, you have to install the PEAR::Cache_Lite package. As usual, this can be done with the help of the PEAR Installer. Assuming PEAR is installed in your PHP installation, you can run the following command from the command line to install the PEAR::Cache_Lite package:

$ pear install Cache_lite

The Cache/Lite/Function.php file included in the above script is created during PEAR::Cache_Lite installation. This file contains the Cache_Lite_Function class used in the script to cache getEmpOrders calls.

However, before you can create an instance of Cache_Lite_Function you have to define an array of options, which will be passed to the class constructor as the parameter. In this particular example, you set the lifeTime option to 300, which will tell the Cache_Lite_Function object to cache the results of the getEmpOrders function for 300 seconds.

Another parameter included in the array of options is cacheDir, through which you define the directory to hold cache files on the web server. In this example, you set this option to /tmp/, thus specifying the /tmp directory to be the cache directory.

Next, you call the getEmpOrders cacheable function using the call method of the Cache_Lite_Function object. The $dbh->dns and $username are the parameters passed to getEmpOrders. As mentioned earlier, the $username parameter is used only to make it possible for the Cache_Lite_Function object to distinguish between the calls of getEmpOrders made to obtain the orders placed by different users.

Next, you use the foreach construct to loop over the array returned by the getEmpOrders function.

In this example, you drop the cached data associated with the corresponding getEmpOrders function call with given parameters if the call method returns false instead of the array of orders, which in turn is returned either by the getEmpOrders function or from the local cache.

Once the data has been updated in the database, it is good practice to update the cache to make sure that it does not contain out-of-date data. The problem with the caching approach used in the preceding example is that it does not provide a way to determine when the time comes to update the cache.

To solve this problem, you can make use of the drop method of the Cache_Lite_Function class, removing from the cache a specific call of the getEmpOrders function when the data that that call is supposed to return has been changed in the database.

So, you can now turn back to the newOrder.php script discussed in the Resetting Values in a Global Context section earlier and revise it so that it removes the set of orders placed by the current user from the cache once that user has successfully inserted a new order into the usr.orders table.

The following listing shows the updated newOrder.php script that calls the drop method of the Cache_Lite_Function object to remove the out-of-date set of orders from the cache.

<?php
//File: newOrderCache.php
require_once "testAuthor.php";
require_once 'Cache/Lite/Function.php';

print '<font face="Arial">';
require_once 'HTML/QuickForm.php';
$form = new HTML_QuickForm('newOrderForm'),
$form->setDefaults(
array(
'empno' => $username
)
);
$form->addElement('text', 'ordno', 'Enter order number:', array('size' => 10, 'maxlength' => 10));
$form->addElement('text', 'empno', 'Your account name:', array('size' => 20, 'readonly'));
$form->addElement('text', 'total', 'Enter order total:', array('size' => 20, 'maxlength' => 20));
$form->addElement('submit', 'submit', 'Send'),
// Define validation rules
$form->addRule('ordno', 'Please enter order number', 'required', null, 'client'),
$form->addRule('total', 'Please enter order total', 'required', null, 'client'),
// Try to validate a form
if(isset($_POST['submit'])) {
$arr_ord =array($form->getSubmitValue('ordno'),
$form->getSubmitValue('empno'),
$form->getSubmitValue('total')
);
$rslt =$dbh->query("BEGIN usr.newOrder(?,?,?); END;",$arr_ord);
if (PEAR::isError($rslt)) {
die($rslt->getMessage());
}
$f_name =$a->getAuthData('FULL_NAME'),
$rslt =$dbh->query("BEGIN sec_adm.cxt_ord_pkg.setRecentOrder(?,SYSDATE); END;",$f_name);
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
print "<h4>You just placed a new order!</h4>";
$options = array(
'cacheDir' => '/tmp/'
);
$cache = new Cache_Lite_Function($options);
$cache->drop('getEmpOrders', $dbh->dsn, $username);

} else{
// Output the form
$form->display();
}
print '</font>';
?>

You start with including the file that contains the Cache_Lite_Function class upon which you then create an object within the second highlighted block in the above script.

Note that you don't specify the cache lifetime in the array of caching options highlighted in the script opposite. This is because a Cache_Lite_Function object doesn't need this parameter when it comes to removing a function call from the cache. Here, you specify only the cache directory to tell the Cache_Lite_Function object created in this script where the cache files reside.

To remove a specific function call from the cache, you must pass the same parameters to the drop method, called within the highlighted block opposite, as you passed to the call method when making that function call.

That is it. Now, the newOrderCache.php script will take care to remove the out-of-date data from the cache upon insertion of a new order. To make sure that it works as expected, you might perform the following steps:

As you can see, the newOrderCache.php script not only provides an input form for entering a new order, but also removes the set of orders that becomes out of date upon insertion of that new order from the cache.

It is good practice to audit notification messages issued by the change notification mechanism. To achieve this, you first have to create an audit table that will hold information about issued notifications.

By issuing the following CREATE TABLE SQL statement you create the usr.ntfresults table to be used for holding information about each notification issued.

CONN /as sysdba
CREATE TABLE usr.ntfresults (
ntf_date DATE,
tbl_name VARCHAR2(60),
emp_id VARCHAR2(40),
rslt_msg VARCHAR2(100)
);

As you can see, the table is designed to hold the following information about a notification:

The notification handler is responsible for generating an audit record based on the notification received.

Next, you might want to encapsulate the functionality associated with posting a notification to the client into a separate PL/SQL procedure, which will allow you to have a simpler and more readable notification handler.

The following listing contains the statements you have to issue in order to create the usr.postNtf PL/SQL procedure that will be called from within the notification handler and will invoke a PHP script dropping the set of orders that have become out of date, from the local cache on the web server.

CONN /as sysdba
GRANT EXECUTE ON dbms_crypto TO usr;
CREATE OR REPLACE PROCEDURE usr.postNtf(url IN VARCHAR2, sch_tbl IN VARCHAR2, usr IN VARCHAR2) IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
err_msg VARCHAR2(100);
usr_hash VARCHAR2(40);
BEGIN
BEGIN
usr_hash:= DBMS_CRYPTO.HASH (
UTL_I18N.STRING_TO_RAW (usr, 'AL32UTF8'),
DBMS_CRYPTO.HASH_SH1);
usr_hash:=NLS_LOWER(usr_hash);
req := UTL_HTTP.BEGIN_REQUEST(url||usr_hash);
resp := UTL_HTTP.GET_RESPONSE(req);
INSERT INTO ntfresults VALUES(SYSDATE, sch_tbl,
usr, resp.reason_phrase);
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION WHEN OTHERS THEN
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO ntfresults VALUES(SYSDATE, sch_tbl, usr, err_msg);
END;
COMMIT;
END;
/

In the above script, you start with granting the EXECUTE privilege on the DBMS_CRYPTO package to the usr database schema. You refer to this package from within the usr.postNtf procedure.

The usr.postNtf procedure created by the above CREATE PROCEDURE statement takes the following information through its parameters:

For security reasons, you hash the ID of the user before sending it as the parameter in the HTTP request issued by the UTL_HTTP.BEGIN_REQUEST function.

So, the usr.postNtf procedure uses the UTL_HTTP.BEGIN_REQUEST function to send a notification message to the client in the form of an HTTP request. And the usr_hash parameter in the request contains the hash of the ID of the user who just inserted a new record into the usr.orders table.

Then, the usr.postNtf procedure calls UTL_HTTP.GET_RESPONSE to obtain response information indicating whether the PHP script specified in the HTTP request has been successfully invoked. Actually, usr.postNtf is not interested in processing the whole response returned from the client. Instead, it obtains only a short message stored in the reason_phrase field of the RESP record, describing the status code. This message, among other pieces of information, is stored in the ntfresults table created as described in the Auditing Notification Messages section. However, if the UTL_HTTP package raises an error, a new record is inserted into ntfresults in the exception handler block.

Now, the last thing you have to do before proceeding to the notification handler is to grant the usr schema the privileges required to work with the change notification, and alter the system to receive notifications.

Here are the SQL statements you have to execute before you can create the notification handler. Specifically, you grant the usr schema privileges required to work with notifications and alter the system to receive notifications.

CONNECT /AS SYSDBA;
GRANT CHANGE NOTIFICATION TO usr;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO usr;
ALTER SYSTEM SET "job_queue_processes"=2;

As its name implies, the notification handler is used to handle notifications. By issuing the CREATE PROCEDURE statement shown below, you create the usr.orders_ntf_callback procedure that will then be used as the notification handler for the notifications issued in response to changes to the usr.orders table.

CONN /as sysdba
CREATE OR REPLACE PROCEDURE usr.orders_ntf_callback (ntfnds IN SYS.CHNF$_DESC) IS
tblname VARCHAR2(60);
numtables NUMBER;
event_type NUMBER;
row_id VARCHAR2(20);
numrows NUMBER;
usr VARCHAR2(40);
url VARCHAR2(256) := 'http://localhost/PHPOracleInAction/ch7/dropCachedEmpOrders.php?par=';
BEGIN
event_type := ntfnds.event_type;
numtables := ntfnds.numtables;
IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
FOR i IN 1..numtables LOOP
tblname := ntfnds.table_desc_array(i).table_name;
IF (bitand(ntfnds.table_desc_array(i).opflags, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
numrows := ntfnds.table_desc_array(i).numrows;
ELSE
numrows :=0;
END IF;
IF (tblname = 'USR.ORDERS') THEN
FOR j IN 1..numrows LOOP
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
SELECT empno INTO usr FROM usr.orders WHERE rowid = row_id;
postNtf(url, tblname, usr);
END LOOP;
END IF;
END LOOP;
END IF;
COMMIT;
END;
/

To figure out how the orders_ntf_callback notification handler created as shown above works, let's take a closer look at its code.

As you can see, the notification handler takes the SYS.CHNF$_DESC object as its parameter. Through this parameter, the notification system passes the information about the change that occurred.

The url variable declared in the orders_ntf_callback created above contains the URL that points to the PHP script that will be used to remove the out-of-date set of orders from the local cache on the web server. Notice that the URL specified here is not completed—it is assumed that the value of the par parameter will be appended to the URL during execution. If you recall from the Building a PL/SQL Procedure Sending Notifications to the Web Server section, the usr.postNtf PL/SQL procedure called from within the notification handler appends the hash value of the employee's ID to the value of the url variable.

Next, before proceeding with processing the notification information, you check to see whether the notification type is set to EVENT_OBJCHANGE, which means that the notification has been published by the database in response to changing data in an object or objects registered for notification.

If the notification type is set to EVENT_OBJCHANGE, you move on to the loop iterating through the tables registered for notification in which a change occurred.

Within the loop, you obtain the name of the modified table, getting the value of the table_name attribute of the so-called table change descriptor—an object holding change notification information related to a particular modified table. The SYS.CHNF$_DESC object, which is passed to the notification handler as the parameter, contains the table_desc_array attribute holding an array of table change descriptors. Again, in this particular example, this array will contain the only table descriptor—the one that corresponds to the usr.orders table.

Before obtaining the number of modified rows (actually, inserted and/or updated rows) in a given modified table, you check to see whether the ROWID information for those rows is available through the table change descriptor.

For the usr.orders table change descriptor, you begin a nested loop to step through all of the modified rows. You obtain the ROWID of each row and then you use this information in the WHERE clause of the SELECT query to obtain the value of the empno field in the modified row. Then, you invoke the postNtf procedure created as discussed in the Building a PL/SQL Procedure Sending Notifications to the Web Server section earlier, passing to it the following parameters: the URL defined at the beginning of the procedure, the table name obtained in the first line of the outer loop, and the empno of the modified (inserted or updated) row.

Once you have created a notification handler, you then have to create a query registration for it. In this example, you need to execute any query on the usr.orders table within the so-called registration block, specifying orders_ntf_callback as the notification handler.

By issuing the following PL/SQL block, you create a query registration for the notification handler defined in the preceding section.

CONN /as sysdba
GRANT CONNECT TO usr;
CONNECT usr/usr
DECLARE
REGDS SYS.CHNF$_REG_INFO;
regid NUMBER;
empid VARCHAR2(40);
qosflags NUMBER;
BEGIN
qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
REGDS := SYS.CHNF$_REG_INFO ('usr.orders_ntf_callback', qosflags, 0,0,0);
regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS);
SELECT empno INTO empid FROM usr.orders WHERE ROWNUM<2;
DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/
CONN /as sysdba
REVOKE CONNECT FROM usr;

By issuing the above anonymous PL/SQL block, you register the usr.orders table with the usr.orders_ntf_callback procedure. However, before you can execute this block, you have to temporarily grant CONNECT role to the usr schema and then connect as usr/usr.

In the above example, you use the qosflags variable to hold the options that are used during registration. In particular, you specify that the notification should include the ROWID information of the modified rows and notifications should persist in the database, surviving instance failure.

Then, you create a SYS.CHNF$_REG_INFO object, passing the qosflags variable to the constructor and specifying usr.orders_ntf_callback as the notification handler.

With the DBMS_CHANGE_NOTIFICATION.NEW_REG_START function, you begin a registration block. Next, you issue a query against the usr.orders table, so that it returns a single row. Finally, you end the registration by calling DBMS_CHANGE_NOTIFICATION.REG_END.

After the anonymous PL/SQL block defined in the listing is successfully executed, you can revoke the CONNECT role from the usr schema.

From now on, the usr.orders_ntf_callback procedure will be invoked whenever the usr.orders table changes. As a quick test, you might run the following INSERT statement from SQL*Plus:

CONN /as sysdba
INSERT INTO usr.orders VALUES(1045, 'maya', SYSDATE, 450.75);
COMMIT;

and then issue the following query:

SELECT * FROM usr.ntfresults;

As a result, you should see the following output:

NTF_DATE TBL_NAME EMP_ID RSLT_MSG
--------- ----------- --------- -------------
27-AUG-06 USR.ORDERS maya Not Found

This proves that the notification handler has been invoked. Not found in the rslt_msg field indicates that the script invoked by the postNtf procedure was not found. This is expected behavior in this case because you haven't yet created a dropCachedEmpOrders.php script specified in the HTTP request issued from within the postNtf procedure.