5
Using the PL/SQL Web Toolkit

Before you can use mod_plsql, you must install the packages in the PL/SQL Web Toolkit in a common schema called owa_public in your Oracle database. Public synonyms are used to enable users to execute the objects in the common schema. Users execute the objects in the common schema with their own privileges, rather than with the privileges of the common schema.

If multiple instances of the PL/SQL Web Toolkit are installed in the database, it is recommended that you drop earlier packages from the individual schemas.

5.1 PL/SQL Web Toolkit Installation

If you did not install the PL/SQL Web Toolkit when you installed mod_plsql, you can install it using the owaload .sql installation script. See "Installing required packages" for more information.

5.2 Packages in the Toolkit

The PL/SQL Web Toolkit contains the following packages:

Package	Description
htf and htp	The htp (hypertext procedures) package contains procedures that generate HTML tags. For instance, the htp.anchor procedure generates the HTML anchor tag, <A>. The htf (hypertext functions) package contains the function version of the procedures in the htp package. The function versions do not directly generate output in your web page. Instead, they pass their output as return values to the statements that invoked them. Usethese functions when you need to nest calls. To print the output of htf functions, call them from within the htp.print procedure, which simply prints its parameter values to the generated web page.
owa	Contains subprograms required by mod_plsql.
owa_content	Contains functions and procedures that let you query the content service repository and manipulate document properties.
owa_sec	Contains subprograms used by mod_plsql for authenticating requests. Note This package is included when you install the Toolkit with OAS. mod_plsql does not use it.
owa_util	Contains utility subprograms. It is divided into the following areas: Dynamic SQL utilities enable you to produce pages with dynamically generated SQL code. HTML utilities enable you to retrieve the values of CGI environment variables and perform URL redirects. Date utilities enable correct date-handling. Date values are simple strings in HTML, but should be properly treated as a data type by the Oracle database.
owa_pattern	Contains subprograms that you can use to perform string matching and string manipulation with regular expression functionality.
owa_text	Contains subprograms used by owa_pattern for manipulating strings. They are externalized so you can use them directly
owa_image	Contains subprograms that get the coordinates of where the user clicked on an image. Use this package when you have an imagemap whose destination links invoke a mod_plsql.
owa_cookie	Contains subprograms that enable you to send HTTP cookies to and get them from the client's browser. Cookies are opaque strings sent to the browser to maintain state between HTTP calls. State can be maintained throughout the client's session, or longer if an expiration date is included. Your system date is calculated with reference to the information specified in the owa_custom package.
owa_opt_lock	Contains subprograms that enable you to impose database optimistic locking strategies, so as to prevent lost updates. Lost updates can occur if a user selects and then attempts to update a row whose values have been changed in the meantime by another user.
owa_custom	Contains the authorize function and the time zone constants used by cookies. Note This package is included when you install the Toolkit with OAS. mod_plsql does not use it.

5.2.1 htp and htf packages

The htp and htf packages provide subprograms that enable you to generate HTML tags from your stored procedure. For example, the following commands generate a simple HTML document:

create or replace procedure hello AS
BEGIN
    htp.htmlopen;           -- generates <HTML>
    htp.headopen;           -- generates <HEAD>
    htp.title('Hello');     -- generates <TITLE>Hello</TITLE>
    htp.headclose;          -- generates </HEAD>
    htp.bodyopen;           -- generates <BODY>
    htp.header(1, 'Hello'); -- generates <H1>Hello</H1>
    htp.bodyclose;          -- generates </BODY>
    htp.htmlclose;          -- generates </HTML>
END;

These packages also provide print procedures (such as htp.print), which writes its argument to the current document. You can use these print procedures to generate non-standard HTML, to display the return value of functions, or to pass hard-coded text that appears in the HTML document as-is. The generated text is passed to mod_plsql, which then sends it to the user's browser.

5.2.2 owa_image package

The owa_image package contains subprograms that get the coordinates of where the user clicked on an image. You use this for image maps that invoke mod_plsql. Your procedure would look something like:


create or replace procedure process_image
    (my_img in owa_image.point)
    x integer := owa_image.get_x(my_img);
    y integer := owa_image.get_y(my_img);
begin
    /* process the coordinate */
end

5.2.3 owa_opt_lock

The owa_opt_lock package contains subprograms that enable you to impose database optimistic locking strategies, so as to prevent lost updates. Lost updates can occur if a user selects and then attempts to update a row whose values have been changed in the meantime by another user.

mod_plsql cannot use conventional database locking schemes because HTTP is a stateless protocol. The owa_opt_lock package works around this by giving you two ways of dealing with the lost update problem:

The hidden fields method stores the previous values in hidden fields in the HTML page. When the user requests an update, mod_plsql checks these values against the current state of the database. The update operation is performed only if the values match. To use this method, call the
owa_opt_lock.store_values procedure.
The checksum method stores a checksum rather than the values themselves. To use this method, call the owa_opt_lock.checksum function.

These methods are optimistic. That is, they do not prevent other users from performing updates, but they do reject the current update if an intervening update has occurred.

5.2.4 owa_custom

Note This package is included when you install the Toolkit with OAS. mod_plsql does not use it.

The owa_custom package contains the authorize function and the time zone constants used by cookies. Cookies use expiration dates defined in Greenwich Mean Time (GMT). If you are not on GMT, you can specify your time zone using one of these two constants:

If your time zone is recognized by Oracle, you can specify it directly using dbms_server_timezone. The value for this is a string abbreviation for your time zone. (See Oracle Server SQL Reference for a list of recognized time zones. For example, if your time zone is Pacific Standard Time, you can use the following:

dbms_server_timezone constant varchar2(3) := 'PST'

If your time zone is not recognized by Oracle, use dbms_server_gmtdiff to specify the offset of your time zone from GMT. Specify a positive number if your time zone is ahead of GMT, otherwise use a negative number.

 dbms_server_gmtdiff constant number := NULL

After making the appropriate changes, you need to reload the package.

5.2.5 owa_content

Note This package is included when you install the Toolkit with OAS. mod_plsql does not use it.

The owa_content package contains functions and procedures that let you query the content service repository and manipulate document properties. You can use this package to perform tasks, like:

set a document description
delete documents
delete document attributes
retrieve attribute information
list document attributes
retrieve content type of a document

When compiling PL/SQL procedures and packages that use the owa_content package, you may get the following error message:

PLS-00201 
identifier `WEBSYS.OWA_CONTENT' must be declared

To avoid this error, when creating a new DAD that uses a non local database, you must enter the SYS username and corresponding password when prompted for a DBA user. Entering the SYSTEM user will not allows the correct grant and rights to be assigned to the database user. If you have entered SYSTEM as the DBA user then you must explicitly perform the grant privilege option as shown below:

SQL>grant all on WEBSYS.OWA_CONTENT to scott

If you are creating a DAD using an existing database user, you must perform the manual grant privilege shown above before using the OWA_CONTENT package.

The PL/SQL samples use the OWA_CONTENT package; so, these steps must be performed before installing the PL/SQL samples.

5.3 Conventions for Parameter Names in the Toolkit

In the PL/SQL Web Toolkit, the first letter of the parameter name indicates the data type of the parameter:

Table 5-1

First character	Datatype	Example
c	VARCHAR2	cname IN VARCHAR2
n	INTEGER	nsize IN INTEGER
d	DATE	dbuf IN DATE

5.4 HTML Tag attributes

Many HTML tags have a large number of optional attributes that, if passed as individual parameters to the hypertext procedures or functions, would make the calls cumbersome. In addition, some browsers support non-standard attributes. Therefore, each hypertext procedure or function that generates an HTML tag has as its last parameter cattributes, an optional parameter. This parameter enables you to pass the exact text of the desired HTML attributes to the PL/SQL procedure.

For example, the syntax for htp.em is:

htp.em(ctext, cattributes);

A call that uses HTML 3.0 attributes might look like the following:

htp.em('This is an example','ID="SGML_ID" LANG="en"');

which would generate the following:

<EM ID="SGML_ID" LANG="en">This is an example</EM>

5.5 mod_plsql and Applets

When you reference an applet using the APPLET tag in an HTML file, the server looks for the applet class file in the directory containing the HTML file. If the applet class file is in another directory, you use the CODEBASE attribute of the APPLET tag to specify that directory.

When you generate an HTML page from mod_plsql and the page references an applet, you must specify the CODEBASE attribute because mod_plsql does not have a concept of a current directory and does not know where to look for the applet class file.

The following example uses htp.appletopen to generate an APPLET tag. It uses the cattributes parameter to specify the CODEBASE value.

htp.appletopen('myapplet.class', 100, 200, 'CODEBASE="/applets"')

generates

<APPLET CODE="myapplet.class" height=100 width=200 CODEBASE="/applets">

/applets is a virtual path that contains the myapplet.class file.

5.6 Cookies

Cookies can be used to maintain persistent state variables from the client browser:

http://home.netscape.com/newsref/std/cookie_spec.html 
http://www.virtual.net/Projects/Cookies/

The owa_cookie package enables you to send and retrieve cookies in HTTP headers. It contains the following subprograms that you can use to set and get cookie values:

owa_cookie.cookie data type contains cookie name-value pairs.
owa_cookie.get function gets the value of the specified cookie.
owa_cookie.get_all procedure gets all cookie name-value pairs.
owa_cookie.remove procedure removes the specified cookie.

5.7 LONG Data Type

If you use values of the LONG data type in procedures/functions such as htp.print, htp.prn, htp.prints, htp.ps, or owa_util.cellsprint, be aware that only the first 32K of the LONG data is used. This reason for this limitation is that the LONG data is bound to a varchar2 data type in the procedure/function.

5.8 Extensions to the htp and htf Packages

The htp and htf packages allow you to use customized extensions. Therefore, as the HTML standard changes, you can add new functionality similar to the hypertext procedure and function packages to reflect those changes.

Here is an example of customized packages using non-standard <BLINK> and imaginary <SHOUT>tags:


create package nsf as
    function blink(cbuf in varchar2) return varchar2;
    function shout(cbuf in varchar2) return varchar2;
end;

create package body nsf as
    function blink(cbuf in varchar2) return varchar2 is
        begin return ('<BLINK>' || cbuf || '</BLINK>');
    end;
    function shout(cbuf in varchar2) return varchar2 is
        begin return ('<SHOUT>' || cbuf || '</SHOUT>');
    end;
end;

create package nsp as
    procedure blink(cbufin varchar2);
    procedure shout(cbufin varchar2);
end;

create package body nsp as
    procedure blink(cbufin varchar2) is
        begin htp.print(nsf.blink(cbuf));
    end;
    procedure shout(cbufin varchar2) is
        begin htp.print(nsf.shout(cbuf));
    end;
end;

Now you can begin to use these procedures and functions in your own procedure.


create procedure nonstandard as
begin
    nsp.blink('Gee this hurts my eyes!');
    htp.print('And I might ' || nsf.shout('get mad!'));
end;

5.9 String Matching and Manipulation

The owa_pattern package contains procedures and functions that you can use to perform string matching and string manipulation with regular expression functionality. The package provides the following subprograms:

The owa_pattern.match function determines whether a regular expression exists in a string. It returns TRUE or FALSE.
The owa_pattern.amatch function is a more sophisticated variation of the
owa_pattern.match function. It lets you specify where in the string the match has to occur. This function returns the end of the location in the string where the regular expression was found. If the regular expression is not found, it returns 0.
The owa_pattern.change function and procedure lets you replace the portion of the string that matched the regular expression with a new string. If you call it as a function, it returns the number of times the regular expression was found and replaced.

These subprograms are overloaded. That is, there are several versions of each, distinguished by the parameters they take. Specifically, there are six versions of MATCH, and four each of AMATCH and CHANGE. The subprograms use the following parameters:

line - This is the target to be examined for a match. Despite the name, it can be more than one line of text or can be a owa_text.multi_line data type.
pat - This is the pattern that the subprograms attempt to locate in line. The pattern can contain regular expressions. Note in the
owa_pattern.change function and procedure, this parameter is called from_str.
flags - This specifies whether the search is case-sensitive or if substitutions are to be done globally.

5.10 owa_pattern.match

The regular expression in this function can be either a VARCHAR2 or a
owa_pattern.pattern data type. You can create a owa_pattern.pattern data type from a string using the owa_pattern.getpat procedure.

You can create a multi_line data type from a long string using the
owa_text.stream2multi procedure. If a multi_line is used, the rlist parameter specifies a list of chunks where matches were found.

If the line is a string and not a multi_line, you can add an optional output parameter called backrefs. This parameter is a row_list that holds each string in the target that was matched by a sequence of tokens in the regular expression. Here is an example of the owa_pattern.match function:

boolean foundMatch;
foundMatch := owa_pattern.match('KAZOO', 'zoo.*', 'i');

This is how the function works: KAZOO is the target where it is searching for the zoo.* regular expression. The period indicates any character other than newline, and the asterisk matches 0 or more of the preceding characters. In this case, it matches any character other than the newline.

Therefore, this regular expression specifies that a matching target consists of zoo, followed by any set of characters neither ending in nor including a newline (which does not match the period). The i is a flag indicating that case is to be ignored in the search. In this case, the function returns TRUE, which indicates that a match had been found.

5.11 owa_pattern.change

owa_pattern.change can be a procedure or a function, depending on how it is invoked. As a function, it returns the number of changes made. If the flag `g' is not used, this number can only be 0 or 1. The flag `g' specifies that all matches are to be replaced by the regular expression. Otherwise, only the first match is replaced.

The replacement string can use the token ampersand (&), which indicates that the portion of the target that matched the regular expression is to be included in the expression that replaces it. For example:

owa_pattern.change('Cats in pajamas', 'C.+in', '& red ')

The regular expression matches the substring `Cats in'. It then replaces this string with `& red'. The ampersand character, &, indicates `Cats in', since that's what matched the regular expression. Thus, this procedure replaces the string `Cats in pajamas' with 'Cats in red'. If you called this as a function instead of a procedure, the value it would return would not be `Cats in red' but 1, indicating that a single substitution had been made.

5Using the PL/SQL Web Toolkit