Oracle Web Applications: PL/SQL Developer's Introduction

Oracle Web Applications: PL/SQL Developer's IntroductionSearch this book
Previous: 7.3 Maintaining StateChapter 7
The PL/SQL Toolkit
Next: 8. Developing Applications
 

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

Figure 7.4

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

Figure 7.5

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

Figure 7.6

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

Figure 7.7

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


Previous: 7.3 Maintaining StateOracle Web Applications: PL/SQL Developer's IntroductionNext: 8. Developing Applications
7.3 Maintaining StateBook Index8. Developing Applications

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference