7. The PL/SQL Toolkit

Contents:
Communicating with the Outside World
Text Processing
Maintaining State
Improving Productivity

The PL/SQL toolkit is a set of PL/SQL packages supplied by Oracle for use in developing web applications. These packages are used to generate HTML dynamically, perform text operations, and improve developer productivity. Table 7.1 shows an alphabetical listing of the packages included in the PL/SQL toolkit, along with an explanation of their uses.

Table 7.1: PL/SQL Toolkit Packages
Package Name	Use
HTF	Parses HTML
HTP	Generates HTML
OWA_COOKIE	Stores cookies
OWA_OPT_LOCK	Performs record locking
OWA_PATTERN	Searches and replaces text
OWA_SEC	Manages security
OWA_TEXT	Represents text
OWA_UTIL	Improves productivity

The sections that follow group these packages in categories according to their functionality. HTF and HTP are used for communicating with the outside world; OWA_TEXT and OWA_PATTERN are used for text processing; OWA_COOKIE and OWA_OPT_LOCK are used for maintaining state. The last two packages, OWA_UTIL and OWA_SEC, are used for maintaining productivity and security.

In addition to learning how to use dozens of procedures, we'll keep an eye on what these packages can teach us about good design. After all, the developers who created these packages are some of the most talented PL/SQL programmers in the world. We would be wise to learn from their examples.

7.1 Communicating with the Outside World

When scripting languages like Perl are used to develop dynamic resources, their output is sent to the standard output ( stdout ) device, then funneled back to the browser. Because PL/SQL cannot communicate directly with stdout , the toolkit includes a package, called HTP, that mimics this behavior.

7.1.1 HTP: Generating HTML

The HTP package is a sort of web-enabled version of DBMS_OUTPUT, a built-in package that provides basic output capabilities such as printing text. Like DBMS_OUTPUT, HTP contains commands that store text in a buffer. When a procedure using the package terminates, the contents of the output buffer are "printed" and returned to the user. This buffering is one difference between PL/SQL toolkit programs and standard CGI programs, which immediately return output to the user. As such, the size of the buffer limits the size of a page. In most cases, this is not a problem; however, you should be aware that if you choose to dump a million-row table onto a single page, you will quickly encounter this limit. Table 7.2 shows the procedures included in the HTP package; HTP also includes a large number of wrapper procedures that correspond to various HTML tags.

Table 7.2: Various HTP Procedures
Procedure	Parameters	Description
ANCHOR	Depend on tag	Generates an anchor tag
PRINT	Any value	Outputs any value passed as a parameter
Various wrapper procedures (e.g., HTMLOPEN)	Depend on tag	Simplifies coding of an HTML tag

The HTP procedure PRINT, which is analogous to DBMS_OUTPUT.PUT_LINE, simply outputs the value that is passed as a parameter. Here, for example, is a procedure that generates a page that prints "Hello, World!":

CREATE OR REPLACE PROCEDURE hello_world IS BEGIN    HTP.print ('<html>');    HTP.print ('<head>');    HTP.print ('<title>You knew it was coming...</title>');    HTP.print ('</head>');    HTP.print ('<!-- ');    HTP.print ('This phrase is in every computer book.');    HTP.print ('--!>');    HTP.print ('<body bgcolor=blue>');    HTP.print ('And here it is .... Hello, World!');    HTP.print ('</body>');    HTP.print ('</html>'); END;

More sophisticated tags require parameters to be included in the wrapper procedure. Each parameter corresponds to a particular tag attribute. As a general rule, a parameter is named after the HTML attribute it represents and is used to complete a template based on the tag's syntax. This parameter can take any valid PL/SQL value, including a literal, variable, concatenation, or function. Optional attributes are declared as DEFAULT NULL.

To make the HTML syntax more palatable to Oracle developers, HTP has a number of specialized wrapper procedures that correspond to individual tags. These procedures hide HTML's ugly syntax from developers, who are more familiar with PL/SQL and other 3GLs. For example, rather than embedding <html> directly into a program, as we've done in the previous example, the HTP package provides a more aesthetically pleasing procedure called HTMLOPEN to perform the same function. Other HTP wrapper procedures include HTP.HTMLCLOSE for </html> , and HTP.HEADOPEN and HTP.HEADCLOSE for <head> and </head> , respectively.

Although there are many benefits in using an API to isolate programs against underlying changes, the wrapper procedures often cause more problems than they prevent. During development, you may find yourself flipping through manuals to figure out the order of a particular procedure's parameters or trying to match some obscure tag to its toolkit equivalent. Once you locate the procedure, you often find that there is no clear way to create the complex nesting required by many of the most useful tags, such as those for forms or tables. In general, code is simply much more readable if you can see the actual HTML, rather than hiding it away behind a complex API.

By convention, the parameters for these attributes are preceded by a single character indicating the parameter's datatype. VARCHAR2 parameters, denoted by a "c," are by far the most common. "N" and "d" denote, respectively, the integer and date datatypes, and appear mainly in overloaded or specialized procedures.

Here, for example, is the declaration -- as it appears in the HTP specification -- for a wrapper procedure that generates an anchor (<a> ) tag:

PROCEDURE anchor (    curl IN VARCHAR2,    ctext IN VARCHAR2,    cname IN VARCHAR2 DEFAULT NULL,    cattributes IN VARCHAR2 DEFAULT NULL    )

The parameter values are used to complete a template based on the tag the procedure represents. The ANCHOR procedure has the following template:

<a href="curl" name="cname" cattributes>ctext</a>

Calling the ANCHOR procedure with the following values returns a link to the O'Reilly home page on the Web:

HTP.anchor (    'www.oreilly.com',    'O' || CHR (39) || 'Reilly Homepage',    'oreilly_link',    'target=_blank' );

The URL for the link is http://www.oreilly.com , its name is oreilly_link, and its text (the part that shows up on the user's screen) is "O'Reilly Homepage." The call uses the cattributes parameter to cause the page to open in a new window. Since HTML has such a flexible syntax that a tag may have dozens of optional attributes, most of the procedures in the HTP package include the cattributes parameter as a sort of catch-all within the tag. Here is the URL returned by the call:

<A HREF='www.oreilly.com' NAME='oreilly_link' target=blank>O'Reilly Homepage</A>

Individually, these commands are of limited use. You can combine the various procedures, however, to create a complete page. Here is the "Hello, World" program written using the procedures from HTP:

CREATE OR REPLACE PROCEDURE hello_world2 IS BEGIN    HTP.htmlopen;    HTP.headopen;    HTP.title ('You knew it was coming...');    HTP.headclose;    HTP.comment ('This phrase is in every computer book.');    HTP.bodyopen (cattributes => 'body bgcolor=blue');    HTP.print ('And here it is .... Hello, World!');    HTP.bodyclose;    HTP.htmlclose; END;

The other wrapper procedures in the package work in exactly the same way as the ANCHOR procedure. In the next section, we'll concentrate on how to make the best use of the wrapper procedures, rather than focus on the gritty details of their API.

7.1.2 WebAlchemy

As you can imagine, developing sophisticated interfaces by translating raw HTML into its PL/SQL equivalent is enormously tedious. Fortunately, there is a free tool that does much of this work: WebAlchemy, written by Alan Hobbs of Oracle Consulting, Australia, translates a static HTML file into a corresponding PL/SQL procedure. By combining WebAlchemy with any of the dozens of GUI-based HTML editors, you can create complex screens in PL/SQL quickly and easily.

WebAlchemy is simple enough to use that you probably won't even need any documentation. Figure 7.1 shows the main screen, whose menu options should be familiar to any user of PC software. Using the program is simply a matter of opening an HTML file and using the "Generate PL/SQL" option to generate a corresponding procedure. Figure 7.1 illustrates this procedure. The raw HTML file, hello_world.html , appears on the left panel; the right panel shows the PL/SQL procedure created by the "Generate PL/SQL" option.

Figure 7.1: WebAlchemy main screen

You can download WebAlchemy from:

http://www.users.bigpond.com/ahobbs/

At first blush, WebAlchemy inspires a sense of euphoria, because it promises to eliminate the need to know both HTML and the PL/SQL toolkit. However, although WebAlchemy is useful for creating static pages whose layout is known ahead of time, most programs generate documents dynamically from information stored in a table. There is simply no getting around the fact that you must understand how to manually construct an HTML document. Fortunately, this is not particularly difficult.

7.1.3 HTF: Parsing HTML

The HTF package turns HTP procedures into functions that return the HTML output as a formatted string. Table 7.3 summarizes the functions available in the HTF package.

Table 7.3: Various HTF Functions
Function	Parameters	Description
ANCHOR	Depend on tag	Stores anchor tag as a string
PRINT	Any value	Stores any value in a string

For example, the following procedure stores the results of the HTF.ANCHOR function in a string, and then uses the PL/SQL built-in SUBSTR function to print the result on two lines, using the DBMS_OUTPUT package:

CREATE OR REPLACE PROCEDURE htf_test IS    anchor_string VARCHAR2(500); BEGIN    anchor_string :=       HTF.anchor (          'http://www.ora.com',          'O' || CHR (39) || 'Reilly',          'ora_link',          'target=_blank'       );    DBMS_OUTPUT.put_line (SUBSTR (anchor_string, 1, 29));    DBMS_OUTPUT.put_line (SUBSTR (anchor_string, 30, 50)); END;