How-To use PL/SQL APIs to invalidate cached content.
Using PL/SQL API to invalidate cached content
Date: January 28, 2003
How to use PL/SQL API to invalidate cached content.
After completing this How-To you should be able
to:
Know about the PL/SQL API to invalidate the cached content.
Run the sample code to do the same using
the instructions provided.
Readers should be familiar with
To understand this document the readers should
have the basic idea about HTML, JSP, JavaScript, Java, JDBC, SQL.
Introduction
OracleAS Web Cache improves the scalability,
performance and availability of e-business Web sites. Legacy system caches
only static content but OracleAS Web Cache can cache static as well as dynamic
content and accelerates the delivery of both static and dynamically generated
web page, Key benefits includes.
Higher throughput
Shorter response times
Greater reliability
Lower infrastructure costs
OracleAS Web Cache can be deployed in a central
data center or in branch office locations, OracleAS Web Cache uses caching,
page assembly, invalidation, and compression technologies to speed the
delivery of dynamically generated content. Built-in clustering, load balancing,
and fail over features enhance application availability.
OracleAS Web Cache provides PL/SQL Application
Program Interfaces (APIs) to enable you to better customize applications
for OracleAS Web Cache invalidation features. The <ORACLE_HOME>/webcache/toolkit
directory contains the following APIs and Document Type Definition (DTD)
files for invalidation with OracleAS Web Cache. This How-To explains how
can you use PL/SQL API to invalidate the content.
Description
The example depicts the product information system.
It displays product listing available in the database and user can add,
modify and delete these information. All the dynamically generated pages
like product-listing page, product detail pages are cached using the OracleAS
Web Cache except for addConfirmation.jsp , modifyConfirmation.jsp and deleteConfirmation.jsp.
When the content is cached in OracleAS Web Cache, the application must
make sure to invalidate the content as it changes. For example, the product-listing
page (welcome.jsp) will change when new products are added/removed in the
system or product details are modified.
The sample application has to invalidate cached
pages in OracleAS Web Cache when the product catalog changes. The invalidation
of the cached pages is done by sending the XML invalidation messages to
the specified invalidation port of OracleAS Web Cache Server. In the sample
PL/SQL API (wxvutil.sql) is used to create and send these XML invalidation
messages. Whenever the data gets added, modifies or deleted, database trigger
associated with the products table, call PL/SQL API to invalidate the cached
content. When these pages are invalidated, fresh copy of the content will
be requested from the origin server for the next request, cached in the cache
server and served to the client. Following table lists the web pages and
java class used in this sample.
welcome.jsp
This JSP displays product listing available.
addData.jsp
This JSP adds new product information in the database.
addConfirmation.jsp
This JSP displays the message for the data that you are trying
to add is added successfully or not.
modifyData.jsp
This JSP modifies the product information.
modifyConfirmation.jsp
This JSP displays the message for the data that you are trying
to modify is modified successfully or not.
deleteConfirmation.jsp
This JSP deletes the data and displays the data that you are
trying to delete is deleted successfully or not.
DbInteraction.java
This java file contains all the functions related to database
business logic.
ConnectionDb.java
This java Bean stores the business logic to connect database.
ConnectionManager.java
This is an Interface used to establish database connection and
close connection.
invalidation-trigger.sql
This SQL script does the following things:-
1) Creates a table called PRODUCT_INFO_TABLE that will be used by the
current sample application.
2) Inserts sample data into PRODUCT_INFO_TABLE table.
3) User will be asked to enter the value or OracleAS Web Cache host
name , invalidation port and invalidation password. These values will be
stored in temporary variables named webcachehostname , invalidationport and
invalidationpassword.
4) Creates two triggers named as WELCOME_INVALID_TRIG , MODIFY_INVALID_TRIG.
These triggers calls procedures defined in wxvutil.sql API.
Following table shows the code snippet for WELCOME_INVALID_TRIG
trigger.
CREATE OR REPLACE TRIGGER
WELCOME_INVALID_TRIG
AFTER DELETE OR INSERT OR UPDATE on PRODUCT_INFO_TABLE FOR EACH
ROW
BEGIN
wxvutil.invalidate_reset;
wxvutil.invalidate_uri('/PlSqlInvalidation/welcome.jsp',
0, null);
wxvutil.invalidate_exec('&webcachehostname',
&invalidationport, '&invalidationpassword');
END;
/
WELCOME_INVALID_TRIG trigger will be called whenever there is any
insert , update or delete operation on any row of PRODUCT_INFO_TABLE table,
which inturn will invalidate the welcome.jsp page.
Following table shows the code snippet for MODIFY_INVALID_TRIG
trigger.
CREATE OR REPLACE TRIGGER
MODIFY_INVALID_TRIG
AFTER UPDATE on PRODUCT_INFO_TABLE
FOR EACH ROW
BEGIN
wxvutil.invalidate_reset;
wxvutil.invalidate_uri('/PlSqlInvalidation/modifyData.jsp?modifyPid='
|| :old.PRODUCT_ID, 0, null);
wxvutil.invalidate_exec('&webcachehostname',
&invalidationport, '&invalidationpassword');
MODIFY_INVALID_TRIG trigger will be called whenever there is any
update operation in any row of PRODUCT_INFO_TABLE table, which inturn will
invalidate the modifyData.jsp page and productDetail.jsp page for given
product id.
Pre-requisites for running the example
You will need the following to run this example -
Oracle Application Server (9.0.3 or above). This can be downloaded
from here
Oracle JDeveloper (9.0.3 or above). This can be downloaded from here.
This is optional and required only if you wish to deploy from JDeveloper.
Deploying the Sample
The complete source code for this sample is available
here. This section discusses the instruction
to run the sample application
Extract JAR
Unjar PlSqlInvalidation.jar using Winzip,
or using the following command:
> jar xvf PlSqlInvalidation.jar
This creates a directory PlSqlInvalidation.
Database Setup
Connect to the database that you would be
using. For example, you can connect to oe/oe or any other user and follow
the steps given below to setup the database.
If you are using JDeveloper then, follow these steps
Open Oracle9i JDeveloper and use 'File/Open' option to select the
PlSqlInvalidation.jws from the PlSqlInvalidation
directory.
Next, select Project/Make PlSqlInvalidation.jpr from main
menu.
Need to create an application server connection in case if
you don't have it.
Go to Connections -> Application Server Connection. Right Click
and 'click New connection'.
Enter connection name and select 'Oracle9i Application
Server'.
Enter user name and password
Enter Enterprise Manager's host name, port number and
Remote Application Server home directory (required only if deployed on remote
machine).
Click 'Next' and 'Test connection'. Click Finish button.
Need to create database connection in case if you don't have
it.
Go to Connections -> Database. Right Click and click 'New connection'.
Enter Connection name as 'plsql' and select Connection
Type as 'Oracle(JDBC)'.
Enter user name and password and click 'Next'.
Select Driver as 'thin', and enter Host Name, JDBC Port
and SID. Click 'Next'.
Click 'Test connection'. If it says 'Success!' then congratulations.
Click Finish button.
Now, Right click PlSqlInvalidation.deploy and select Deploy
to <connection name>, to the application server connection, which
you have created in the previous step.
Deployment using EM
To deploy the application using EM, follow
the steps.
From the <SAMPLE_HOME>/deploy directory, ExtractPlSqlInvalidation.ear jar xvf PlSqlInvalidation.ear META-INF/data-sources.xml
Edit <SAMPLE_HOME>/deploy/META-INF/data-sources.xml.
You need to change the following parameters in data-sources.xml.
url - is the database connection string containing database
host name, port and SID.
username - is the user name.
password - is the password.
Update the ear and war files jar uvf PlSqlInvalidation.ear META-INF/data-sources.xml
Go to EM web site -> default oc4j home.,
Choose Deploy EAR file
Enter J2EE Application name, Click the Browse button and choose
PlSqlInvalidation.ear from <SAMPLE_HOME>/deploy/PlSqlInvalidation.ear
Enter the Application name as PlSqlInvalidation
and click next
Enter URL mapping as /PlSqlInvalidation
and click Finish
Click Deploy to deploy the application.
Running the Sample
Access the page on the browser, using the following
URL
Bookmark
