By Edward Honour
My purpose in this article is to introduce you to the development of Web-enabled
applications using Oracle WebServer 2.0, and I focus on developing applications
using the PL/SQL agent. The PL/SQL Agent lets you develop Web documents
using PL/SQL packages provided with WebServer. You will be exposed to the
creation of a simple Web page, a page that queries the database, and a form
that accepts input from the user. Table 1
shows some basic Web and WebServer terms.
With Oracle's WebServer, you can present and modify data in an Oracle database using Web technologies. You can transmit database information to the public using the Web, or distribute it internally within your organization using a corporate intranet. Web applications and documents are stored on the WebServer and transmitted to clients when requested. Distributing a new application is easy because the application resides only on the WebServer, and clients need only an
Internet browser in order to request and access applications and data.
The PL/SQL Agent is a WebServer cartridge that enables you to develop Web applications and HTML documents using PL/SQL stored procedures. The WebServer Developer's Toolkit is a set of stored packages that simplifies the creation of Web applications and in some ways reduces your need to understand HTML. The Web Request Broker (WRB) receives the request and determines how it should be processed based on the directory structure inthe URL.
Communication between a user's Web browser and a Web server happens through a protocol called Hypertext Transfer Protocol, or HTTP. HTTP can handle almost any Web tool, file format, or resource. The HTTP server and Web browser communicate and coordinate the transfer of documents. Users request Web documents by providing a Universal Resource Locator (URL), which is the address of the document, file, or other resource to be obtained by the Web server. When the Web server receives the URL, it determines how to process the request and sends the results back to the Web browser.
Within Oracle WebServer 2.0, the WebServer Listener listens to an IP address waiting for a request to process. When a request comes in, the WRB determines how it should be processed, based on the URL, and passes it to the proper WebServer cartridge for processing. WebServer cartridges allow for the processing of many different types of resources, such as Java or PL/SQL. The WRB is a high-speed mechanism for dispatching, load-balancing, and adding third-party server extensions. The WRB runs all server extensions as individual processes and thereby provides asynchronous independent processing.
When a browser requests a document from a Web server, the document must be transmitted in a format that the browser can understand. An HTML (Hypertext Markup Language) document is a standard text document with browser commands, called "tags," embedded in it. Tags control the appearance of the document in the browser. Tags designate typeface, style, format, graphics, sound, and so forth. Tags can also link the document to other documents or sections of the same document. Tags are delimited by the greater-than (">") and less-than ("<") symbols. Table 2 shows some basic HTML tags and their use.
There are too many HTML tags to list in this article; see HTML 3 How-To
by David Kervin, Jeff Foust, and John Zakour (Waite Group Press, 1996) for
a good reference for HTML programming. Some HTML tags begin a formatting
operation but require another tag to complete the operation. For example,
the <B> tag creates all text in boldface until a </B>
tag ends the operation. Tags such as IMG and A HREF
also contain parameters that control the operation of the tag.
The PL/SQL Agent and the Developer's Toolkit are powerful tools for creating Web documents. Because PL/SQL stored procedures are stored in the database and have access to Oracle data, Web documents created in PL/SQL can have unlimited possibilities. The following example shows a basic Web document that prints "Hello World" to the Web browser:
CREATE OR REPLACE PROCEDURE hello AS
BEGIN
htp.p('<HTML>');
htp.p('Hello World!');
htp.p('</HTML>');
END;
An htp.p procedure is a Developer's Toolkit procedure that
outputs its text parameter directly to the Web browser. The text printed
by an htp.p procedure will be processed by the browser as if
it were read from a text file. HTML tags contained in the output
will be processed by the browser. The first htp.p procedure call in the
example outputs the <HTML> tag declaring the beginning
of the HTML document. The second procedure call prints the
message "Hello World" on the browser. The final procedure call
closes the Web document by generating the </HTML> tag.
After the procedure is compiled and stored in the database, it can be called
from a browser with the URL localhost:80/ows-bin/owa/hello. In this URL,
the address "localhost:80" identifies the local Web server if
you are running the browser on the same computer as Oracle WebServer. If
you are running the browser from another location on the network, replace
"localhost" with the IP address of the server and "80"
with the port number established by your WebServer administrator.
The Developer's Toolkit packages let you generate HTML output without including the HTML tags in your document. Each procedure or function contained in the Developer's Toolkit generatesspecific HTML tags when executed. Table 3 shows the Toolkit packages related to the text that prints to the browser. Many procedures and functions are contained in the Developer's Toolkit. The tables in Chapter 19 of my book Oracle How-To (Waite Group Press, 1996) show the complete collection of Developer's Toolkit packages.
Although it is possible to generate your entire application using only
the procedures displayed in Table 3,
other procedures provided in the Developer's Toolkit make it easier to develop
applications without knowing the intricacies of HTML tags. For example,
the procedure HTP.HTMLOPEN is equivalent to HTP.P('<HTML>').
Throughout this article, I use the additional Developer's Toolkit procedures
and functions when possible.
Each procedure in the htp package has a corresponding function in the htf package. This lets you nest functions within the htp procedure to generate complex output. htf functions each return VARCHAR2 output, which you can use within the htp procedure.
One of the most powerful features of the PL/SQL Agent is its ability to generate HTML documents on the fly using PL/SQL. Because PL/SQL has direct access to the Oracle database, you can create documents that present information from the server. The code in Listing 1 creates a Web document that displays the system date and time queried from the database. The example is printed as it would be entered into SQL*Plus so that you can reference the line numbers.
Lines 2 and 3 declare variables to contain the date and time. Lines 4
through 16 contain the executable section of the stored procedure. The query
contained in lines 5 through 9 returns the SYSDATE pseudocolumn
formatted with the TO_CHAR function. Line 10 uses the HTMLOPEN
procedure from the HTP package to generate the <HTML> tag to begin
the document. Line 11 uses the TITLE procedure to generate
the <TITLE> and </TITLE> tags to specify
the title of the document. Line 12 uses the HEADER procedure
to generate the tags for a first-level header. Lines 13 and 14 display the
current date and time to the browser. Line 15 generates the </HTML>
tag by using the HTMLCLOSE procedure. Figure 1 shows the output
when you execute this procedure within a browser.
In Lesson 4, I showed you how you can display database information using the PL/SQL Agent. In this lesson you'll learn how to create a Web document that displays the results of a multirecord query.
The code shown in Listing 2 executes
a query of the USER_SOURCE data dictionary views and displays
all stored modules owned by the current user account. Lines 2 through 5
create a cursor in the declarative section of the procedure to query the
USER_SOURCE data dictionary view. Lines 6 through 20 contain
the executable section of the procedure. Line 7 generates the <HTML>
tag by using the HTMLOPEN procedure from the HTP package. Line
8 uses the HTITLE procedure to generate both the title tags
and heading tags for the document. Line 9 prints the <CENTER>
tag by using the P procedure. The TABLEOPEN procedure in Line
10 generates the tags to begin an HTML table. The TABLECAPTION
procedure in Line 11 creates the caption for the table. Lines 12 through
17 loop through each record returned by the cursor to display the record
as part of the HTML table. Line 13 uses the TABLEROWOPEN procedure
to begin a new row in the table. Lines 14 and 15 present the record data
in the table by using the TABLEDATA procedure. Each row of
the table is ended with the TABLEROWCLOSE procedure in Line
16. Lines 18 and 19 close the table and the document. Figure 2 shows the
output of the stored procedure after it is compiled and queried.
In lessons one through five, I used Oracle WebServer to display information from an Oracle database. In this lesson, I use HTML forms to insert information into a table within an Oracle database.
Form processing with the PL/SQL Agent requires two stored procedures. The first procedure presents the form onto the browser to accept the input; the second processes the information when it is submitted. The first procedure is shown in Listing 3 ; it generates a form containing two fields.
Line 3 uses the HTMLOPEN procedure to begin the document.
Line 4 uses the HTITLE procedure to generate both the document
title and the heading text. The FORMOPEN procedure in Line
5 begins the form area in the document. The parameter specifies the name
of the procedure that will process the form when it is submitted. In this
case, submitting the form will call the PROCESSFORM procedure.
Lines 6 and 7 creates the two fields by using the P procedure and the FORMTEXT
function from the HTF package. The FORMTEXT function
creates the tags necessary to generate a form field with the specified name.
The P procedure outputs those tags along with some boilerplate text. Line
8 generates the SUBMIT button using the FORMSUBMIT
procedure. Lines 9 and 10 close the form and document, respectively. Figure
3 shows the output of the procedure after it is compiled and executed.
Once the user enters the information and selects the SUBMIT
button, the PROCESSFORM procedure is executed. The procedure
serves two purposes: First, it inserts the data into the database; second,
it displays a success message to the user. Each field contained in the calling
form must be referenced as a parameter in the processing procedure. (See Listing 4)
Lines 2 and 3 declare the input parameters that correspond to the field
contained in the other document. An error occurs if all fields in the calling
form are not represented by parameters. In case the field is not entered,
the parameter must default to NULL. Lines 5 and 6 insert a
record into the MY_EMPLOYEE table. Line 7 opens the success
document with the HTMLOPEN procedure. Lines 8 through 11 print the success
message and the value of the fields. Line 12 closes the document.
In my next article, I will discuss techniques for passing variables between
forms, populating default values in forms, and building a WebServer form
to insert, update, and delete data.
Table 1. Some basic Web and WebServer terms.
| Term | Definition |
|---|---|
| HTTP | The protocol used to communicate between a Web Server and the client browser. |
| HTML | Hypertext Markup Language - A textual language that determines how a Web page will look. |
| Web Listener | The WebServer program that listens to an IP address for a WebServer request. |
| Web Request Broker | The WebServer program that determines which WebServer cartridge will handle the request. |
| PL/SQL Agent | The WebServer cartridge that lets a developer create Web applications using PL/SQL. |
| Developer's Toolkit | A set of PL/SQL packages supplied with WebServer that let the developer generate HTML output from PL/SQL. |
Table 2. Some basic HTML tags.
| HTML | Tag Description |
|---|---|
| <HTML> | Begins an HTML document. |
| <TITLE>Title goes here</TITLE> | Designates the title of the document. |
| <H1>Heading goes here</H1> | Designates a heading. |
| <H2>Sub-Heading goes here</H2> | Designates a subheading. The number 2 specifies second level. It can go sixlevels deep. |
| <B>bold faced text</B> | Highlights the text as bold-faced. |
| <I>italicized text</I> | Highlights the text as italic. |
| <A HREF="url">Text for link</A> | Highlights the text as a link and links to the document "url" when selected by the user. |
| <IMG SRC="graphic"> | Includes a graphic image identified by "graphic" in the document. |
Table 3. Print-related Developer's Toolkit procedures.
| Procedure | Description |
|---|---|
| htp.print | Prints a text string to the browser. |
| htp.p | Same as htp.print. |
| htp.prn | Same as htp.print, but puts a new line at the end of the string. |
| htp.prints | Prints a text string to the browser, but replaces all occurrences of the "<," ">," and "&" characters with the corresponding escape sequences so they can be displayed literally in the browser. |
| htp.ps | Same as htp.prints. |
SQL> CREATE OR REPLACE PROCEDURE dynamic AS
2 current_date VARCHAR2(40);
3 current_time VARCHAR2(40);
4 BEGIN
5 SELECT to_char(SYSDATE,'MM/DD/YY'),
6 to_char(SYSDATE,'HH24:MI:SS') INTO
7 current_date,
8 current_time
9 FROM DUAL;
10 htp.htmlopen;
11 htp.title('Dynamic Web Document');
12 htp.header(1,'Dynamic Page Demo');
13 htp.ps('Current Date is: '||current_date);
14 htp.ps('Current Time is: '||current_time);
15 htp.htmlclose;
16 END;
SQL> CREATE OR REPLACE PROCEDURE query AS
2 cursor c1 is
3 SELECT DISTINCT type, name
4 FROM user_source
5 ORDER BY name, type;
6 BEGIN
7 htp.htmlopen;
8 htp.htitle('Database Query Demo');
9 htp.p('<CENTER>');
10 htp.tableopen('BORDER');
11 htp.tablecaption('Stored Modules','CENTER');
12 FOR I IN c1 LOOP
13 htp.tablerowopen;
14 htp.tabledata(I.type);
15 htp.tabledata(I.name);
16 htp.tablerowclose;
17 END LOOP;
18 htp.tableclose;
19 htp.htmlclose;
20 END;
SQL> CREATE OR REPLACE PROCEDURE form AS
2 BEGIN
3 htp.htmlopen;
4 htp.htitle('Data Entry Form Demo');
5 htp.formopen('PROCESSFORM');
6 htp.p('Last Name: '||htf.formtext('LAST_NAME'));
7 htp.p('First Name: '||htf.formtext('FIRST_NAME'));
8 htp.formsubmit;
9 htp.formclose;
10 htp.htmlclose;
11 END;
SQL> CREATE OR REPLACE PROCEDURE processform (
2 last_name VARCHAR2 := NULL,
3 first_name VARCHAR2 := NULL) AS
4 BEGIN
5 INSERT INTO my_employee(last_name, first_name)
6 VALUES (last_name, first_name);
7 htp.htmlopen;
8 htp.htitle('Data Entry Form Results');
9 htp.p('You entered the following data:');
10 htp.ps('Last Name: '||last_name);
11 htp.ps('First Name: '||first_name);
12 htp.htmlclose;
13 END;