#

| Chapter 7 The PL/SQL Toolkit |

## 7.4 Improving Productivity

The final two packages, OWA_UTIL and OWA_SEC, let you perform a variety of administrative and security-related tasks that help improve overall productivity.

### 7.4.1 OWA_UTIL: Creating Complex HTML Structures

The OWA_UTIL package is a grab-bag of useful procedures and functions that simplify many complex tasks. It contains procedures to query the web server environment, simplify debugging, change the default HTTP header, and simplify HTML development.

Table 7.15 shows the various functions and procedures contained in the OWA_UTIL package, which are grouped in categories in the following sections according to their uses.

> NOTE: I've attempted to classify the OWA_UTIL procedures into broad, general categories (debugging, querying the environment, representing dates, etc.). These classifications reflect my own experience and are not intended to limit other possible uses.

Table 7.15: Various OWA_UTIL Procedures and Functions
Procedure/Function | Description
--- | ---
BIND_VARIABLES | Creates complex HTML structures
CALENDARPRINT | Creates complex HTML structures
CELLSPRINT | Creates complex HTML structures
CHOOSE_DATE | Represents dates
DATETYPE | Represents dates
GET_CGI_ENV | Queries the environment
GET_OWA_SERVICE_PATH | Queries the environment
GET_PROCEDURE | Performs debugging
HTTP_HEADER_CLOSE | HTML and HTTP utilities
IP_ADDRESS | Queries the environment
LISTPRINT | Creates complex HTML structures
MIME_HEADER | HTML and HTTP utilities
PRINT_CGI_ENV | Queries the environment
REDIRECT_URL | HTML and HTTP utilities
SHOWPAGE | Performs debugging
SHOWSOURCE | Performs debugging
SIGNATURE | HTML and HTTP utilities
STATUS_LINE | HTML and HTTP utilities
TABLEPRINT | Creates complex HTML structures
TODATE | Represents dates
WHO_CALLED_ME | Performs debugging

#### 7.4.1.1 Debugging

OWA_UTIL has a number of procedures useful for debugging, many of which are built on top of the DBMS_UTILITY built-in package. These debugging procedures are listed in Table 7.16.

Table 7.16: OWA_UTIL Procedures and Functions Used for Debugging
Procedure/ Function | Parameters | Description
--- | --- | ---
GET_PROCEDURE | None | Returns the name of the procedure being executed by the PL/SQL agent
SHOWPAGE | None | Prints the HTML generated by the HTP package
SHOWSOURCE | None | Prints the PL/SQL source code for a particular procedure, function, or package
WHO_CALLED_ME | owner IN VARCHAR2 name IN VARCHAR2 lineno IN NUMBER caller_t IN VARCHAR2 | Returns information about the procedure that called the currently executing procedure

#### 7.4.1.1.1 The SHOWPAGE procedure.

This procedure allows you to print the HTML generated by the HTP package. As mentioned earlier, output from this package is stored in a buffer. The SHOWPAGE procedure lets you view the contents of this buffer in SQL*Plus. To use SHOWPAGE:

1. Use SQL*Plus to log into the account that owns the desired procedure.

2. Use the SQL*Plus command SET SERVEROUT ON to turn on server output.

3. Execute the procedure, making sure to provide necessary parameters.

4. Execute OWA_UTIL.SHOWPAGE to print the results.

#### 7.4.1.1.2 The SHOWSOURCE procedure.

This procedure prints the PL/SQL source code for a given procedure, function, or package.

#### 7.4.1.1.3 The GET_PROCEDURE function.

This function returns the name of the procedure that is being executed by the PL/SQL agent.

#### 7.4.1.1.4 The WHO_CALLED_ME procedure.

This procedure returns information about the procedure that called the currently executing procedure. This information is particularly useful when you are trying to trace a program's execution. The parameters to the WHO_CALLED_ME procedure, which are all defined as OUT variables, are the following:

owner IN VARCHAR2

  The owner of the calling program unit.

name IN VARCHAR2

  The name of the calling unit (procedure name, function name, or ANONYMOUS).

lineno IN NUMBER

  The line number of the call within the calling unit.

caller_t IN VARCHAR2

  The type of call made. Here is an example:

>     -- Parameters to who_called_me must be declared as local variables
>     OWA_UTIL.who_called_me (cowner, cname, clineno, ccaller);
>     HTP.print (cowner || '<p>');
>     HTP.print (cname || '<p>');
>     HTP.print (clineno || '<p>');
>     HTP.print (ccaller || '<p>');

#### 7.4.1.2 Querying the environment

Like any web server, OAS maintains environment variables. Several procedures within OWA_UTIL allow you to query these settings, as shown in Table 7.17.

Table 7.17: OWA_UTIL Procedures, Functions, and Datatypes for Querying the Environment
Procedure/Function | Parameters | Description
--- | --- | ---
GET_CGI_ENV | param_name IN VARCHAR2 | Returns the value of an environment variable
GET_OWA_SERVICE_PATH | None | Returns the full path of the PL/SQL agent that executed the request
IP_ADDRESS | None | Datatype to hold the TCP/IP address of the client machine that executed a procedure or function
PRINT_CGI_ENV | None | Generates a list of the names and values for all environment variables

#### 7.4.1.2.1 The PRINT_CGI_ENV procedure.

This procedure generates a list of names and values for all the environment variables. The procedure is used like the HTML procedures. As a general rule, it is not a good idea to allow casual users to view these settings, which provide detailed information, such as path settings, that can be exploited by malicious deviants. Figure 7.4 illustrates the output of this procedure.

#### Figure 7.4: The output of OWA_UTIL.PRINT_CGI_ENV

#### 7.4.1.2.2 The GET_CGI_ENV function.

This function returns the value of an environment variable. It accepts a single VARCHAR2 parameter, param_name, and returns the value as a string. If the environment variable is not defined, the function returns NULL. For example:

>     -- Fetch the server name into a local variable
>     server := OWA_UTIL.get_cgi_env ('SERVER_NAME');
>     HTP.print ('The server is: ' || server);

#### 7.4.1.2.3 The IP_ADDRESS datatype.

The TCP/IP address of the client machine that executed a procedure or function is a particularly useful environment variable. For this reason, OWA_UTIL declares a special data structure just to hold this address. Inexplicably, however, this structure seems to be used only by the OWA_SEC package's GET_CLIENT_IP_ADDRESS function. Go figure!

>     TYPE ip_address IS TABLE OF INTEGER
>        INDEX BY BINARY_INTEGER;

The four elements of the ip_address array correspond to the four components of the address.

#### 7.4.1.2.4 The GET_OWA_SERVICE_PATH function.

This function returns the full path of the PL/SQL agent used to execute the request. This string is typically the name of the PL/SQL agent followed by "/plsql/" (depending on the agent's configuration).

>     -- SP is a local VARCHAR2 variable
>     sp := OWA_UTIL.get_owa_service_path;
>     HTP.print (sp);

#### 7.4.1.3 Representing dates

With dozens of possible formats, dates are troublesome in almost every development environment. OWA_UTIL can help simplify date entry by providing a standard input format for the day, month, and year. The procedures used to do this are shown in Table 7.18.

Table 7.18: OWA_UTIL Procedures for Representing Dates
Procedure/Function | Parameters | Description
--- | --- | ---
CHOOSE_DATE | p_name IN VARCHAR2 p_date IN DATE DEFAULT SYSDATE | Generates input elements for date, month, and year
DATETYPE | None | Datatype for day, month, and year from choose_date
TODATE | None | Converts a datetype into a normal date variable

#### 7.4.1.3.1 The CHOOSE_DATE procedure.

This procedure generates input elements for the day, month, and year that are used as part of a data entry form. Since each element has the same name, the date is passed as an array. Its parameters are as follows:

p_name IN VARCHAR2

  The name of the form element.

p_date IN DATE DEFAULT SYSDATE

  The value of the date.

The following procedure creates a nicely formatted form for entering a hire date:

>     HTP.print ('form action=proc_date');
>     HTP.print ('Date Hired:');
>     OWA_UTIL.choose_date ('date_hired','31-OCT-98');

Figure 7.5 shows the output from this procedure.

#### Figure 7.5: A form that uses OWA_UTIL.CHOOSE_DATE

#### 7.4.1.3.2 The DATETYPE datatype.

The day, month, and year created with the CHOOSE_DATE procedure are held as three elements in an array:

>     TYPE datetype IS TABLE OF VARCHAR2(10)
>        INDEX BY BINARY_INTEGER;

The specification also includes a DATETYPE variable called empty_date that is used as the default value for parameters that receive a DATETYPE value.

#### 7.4.1.3.3 The TODATE function.

This function is used in the procedure that processes a form and converts a DATETYPE into a normal date variable:

>     CREATE OR REPLACE PROCEDURE proc_date (
>        date_hired OWA_UTIL.datetype DEFAULT OWA_UTIL.empty_date
>        )
>     IS

>        dhire DATE;

>     BEGIN
>        dhire := OWA_UTIL.todate (date_hired);
>        HTP.print (TO_CHAR (dhire, 'Month DD, YYYY'));
>     END;

#### 7.4.1.4 HTML and HTTP utilities

OWA_UTIL contains a number of specialized HTTP and HTML procedures that don't fit cleanly into the HTP package. These are shown in Table 7.19.

Table 7.19: OWA_UTIL Procedures Used for HTML and HTTP
Procedure | Parameters | Description
--- | --- | ---
HTTP_HEADER_CLOSE | ccontent_type IN VARCHAR2 bclose_header IN BOOLEAN DEFAULT TRUE | Manually closes a web page header
MIME_HEADER | ccontent_type IN VARCHAR2 bclose_header IN BOOLEAN DEFAULT TRUE | Signals the PL/SQL agent to change the default header for a document
REDIRECT_URL | curl IN VARCHAR2 bclose_header IN BOOLEAN DEFAULT TRUE | Sends a user to a URL (passed as a parameter)
SIGNATURE | cname (optional; not recommended) | Generates a single document signature showing the date the page was last updated
STATUS_LINE | nstatus IN INTEGER creason IN VARCHAR2 DEFAULT NULL bclose_header IN BOOLEAN DEFAULT TRUE | Sends a numerical code to the browser indicating the status of a request

#### 7.4.1.4.1 The SIGNATURE procedure.

A signature is a standardized line that usually appears at the end of a document. For example, an email signature often lists the sender's company, position, and phone number. Similarly, an HTML signature appears at the end of a web page. The SIGNATURE procedure generates a simple signature that gives the date the page was last updated:

>     <b>This page was produced by the PL/SQL Agent on sysdate</b>

You can also provide the name for a procedure or function in an optional parameter called cname. This adds an additional hyperlink to the signature that, when clicked, displays the PL/SQL code for the procedure or function specified in the parameter. This is a dangerous practice you should probably avoid.

#### 7.4.1.4.2 The MIME_HEADER procedure.

Every resource is identified as a particular type of content. This classification, called the MIME (Multipurpose Internet Mail Extension) type, is based on a set of standards used for transmitting ASCII and binary files across the Internet.

This MIME type is set in a section called the HTTP header that is separate from the actual content.[4] The header section begins with a header that (like normal HTML) must be closed by another instruction. By default, the PL/SQL agent automatically sends `text/html` as the MIME type and closes the header. To perform certain tasks, such as creating a cookie or activating a content handler on the user's browser, we must interrupt this normal flow of events.

> [4] Although they have similar names, the HTTP header is not the same as the HTML header created by the `<head>` tag.

For example, suppose we want to place the results of a query in a spreadsheet, rather than in an HTML document. To accomplish this, we must tell the browser that the content is not a normal HTML document, then generate a data stream, such as a tab-delimited set of columns, that is funneled to the spreadsheet program. We need to change the default `text/html` type to something like `text/tab` (the MIME type for tab-delimited content) to signal the browser to start a new content handler.

This is done with the OWA_UTIL procedures that change the default HTTP header. These procedures are not normal HTML; instead, they are special instructions that cause the browser to act in a particular way, and each must be used before any of the normal HTP procedures.

The MIME_HEADER procedure signals the PL/SQL agent to change the default header that is normally sent with the document. It has two parameters:

ccontent_type IN VARCHAR2

  The new content type.

bclose_header IN BOOLEAN DEFAULT TRUE

  Flag indicating if the header should be immediately closed; a value of FALSE leaves it open so that more instructions (like these to set cookies) can be included as part of the header.

#### 7.4.1.4.3 The STATUS_LINE procedure.

This procedure sends a numerical code to the browser indicating the status of a request. There are three parameters to the procedure:

nstatus IN INTEGER

  The numeric status code.

creason IN VARCHAR2 DEFAULT NULL

  Code description.

bclose_header IN BOOLEAN DEFAULT TRUE

  Flag to close the HTTP header.

The Internet community has developed a standard set of number/message result codes, the most common of which are:

200: Success
401: Unauthorized
403: Forbidden
404: Not Found

#### 7.4.1.4.4 The REDIRECT_URL procedure.

It is often necessary to transparently send users from one web page to another. Most often, this is done when a page is moved to another location. Rather than having users reenter the new location, we simply redirect them to the new page.

Sometimes we want to direct users to a static page from within a PL/SQL program. For example, suppose you need to make some changes to a popular PL/SQL web application and you want to keep users out for a while. You can use redirection to send users who attempt to use the application to a new page that explains why the application is closed and when it will be available again (assuming, of course, that you haven't shut the database down entirely). This basic courtesy can save you lots of calls from irate users.

The REDIRECT_URL procedure sends a user to the URL passed as a parameter. This URL can refer to a static page or another PL/SQL program; you can even pass parameters using the query string. Like MIME_HEADER and STATUS_LINE, REDIRECT_URL places its output within the HTTP header and must appear before any other HTP calls. It has two parameters:

curl IN VARCHAR2

  The new URL.

bclose_header IN BOOLEAN DEFAULT TRUE

  Flag to close the HTTP header.

The following procedure illustrates how you could redirect a user to a static page if you wanted to shut down an application temporarily:

>     PROCEDURE popular_app_main
>     IS
>     BEGIN
>        IF popular_app_is_closed
>        THEN
>           -- Redirect to static page
>           OWA_UTIL.redirect_url ('http://server/alert/status.html');
>        ELSE
>           HTP.title ('The application you know and love...');
>           popular_app.show_main_page;
>        END IF;
>     END;

#### 7.4.1.4.5 The HTTP_HEADER_CLOSE procedure.

This procedure is used to manually close the header when the bclose_header flag to any of the previous procedures is FALSE. It does not have any parameters.

#### 7.4.1.5 Creating complex HTML structures

The OWA_UTIL procedures and functions listed in Table 7.20 help you create more complex HTML structures. They are described in the following sections.

Table 7.20: OWA_UTIL Procedures and Functions for Creating Complex HTML Structures
Procedure/Function | Parameters | Description
--- | --- | ---
BIND_VARIABLES | theQuery IN VARCHAR2 DEFAULT NULL bvnName IN VARCHAR2 DEFAULT NULL bvnValue IN VARCHAR2 DEFAULT NULL | Provides an interface to the built-in package DBMS_SQL
CALENDARPRINT | p_theQuery IN VARCHAR2 OR NUMBER p_cname IN VARCHAR2 p_nsize IN NUMBER p_multiple IN BOOLEAN DEFAULT FALSE | Creates an HTML-based monthly calendar
CELLSPRINT | theQuery IN VARCHAR2 OR NUMBER p_max_rows IN NUMBER p_format_numbers IN VARCHAR2 DEFAULT NULL p_skip_rec IN NUMBER DEFAULT 0 p_more_data OUT BOOLEAN | A stripped-down version of TABLEPRINT
LISTPRINT | p_theQuery IN VARCHAR2 OR NUMBER p_cname IN VARCHAR2 p_nsize IN NUMBER p_multiple IN BOOLEAN DEFAULT FALSE | Creates a list of values (LOV) on an HTML form
TABLEPRINT | ctable IN VARCHAR2 cattributes IN VARCHAR2 DEFAULT NULL ntable_type IN INTEGER DEFAULT HTML_TABLE ccolumns IN VARCHAR2 DEFAULT `*' cclauses IN VARCHAR2 DEFAULT NULL ccol_aliases IN VARCHAR2 DEFAULT NULL nrow_min IN NUMBER DEFAULT 0 nrow_max IN NUMBER DEFAULT 0 | Produces a formatted HTML table based on a SQL query

#### 7.4.1.5.1 The TABLEPRINT function.

This function produces a formatted HTML table based on a SQL query whose appearance is similar to that of a SELECT statement in SQL*Plus. The function's return value indicates if all the rows in the underlying table have been displayed. Its parameters are:

ctable IN VARCHAR2

  The database table that is being reported on.

cattributes IN VARCHAR2 DEFAULT NULL

  Free-format attributes to be included as part of the table tag (i.e., `<table cattributes>`).

ntable_type IN INTEGER DEFAULT HTML_TABLE

  The output type; can be either an HTML table or a text table; two numeric constants, HTML_TABLE (value = 1) and PRE_TABLE (value = 2) are defined to represent these types.

ccolumns IN VARCHAR2 DEFAULT '*'

  The columns to include in the output; the list is delimited with commas.

cclauses IN VARCHAR2 DEFAULT NULL

  A WHERE or ORDER BY clause used to select specific rows from the underlying table; the clause must be syntactically correct and include all necessary keywords (such as WHERE...).

ccol_aliases IN VARCHAR2 DEFAULT NULL

  The column aliases used for each column; this list is comma-delimited and should correspond to the columns specified in the ccolumns parameter.

nrow_min IN NUMBER DEFAULT 0

  The ordinal position of the first row in the result set to display; not the same as rownum.

nrow_max IN NUMBER DEFAULT 0

  The ordinal position of the last row in the result set to display; not the same as rownum.

The tprint procedure, shown in the following code, uses the TABLEPRINT procedure to page through the EMP table five rows at a time. The i_page_num parameter is used to calculate corresponding values for the nrow_min and nrow_max parameters. The output is shown in Figure 7.6.

>     CREATE OR REPLACE PROCEDURE tprint (
>        i_page_num IN VARCHAR2 DEFAULT '1'
>        )
>     IS

>        more_rows BOOLEAN;

>        cur_page NUMBER
>           := TO_NUMBER (i_page_num);
>        min_row NUMBER;
>        max_row NUMBER;

>        i_num_rows CONSTANT NUMBER := 5;

>     BEGIN
>        min_row :=  (cur_page - 1) * i_num_rows + 1;
>        max_row := min_row + i_num_rows - 1;
>        more_rows :=
>           OWA_UTIL.tableprint (
>              ctable       => 'scott.emp',
>              cattributes  => 'border=1',
>              ntable_type  => OWA_UTIL.html_table,
>              ccolumns     => 'job, ename, hiredate, sal',
>              cclauses     => 'order by job, ename',
>              ccol_aliases => 'Job, Employee Name, Date Hired, Salary',
>              nrow_min     => min_row,
>              nrow_max     => max_row
>           );
>        -- Put a "Prev" hyperlink if min_row > 1
>        IF cur_page > 1
>        THEN
>           HTP.anchor (
>              'tprint?i_page_num=' ||  (cur_page - 1),
>              'Previous'
>           );
>        END IF;
>        -- Put a "Next" hyperlink if there are more rows in the query
>        IF more_rows
>        THEN
>           HTP.anchor (
>              'tprint?i_page_num=' ||  (cur_page + 1),
>              'Next'
>           );
>        END IF;

>     END;

#### Figure 7.6: The output of the tprint procedure

#### 7.4.1.5.2 The BIND_VARIABLES procedure.

This procedure puts a friendly face on DBMS_SQL, one of the most flexible and powerful of all the built-in packages. DBMS_SQL allows you to dynamically construct and execute SQL statements as your program executes. The queries constructed by BIND_VARIABLES can even contain variables that are bound to values entered on the HTML form.

The BIND_VARIABLES procedure accepts a SQL statement and up to 25 name/value pairs of bind variables. It returns a cursor handle (not an actual cursor) that can be passed to other OWA_UTIL procedures to create complex HTML structures. This handle is also used by the various procedures in DBMS_SQL to fetch, parse, and close dynamic queries. For an excellent discussion of DBMS_SQL, see Oracle Built-in Packages by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).

The parameters to the BIND_VARIABLES procedure are:

theQuery IN VARCHAR2 DEFAULT NULL

  The select query to use in creating the dynamic cursor; it can contain up to 25 bind variables.

bvnName IN VARCHAR2 DEFAULT NULL

  The name of the nth bind variable (i.e., bv1Name, bv2Name, . . . bv25Name); there must be a bind variable parameter for each bind variable in the SELECT statement.

bvnValue IN VARCHAR2 DEFAULT NULL

  The value of the n th bind variable (i.e., bv1Value, bv2Value, . . . bv25Value); there must be a corresponding value for each bind variable name.

Here is a simple code snippet illustrating the use of the BIND_VARIABLES procedure. The SELECT statement is built and stored in a string:

>     stmt :=
>           'select emp.ename, emp.job, emp.sal, dpt.dname, dpt.loc';
>        stmt := stmt || ' from scott.emp emp, scott.dept dpt';
>        stmt := stmt || ' where emp.deptno = dpt.deptno and';
>        stmt := stmt || ' dpt.dname like :bvDept and';
>        stmt := stmt || ' emp.job like :bvJob and ';
>        stmt := stmt || ' emp.sal > :bvSal';
>        stmt := stmt || ' order by emp.ename';
>        --
>        cur_handle :=
>           OWA_UTIL.bind_variables (
>              TheQuery => stmt,
>              bv1Name  => 'bvDept',
>              bv1Value => 'RESEARCH',
>              bv2Name  => 'bvJob',
>              bv2Value => '%',
>              bv3Name  => 'bvSal',
>              bv3Value => 1000
>           );

#### 7.4.1.5.3 The CELLSPRINT procedure.

CELLSPRINT is a stripped-down version of TABLEPRINT. The main difference between the two procedures is that CELLSPRINT can accept a dynamic query generated with BIND_VARIABLES in addition to a simple VARCHAR2 query string. This is especially useful when the underlying query contains a number of bind variables. Its parameters are:

theQuery IN VARCHAR2 OR NUMBER

  The query on which to build the table; it can be a simple string or a cursor handle returned by BIND_VARIABLES.

p_max_rows IN NUMBER

  The maximum number of rows allowed in the HTML output; this parameter is not optional.

p_ format_numbers IN VARCHAR2 DEFAULT NULL

  If this value is non-null, numbers in the table are right-justified and formatted to two decimal places.

p_skip_rec IN NUMBER DEFAULT 0

  Optional offset; sets the first row of the result set that is displayed; similar to the nrow_min parameter of TABLEPRINT.

p_more_data OUT BOOLEAN

  Optional flag used in conjunction with p_skip_rec that indicates if there are more rows in the underlying table; similar to the return value of the TABLEPRINT function.

Here is a simple example based on a VARCHAR2 query string:

>     OWA_UTIL.cellsprint (
>        'select * from emp where job like ' || iename || '%',
>        10,
>        'Y'
>     );

We could use CELLSPRINT to quickly print the results of the SELECT statement defined in a call to the BIND_VARIABLES procedure:

>     OWA_UTIL.cellsprint (cur_handle, 10, 'Y');

#### 7.4.1.5.4 The LISTPRINT procedure.

This procedure is handy for creating lists of values (LOVs) on an HTML form. Like traditional LOVs, the elements in the list come from an underlying query. However, since HTTP is stateless, the entire contents of the query must be downloaded to the HTML form, which can present a problem for very large numbers of elements. It has the following parameters:

p_theQuery IN VARCHAR2 OR NUMBER

  The underlying query on which the LOV is based; can be either a VARCHAR2 string or a cursor handle to a dynamic query created with the BIND_VARIABLES procedure.

p_cname IN VARCHAR2

  The name of the HTML input element.

p_nsize IN NUMBER

  The size of the input list; setting this value to "1" creates a drop-down list; otherwise, it creates a scrollbox with the specified number of items visible.

p_multiple IN BOOLEAN DEFAULT FALSE

  Flag indicating that the select list can contain multiple selections; if TRUE, the input element must be treated as an array of elements when the form is processed.

The underlying query must have the following layout:

Column 1

  The value returned when the element is selected from the list (e.g., empno, deptno, etc.).

Column 2

  The value the user sees on the form (e.g., ename, deptname, etc.).

Column 3

  A non-NULL value in the third column marks the row as "selected" on the form.

The following procedure call creates an input element we can include within an HTML form:

>     OWA_UTIL.listprint (
>        'select empno, ename, null from scott.emp order by ename',
>        'iempno',
>        1
>     );

The procedure generates the following HTML:

>     <SELECT NAME="emp_no" SIZE="1">
>     <OPTION value="7876">ADAMS
>     <OPTION value="7499">ALLEN
>     <OPTION value="7698">BLAKE
>     ....
>     <OPTION value="7844">TURNER
>     <OPTION value="7521">WARD
>     </SELECT>

#### 7.4.1.5.5 The CALENDARPRINT procedure.

This procedure creates an HTML-based monthly calendar. The procedure has the following parameters:

p_query IN VARCHAR2 OR INTEGER

  The underlying query for the calendar; can be either a simple VARCHAR2 string or a handle to a dynamic cursor created with the BIND_VALUES procedure.

p_mf_only IN VARCHAR2 DEFAULT `N'

  Flag to exclude Sunday and Saturday from the calendar; an "N" (the default) includes them, a "Y" excludes them.

The underlying query must have the following layout:

Column 1

  A date; CALENDARPRINT generates a one-month calendar for each unique month/year combination in this column. The query should be ordered by this column.

Column 2

  The text printed on the calendar for the date.

Column 3

  If non-NULL, this column turns the text into a hyperlink. The column must contain a valid URL.

For example, suppose we want to print a calendar based on a to-do list stored in a database table with the following columns and data:

>     DUE_DATE    DESCRIPTION                   HYPERLINK
>     ---------   ----------------------------  -----------------------
>     20-OCT-98   Give cat pill                 http://www.sickcat.com
>     22-OCT-98   Research Dev2K                http://www.oracle.com
>     28-OCT-98   Check out new O'Reilly books  http://www.oreilly.com
>     30-OCT-98   Buy Costume
>     31-OCT-98   Trick-or-Treat!

We can use the following line to create the calendar:

>     str := 'select due_date, description, hyperlink ';
>     str := str || 'from to_do order by due_date';
>     OWA_UTIL.calendarprint (str);

Figure 7.7 shows the output of this call.

#### Figure 7.7: A calendar based on a to-do list

### 7.4.2 OWA_SEC: Managing Security

The toolkit includes a package, OWA_SEC, that is used to query and set various security options, such as realms, domains, etc. Most of these procedures are conceptually similar to the procedures of OWA_UTIL that query the environment variables. Table 7.21 summarizes the various security procedures and functions.

Table 7.21: Various OWA_SEC Procedures and Functions
Procedure/Function | Parameters | Description
--- | --- | ---
GET_CLIENT_HOSTNAME | None | Returns the web server's hostname
GET_CLIENT_IP | None | Returns the TCP/IP address of the client browser that executed the procedure
GET_USER_ID | None | Returns the username of the user executing the procedure
GET_PASSWORD | None | Returns the password used to log in
SET_AUTHORIZATION | scheme IN INTEGER | Forces the PL/SQL agent to call a custom authentication function called AUTHORIZE
SET_PROTECTION_REALM | realm IN VARCHAR2 | Forces the user to provide a valid login name and password for the specified security realm

7.3 Maintaining State |  | 8. Developing Applications

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.