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.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.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.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:

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.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