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.
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.
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 .
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:
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:
-- 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>');
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 .
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.
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);
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.
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);
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 .
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:
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.
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.
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;
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 .
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.
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.[
] 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
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
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:
The Internet community has developed a standard set of number/message result codes, the most common of which are:
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:
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;
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.
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:
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;
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:
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 );
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:
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');
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:
The underlying query must have the following layout:
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>
The underlying query must have the following layout:
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.