METHODOLOGY
JANUARY/FEBRUARY 1996

How to Build Web Applications
Using Oracle7 and Oracle WebServer

With Oracle's New WebServer and related products, it's easy to create dynamic, personalized, coherent applications and deliver them over the Internet and the World Wide Web. This simple step-by-step teaches you how.

by Jack Haverty, Oracle Internet Products

The World Wide Web has existed for only a few years, but it has already gone through several evolutions as people have learned how to use it effectively in both public and private environments. From its roots as a scheme for making graphics-rich, highly interconnected documents available, the Web has progressed to include documents whose content is built on the fly when a user requests it. Some or all of the content can come from live data, tailored by parameters obtained on the fly as well---for example, to focus a particular SQL query. These capabilities make it possible for Web users to retrieve and view focused documents such as airline or concert schedules and seat availability, news articles containing key words and phrases, and so on.

The most recent stages in the rapid evolution of the Web introduce two new refinements to the content of Web documents. First, you can now personalize Web documents, tailoring the content to a particular individual. And second, Web documents can be coherent, with the individual pages a user views over time providing the same kind of continuity that a traditional database session provides.

With these capabilities, you can use the Web as a platform for delivering applications. With the new Oracle WebServer and related products and the technology of the Web's standard protocols, Hypertext Transfer Protocol (HTTP) and Hypertext Markup Language (HTML), creating such applications is straightforward. Oracle WebServer combines the Oracle7 Server---including its automatic data replication, distributed data, and parallel-everywhere features---with the Oracle Web Agent and Web Listener, providing a complete multimedia environment for building and deploying workgroup- and enterprise-level Web applications (see "Untangling the Web," on page 33).

The Web is a basic client/server system, in which a client (usually a person using a Web browser) sends to a server a message containing a request. Simple requests are much like requests to a file server---they specify a file the client wants to retrieve. The file can contain static information such as an electronic copy of a document, or it can be formed on the fly by a procedure in the server. The file is specified by a Uniform Resource Locator (URL), which is simply a reference by name to the specific information. URLs such as http://www.oracle.com and http://www.isoc.org are examples of these requests, which users can simply type into a browser.

Users rarely type in more-complex requests; these usually are contained within the pages the server returns in response to the simple requests. The most common example of this is a form page, which contains one or more fields for user input and one or more buttons to trigger some action. The form page collects information from the user and then submits it to the server, again as a single message but containing all the information the user typed into the form (see Figure 1 below).

The Web server almost always passes these complex requests on to a program the author of the pages has provided to handle information submitted by the user. Pages that do some kind of query, such as reading a database to build a page of information about seat availability or inventory status, are the most common example of this kind of client/server interaction. Of course, there's nothing in the Web protocols or architecture to restrict such interactions to be read-only. In fact, as users increasingly apply the Web to business tasks that go beyond information retrieval, making changes to data on a server in response to input from a Web client is becoming commonplace. Registering for courses, updating personal profiles, and placing orders over the Web will become more prevalent as standards for providing secure interactions solidify.

In a classic database environment, sessions keep track of a user's actions. Between sessions, the database remembers the user's previous actions in the form of data stored in tables. When the user buys a product today, the application will remember that fact tomorrow and can offer services such as querying shipping status. Now you can use the Web as a user interface to build these kinds of applications. Some of the techniques remain the same---such as the use of database tables to remember basic information about each specific user. With the Web, however, there is no notion of a session. Web clients issue requests, and Web servers deliver the response and then forget that the interaction happened. As a Web-site builder, you must use other techniques to provide coherent, sessionlike information.

WEBPAD---A SAMPLE APPLICATION

To illustrate these techniques, we'll use fragments of WebPad, a Web application from Oracle. WebPad is the world's simplest personal information manager (PIM). It implements a notebook with sheets that contain short notes of as many as a few thousand characters. Users can give each sheet a subject, which is simply a one-line summary of what a note contains. To users, WebPad appears as a collection of several Web pages, which allow them to register (start a new notebook), enter notes, look at an index of their notes, and look at and modify existing notes. All of this data is stored in Oracle7 tables, and all of the Web pages are created and handled by a collection of PL/SQL procedures working inside an Oracle WebServer.

In this discussion, we'll look in detail at several pieces of the WebPad application, beginning from the user's first encounter. The user needs to be identified to the application to get started, and then the application can take charge, using the techniques of Oracle7 and the Web standards to deliver a personalized, coherent experience to the user. The WebPad application is simple, but since the data is all kept in Oracle7 and manipulated by PL/SQL procedures, it's pretty easy to imagine all sorts of extensions to WebPad---such as links between different user's WebPads, searching and viewing facilities, and so on.

Registering

Rudimentary techniques for identifying users are available in Web standards today but are intended primarily for use in controlling access to documents. Web-site builders can restrict access so that when a user attempts to access a protected document, the browser pops up a dialog box to ask for a name and password it will pass along to the server.

Maintaining individual names and passwords for a large number of users is impractical. With a database in the picture, however, it becomes fairly simple to create individual accounts. A typical application, for example, might have a signup page, which is a form that asks the user to select a name and password, and may take other information as well---such as address and phone number. To implement this with WebServer, you need to create two components: the form, which asks for the information, and the procedure, which processes the form once a user has filled it in.

The Signup Form

The signup form can be a static document, but it's just as easy to create it via a PL/SQL stored procedure, and it can be dynamic as well---for example, it might contain the date/time and news of the day for the application's users. To get started, the user has to sign up up for the notepad service. Here's an example of a fragment of a PL/SQL procedure, using the Web Agent facilities of Oracle WebServer to generate a signup form:

procedure RegUser is begin: htp.htmlOpen;
htp.headOpen;
htp.title('WebPad Registration');
htp.headClose;
htp.bodyOpen;
htp.FormOpen(owa_util.get_owa_service_path ||
'PadGreet.NewUser');
htp.print('Your Name: ');
htp.FormText('U',25,50);
htp.nl;
htp.print('Password ---you choose, can be blank if you like...): ');
htp.FormPassword('P',15,25);
htp.nl;
htp.FormSubmit;
htp.FormReset;
htp.FormClose;
htp.bodyClose;
htp.htmlClose;


end;

This procedure would typically be activated by a Web client attempting to access a top-level Web page that is actually a call to this PL/SQL procedure, which generates the HTML/Web page to put a fill-in form up on the screen asking for a name and password (see Figure 2 below). This form contains three important items:

Identification of the procedure to be used to process the contents of the form (padgreet.newuser---the NewUser procedure in the padgreet package).

Identification of the name of the parameter that will contain the name (in this case, U).

Identification of the name of the parameter that will contain the password (similarly, P).

When the user fills in the form and clicks on Submit, the browser collects all the information that was typed and sends it off to the Web server. This results in the PL/SQL procedure NewUser being called, with the PL/SQL variables U and P containing the strings the user typed.

The Signup Handler

That PL/SQL procedure can do almost anything, but typically it will put the information about the user into Oracle7 tables. Since users don't have unique names and you don't want to make them remember strange IDs, you can use the database facilities (in particular, a SEQUENCE) to assign a unique ID number. Here's the PL/SQL code that handles this:

/* NewUser - enter a new user into the database, if invalid send out registration form again */ procedure NewUser(U in varchar2 default NULL,: P in varchar2 default NULL) is uid integer;



begin 

	
select usrid into uid from usrinfo 

		where usrname = upper(U) and usrpsw=upper(P); 

RegUser; 
exception 

	
when no_data_found then /* no conflicts, try to add the user */ 

	if (length(U) > 0) then 

		begin	/* valid user/password */ 

			insert into USRS values ( 

				SEQUSRID.NEXTVAL, 

				SYSDATE); 

			select SEQUSRID.CURRVAL into uid from dual; 
			insert into USRINFO values( 

				upper(U), 

				upper(P), 

				uid);

			insert into USRVISITS values(uid, SYSDATE); 

			htp.htmlOpen; 

			htp.headOpen; 

			htp.title('WebPad New User'); 

			htp.headClose; 

			htp.bodyOpen; 

			htp.print('Greetings! You are the number ' 

				|| to_char(uid) 

				|| ' user of WebPad!'); 

			htp.nl; 

			htp.hr; 

			htp.anchor(owa_util.get_owa_service_path 

				|| 'PadGreet.GreetUser?U='

				|| U || chr(38) || 'P=' || P, 

				'Go to Central...'); 
		end; 

	else RegUser; 

	end if; 

end;

This procedure allocates a unique user ID by using a SEQUENCE and stores the ID and current date/time. It saves the user name and password in the USRINFO table, along with the unique integer obtained from a SEQUENCE, and records the fact that this user visited this application at this date/time. It also composes a simple Welcome page, containing an anchor (a hot link) that the user can subsequently click on, or save as a bookmark, and use as a top-level entry point to the personalized application.

The anchor in the sample above provides an important facility for personalizing Web sessions as well as for making the sequence of pages coherent. Using the PL/SQL shown here, the procedure embeds the user name and password in the anchor itself---so that this information is automatically provided when the user next accesses the anchor.

htp.anchor(owa_util.get_owa_service_path0: || 'PadGreet.GreetUser?U=' || U || chr(38) || 'P=' || P, 'Go to Central...');

The URL this procedure creates might look something like http://inetprod.us.oracle.com/jack/owa/PadGreet.GreetUser? U=JHAVERTY&P=WELCOME, but the user never even has to see this jargon. All the user sees is a hot link that says Go to Central, which he or she can use at any time to get into this application (see Figure 3).

The User's Home Page for the Application

The user accesses the next page in the sequence by clicking on the Go to Central link, which causes the following procedure to run inside Oracle WebServer's database:

procedure GreetUser(U in varchar2,: P in varchar2 default NULL) is


uid integer; 

uname varchar2(25); 

ushcount integer; 

upsw varchar2(25);
 
begin 

	
select usrid into uid from usrinfo 

		where usrname = upper(U) and usrpsw = upper(P); 

	select usrname,usrpsw into uname,upsw from usrinfo 

			where usrid=uid; 

	select count(*) into ushcount from sheets where owner=I; 

	PadHome(usrid); 

	htp.br; 

	htp.header(2,'Vital Statistics of ' 

			|| uname 

			|| chr(39) 

			|| 's WebPad'); 

	htp.ulistOpen; 

	htp.listItem(ushcount || ' sheets saved'); 

	htp.ulistClose; 

	/* Other info to go here */ 

	htp.nl; 

	PadMenu(usrid); 

	PadTail(usrid); 
exception     /* no such user or mistyped */ 

	
when no_data_found then GreetUser; 
end;

In this procedure, the real work of the application begins to be visible---a simple notepad with sheets that contain notes the user has written. This top-level page presents a summary of the user's pad, with basic information about its contents and a menu of actions the user can take.

From this point on, the server uses the unique user ID. It similarly passes this variable back to the client embedded in URLs and associated hot links so that when a user accesses an item, the corresponding PL/SQL procedure that is activated will have the user's unique ID as a parameter. The procedure below handles the menu command to list the contents of the user's notepad. Note that this is called with one argument: the user's ID in the parameter I.

/* ListSheets - list all sheets */

procedure ListSheets (I in integer) is this sheets%rowtype; cursor s is select * from sheets where owner = I order by cdate desc; uname varchar2(25); sname varchar2(120);

begin: select usrname into uname from usrinfo where usrid = I; WebPad.PadTop(I,uname || ' sheets'); htp.ulistOpen; open s; loop fetch s into this; exit when s%notfound; if this.name is null then sname := '---NONE---'; else sname := this.name; end if; htp.listItem(to_char(this.cdate,'DY, DD YY HH:MI AM') || '---' || htf.anchor(owa_util.get_owa_service_path || 'PadSheet.ShowSheet?I=' || to_char(I) || chr(38) || 'S=' || to_char(this.id), sname)); end loop; htp.ulistClose; close s; WebPad.PadMenu(I); WebPad.PadTail(I);

 
end;

This procedure creates a simple page containing a list of the user's sheets, specifying each with the date it was created and its topic, extracted from the SHEETS table. Each reference is a hot link to the procedure PadSheet.ShowSheet, with the appropriate arguments so that procedure can query the database, retrieve the contents, and return it to the user as a Web page.

NOW BUILD YOUR OWN

You can use these techniques with the Oracle WebServer to create Web applications that are dynamic, personalized, and coherent---real applications such as are traditional for Oracle7 environments---but delivered to end users via the new medium of the Internet and the World Wide Web. In both the public environment of the global Internet and private environments of corporate clones of the Internet, these techniques enable you to combine the power of Oracle7 and Web technology to create powerful applications that are amazingly simple to build.

Jack Haverty is an industry specialist for the Internet Products Group at Oracle. You can reach him via e-mail at [email protected].

This is a copy of an article published @ http://www.oracle.com/