9
The owa_util Package

The owa_util package contains utility subprograms for performing operations such as getting the value of CGI environment variables, printing the data that is returned to the client, and printing the results of a query in an HTML table.

The owa_util contains three types of utility subprograms.

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 are treated as a data type by the Oracle database.

9.1 Summary

owa_util.bind_variables function - prepares a SQL query and binds variables to it.

owa_util.calendarprint procedure - prints a calendar.

owa_util.cellsprint procedure - prints the contents of a query in an HTML table.

owa_util.choose_date procedure - generates HTML form elements that allow the user to select a date.

owa_util.dateType data type - data type to hold date information.

owa_util.get_cgi_env function - returns the value of the specified CGI environment variable.

owa_util.get_owa_service_path function - returns the full virtual path for the PL/SQL Gateway.

owa_util.get_procedure function - returns the name of the procedure that is invoked by the PL/SQL Gateway.

owa_util.http_header_close procedure - closes the HTTP header.

owa_util.ident_arr data type - a data type

owa_util.ip_address data type - used by the owa_sec.get_client_ip function.

owa_util.listprint procedure - generates a HTML form element that contains data from a query.

owa_util.mime_header procedure - generates the Content-type line in the HTTP header.

owa_util.print_cgi_env procedure - generates a list of all CGI environment variables and their values.

owa_util.redirect_url procedure - generates the Location line in the HTTP header.

owa_util.showpage procedure - prints a page generated by the htp and htf packages in SQL*Plus.

owa_util.showsource procedure - prints the source for the specified subprogram.

owa_util.signature procedure - prints a line that says that the page is generated by the PL/SQL Agent.

owa_util.status_line procedure - generates the Status line in the HTTP header.

owa_util.tablePrint function - prints the data from a table in the database as an HTML table.

owa_util.todate function - converts dateType data to the standard PL/SQL date type.

owa_util.who_called_me procedure - returns information on the caller of the procedure.

9.2 owa_util.bind_variables function

This function prepares a SQL query by binding variables to it, and stores the output in an opened cursor. Use this function as a parameter to a procedure sending a dynamically generated query. Specify up to 25 bind variables.

Table 9-1 owa_util.bind_variables function

Properties	Definitions
Syntax:	owa_util.bind_variables( theQuery in varchar2 DEFAULT NULL bv1Name in varchar2 DEFAULT NULL bv1Value in varchar2 DEFAULT NULL bv2Name in varchar2 DEFAULT NULL bv2Value in varchar2 DEFAULT NULL bv3Name in varchar2 DEFAULT NULL bv3Value in varchar2 DEFAULT NULL ... bv25Name in varchar2 DEFAULT NULL bv25Value in varchar2 DEFAULT NULL) return integer;
Parameters:	theQuery - the SQL query statement. This must be a SELECT statement. bv1Name - the name of the variable. bv2Value - the value of the variable.
Returns:	An integer identifying the opened cursor.

Properties

Definitions

Syntax:

owa_util.bind_variables(
   theQuery       in       varchar2   DEFAULT NULL
   bv1Name        in       varchar2   DEFAULT NULL
   bv1Value       in       varchar2   DEFAULT NULL
   bv2Name        in       varchar2   DEFAULT NULL
   bv2Value       in       varchar2   DEFAULT NULL
   bv3Name        in       varchar2   DEFAULT NULL
   bv3Value       in       varchar2   DEFAULT NULL
        ...
   bv25Name       in       varchar2   DEFAULT NULL
   bv25Value      in       varchar2   DEFAULT NULL) return integer;

Parameters:

theQuery - the SQL query statement. This must be a SELECT statement.

bv1Name - the name of the variable.

bv2Value - the value of the variable.

Returns:

An integer identifying the opened cursor.

9.3 owa_util.calendarprint procedure

This procedure creates a calendar in HTML. Each date in the calendar can contain any number of hypertext links. Design your query as follows:

The first column is a DATE. This correlates the information produced by the query with the calendar output generated by the procedure.
The query output must be sorted on this column using ORDER BY.
The second column contains the text, if any, that you want printed for that date.
The third column contains the destination for generated links. Each item in the second column becomes a hypertext link to the destination given in this column. If this column is omitted, the items in the second column are simple text, not links.

This procedure has 2 versions. Version 1 uses a hard-coded query stored in a varchar2 string. Version 2 uses a dynamic query prepared with the owa_util.bind_variables function.

Table 9-2 owa_util.calendarprint procedure

Properties	Definitions
Syntax:	owa_util.calendarprint( p_query in varchar2 p_mf_only in varchar2 DEFAULT 'N'); owa_util.calendarprint( p_cursor in integer p_mf_only in varchar2 DEFAULT 'N');
Parameters:	p_query - a PL/SQL query. See the description above on what the query returns. p_cursor - a PL/SQL cursor containing the same format as p_query. p_mf_only - if "N" (the default), the generated calendar includes Sunday through Saturday. Otherwise, it includes Monday through Friday only.
Generates:	A calendar in the form of an HTML table with a visible border.

Properties

Definitions

Syntax:

owa_util.calendarprint(
   p_query        in       varchar2
   p_mf_only      in       varchar2   DEFAULT 'N');

owa_util.calendarprint(
   p_cursor       in       integer
   p_mf_only      in       varchar2   DEFAULT 'N');

Parameters:

p_query - a PL/SQL query. See the description above on what the query returns.

p_cursor - a PL/SQL cursor containing the same format as p_query.

p_mf_only - if "N" (the default), the generated calendar includes Sunday through Saturday. Otherwise, it includes Monday through Friday only.

Generates:

A calendar in the form of an HTML table with a visible border.

9.4 owa_util.cellsprint procedure

This procedure generates an HTML table from the output of a SQL query. SQL atomic data items are mapped to HTML cells and SQL rows to HTML rows. You must write the code to begin and end the HTML table. There are nine versions of this procedure:

The first version passes the results of a query into an index table. Perform the query and cellsprint does the formatting. To have more control in generating an HTML table from the output of an SQL query, use the htf.format_cell function.
The second and third versions display rows (up to the specified maximum) returned by the query or cursor.
The fourth and fifth versions exclude aspecified number of rows from the HTML table. Use the fourth and fifth versions to scroll through result sets by saving the last row seen in a hidden form element.

The sixth through ninth versions are the same as the first four versions, except that they return a row count output parameter.

Table 9-3 owa_util.cellsprint procedure

Properties	Definitions
Syntax:	owa_util.cellsprint( p_colCnt in integer p_resultTbl in vc_arr p_format_numbers in varchar2 DEFAULT NULL);
	owa_util.cellsprint( p_theQuery in varchar2 p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL);
	owa_util.cellsprint( p_theCursor in integer p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL);
	owa_util.cellsprint( p_theQuery in varchar2 p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL p_skip_rec in numbe DEFAULT 0 p_more_data out boolean);
	owa_util.cellsprint( p_theCursor in integer p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL p_skip_rec in number DEFAULT 0 p_more_data out boolean);
	owa_util.cellsprint( p_theQuery in varchar2 p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL p_reccnt out number);
	owa_util.cellsprint( p_theCursor in integer p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL p_reccnt out number);
	owa_util.cellsprint( p_theQuery in varchar2 p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL p_skip_rec in number DEFAULT 0 p_more_data out boolean p_reccnt out number);
	owa_util.cellsprint( p_theCursor in integer p_max_rows in number DEFAULT 100 p_format_numbers in varchar2 DEFAULT NULL p_skip_rec in number DEFAULT 0 p_more_data out boolean p_reccnt out number);
Parameters:	p_colCnt - the number of columns in the table. p_theQuery - a SQL SELECT statement. p_theCursor - a cursor ID. This can be the return value from the owa_util.bind_variables function. p_max_rows - the maximum number of rows to print. p_format_numbers - if the value of this parameter is not NULL, number fields are right-justified and rounded to two decimal places. p_skip_rec - the number of rows to exclude from the HTML table. p_more_data - TRUE if there are more rows in the query or cursor, FALSE otherwise. p_reccnt - the number of rows that have been returned by the query. This value does not include skipped rows (if any). p_resultTbl - the index table which will contain the result of the query. Each entry in the query will correspond to one column value.
Generates:	<tr><td>QueryResultItem</td><td>QueryResultItem</td></tr> <tr><td>QueryResultItem</td><td>QueryResultItem</td></tr>

9.5 owa_util.choose_date procedure

This procedure generates three HTML form elements that allow the user to select the day, the month, and the year. The parameter in the procedure that receives the data from these elements must be a owa_util.dateType data type. Use the owa_util.todate function to convert the owa_util.dateType data type value to the standard Oracle7 DATE data type.

Table 9-4 owa_util.choose_date procedure

Properties	Definitions
Syntax:	owa_util.choose_date( p_name in varchar2, p_date in date DEFAULT SYSDATE);
Parameters:	p_name - the name of the form elements. p_date - the initial date that is selected when the HTML page is displayed.
Generates:	<SELECT NAME="p_name" SIZE="1"> <OPTION value="01">1 ... <OPTION value="31">31 </SELECT> - <SELECT NAME="p_name" SIZE="1"> <OPTION value="01">JAN ... <OPTION value="12">DEC </SELECT> - <SELECT NAME="p_name" SIZE="1"> <OPTION value="1992">1992 ... <OPTION value="2002">2002 </SELECT>

Properties

Definitions

Syntax:

owa_util.choose_date(
   p_name         in       varchar2,
   p_date         in       date       DEFAULT SYSDATE);

Parameters:

p_name - the name of the form elements.

p_date - the initial date that is selected when the HTML page is displayed.

Generates:

<SELECT NAME="p_name" SIZE="1">
<OPTION value="01">1
     ...
<OPTION value="31">31
</SELECT>
-
<SELECT NAME="p_name" SIZE="1">
<OPTION value="01">JAN
     ...
<OPTION value="12">DEC
</SELECT>
-
<SELECT NAME="p_name" SIZE="1">
<OPTION value="1992">1992
     ...
<OPTION value="2002">2002
</SELECT>

9.6 owa_util.dateType data type

The owa_util.todate function converts an item of this type to the type DATE, which is understood and properly handled as data by the database. The procedure owa_util.choose_date procedure enables the user to select the desired date.

Table 9-5 owa_util.dateType data type

Properties	Definitions
Syntax:	type dateType is table of varchar2(10) index by binary_integer;
Returns:	Not applicable.

9.7 owa_util.get_cgi_env function

This function returns the value of the specified CGI environment variable. Although the WRB is not operated through CGI, many WRB cartridges, including the PL/SQL Gateway, can make use of CGI environment variables.

Table 9-6 owa_util.get_cgi_env function

Properties	Definitions
Syntax:	owa_util.get_cgi_env(param_name in varchar2) return varchar2;
Parameters:	param_name - the name of the CGI environment variable. It is case-insensitive. Get the values of all CGI environment variables except for QUERY_STRING because the PL/SQL Gateway parses the value of QUERY_STRING to determine the parameters to pass to the stored procedure.
Returns:	The value of the specified CGI environment variable. If the variable is not defined, the function returns NULL.

9.8 owa_util.get_owa_service_path function

This function returns the full virtual path of the PL/SQL Gateway that is handling the request.

Table 9-7 owa_util.get_owa_service_path function

Properties	Definitions
Syntax:	owa_util.get_owa_service_path return varchar2;
Parameters:	None.
Returns:	A virtual path of the PL/SQL Gateway that is handling the request.

9.9 owa_util.get_procedure function

This function returns the name of the procedure that is being invoked by the PL/SQL Gateway.

Table 9-8 owa_util.get_procedure function

Properties	Definitions
Syntax:	owa_util.get_procedure return varchar2;
Parameters:	None.
Returns:	The name of a procedure, including the package name if the procedure is defined in a package.

9.10 owa_util.http_header_close procedure

This procedure generates a newline character to close the HTTP header. Use this procedure if you have not closed the header by using the bclose_header parameter in calls such as owa_util.mime_header procedure, owa_util.redirect_url procedure, or owa_util.status_line procedure. The HTTP header must be closed before any htp.print or htp.prn calls.

Table 9-9 owa_util.http_header_close procedure

Properties	Definitions
Syntax:	owa_util.http_header_close;
Parameters:	None.
Generates:	A newline character, which closes the HTTP header.

9.11 owa_util.ident_arr data type

This data type is used for an array.

Table 9-10 owa_util.ident_arr data type

Properties	Definitions
Syntax:	type ident_arr is table of varchar2(30) index by binary_integer;
Returns:	Not applicable.

9.12 owa_util.ip_address data type

This data type is used by the owa_sec.get_client_ip function.

Table 9-11 owa_util.ip_address data type

Properties	Definitions
Syntax:	type ip_address is table of integer index by binary_integer;
Returns:	Not applicable.

9.13 owa_util.listprint procedure

This procedure generates an HTML selection list form element from the output of a SQL query. The columns in the output of the query are handled in the following manner:

The first column specifies the values that are sent back. These values are for the VALUE attribute of the OPTION tag.
The second column specifies the values that the user sees.
The third column specifies whether or not the row is marked as SELECTED in the OPTION tag. If the value is not NULL, the row is selected.

There are two versions of this procedure. The first version contains a hard-coded SQL query, and the second version uses a dynamic query prepared with the owa_util.bind_variables function.

Table 9-12 owa_util.listprint procedure

Properties	Definitions
Syntax:	owa_util.listprint( p_theQuery in varchar2 p_cname in varchar2 p_nsize in number p_multiple in boolean DEFAULT FALSE); owa_util.listprint( p_theCursor in integer p_cname in varchar2 p_nsize in number p_multiple in boolean DEFAULT FALSE);
Parameters:	p_theQuery - the SQL query. p_theCursor - the cursor ID. This can be the return value from the owa_util.bind_variables function. p_cname - the name of the HTML form element. p_nsize - the size of the form element (this controls how many items the user can see without scrolling). p_multiple - whether multiple selection is permitted.
Generates:	<SELECT NAME="p_cname" SIZE="p_nsize"> <OPTION SELECTED value='value_from_the_first_column'>value_from_ the_second_column <OPTION SELECTED value='value_from_the_first_column'>value_from_ the_second_column ... </SELECT>

Properties

Definitions

Syntax:

owa_util.listprint(
   p_theQuery     in       varchar2
   p_cname        in       varchar2
   p_nsize        in       number
   p_multiple     in       boolean   DEFAULT FALSE);

owa_util.listprint(
   p_theCursor    in       integer
   p_cname        in       varchar2
   p_nsize        in       number
   p_multiple     in       boolean   DEFAULT FALSE);

Parameters:

p_theQuery - the SQL query.

p_theCursor - the cursor ID. This can be the return value from the owa_util.bind_variables function.

p_cname - the name of the HTML form element.

p_nsize - the size of the form element (this controls how many items the user can see without scrolling).

p_multiple - whether multiple selection is permitted.

Generates:

<SELECT NAME="p_cname" SIZE="p_nsize">
<OPTION SELECTED value='value_from_the_first_column'>value_from_
the_second_column
<OPTION SELECTED value='value_from_the_first_column'>value_from_
the_second_column
     ...
</SELECT>

9.14 owa_util.mime_header procedure

This procedure changes the default MIME header that the script returns. This procedure must come before any htp.print or htp.prn calls to direct the script not to use the default MIME header.

Table 9-13 owa_util.mime_header procedure

Properties	Definitions
Syntax:	owa_util.mime_header( ccontent_type in varchar2 DEFAULT 'text/html', bclose_header in boolean DEFAULT TRUE, ccharset in varchar2 DEFAULT NULL);
Parameters:	ccontent_type - the MIME type to generate. bclose_header - whether or not to close the HTTP header. If TRUE, two newlines are sent, which closes the HTTP header. Otherwise, one newline is sent, and the HTTP header remains open. ccharset - the character set to use.
Generates:	Content-type: <ccontent_type>; charset=<ccharset>
Example:	owa_util.mime_header('text/plain', false, 'ISO-8859-4') generates: Content-type: text/plain; charset=ISO-8859-4\n

9.15 owa_util.print_cgi_env procedure

This procedure generates all the CGI environment variables and their values made available by the PL/SQL Gateway to the stored procedure.

Table 9-14 owa_util.print_cgi_env procedure

Properties	Definitions
Syntax:	owa_util.print_cgi_env;
Parameters:	None.
Generates:	A list in the following format: cgi_env_var_name = value\n

Properties

Definitions

Syntax:

owa_util.print_cgi_env;

Parameters:

None.

Generates:

A list in the following format:

cgi_env_var_name = value\n

9.16 owa_util.redirect_url procedure

This procedure specifies that the application server is to visit the specified URL. The URL may specify either a web page to return or a program to execute. This procedure must come before any htp or htf procedure or function call.

Table 9-15 owa_util.redirect_url procedure

Properties	Definitions
Syntax:	owa_util.redirect_url( curl in varchar2 bclose_header in boolean DEFAULT TRUE);
Parameters:	curl - the URL to visit. bclose_header - whether or not to close the HTTP header. If TRUE, two newlines are sent, which closes the HTTP header. Otherwise, one newline is sent, and the HTTP header is still open.
Generates:	Location: <curl>\n\n

Properties

Definitions

Syntax:

owa_util.redirect_url(
   curl           in       varchar2
   bclose_header  in       boolean    DEFAULT TRUE);

Parameters:

curl - the URL to visit.

bclose_header - whether or not to close the HTTP header. If TRUE, two newlines are sent, which closes the HTTP header. Otherwise, one newline is sent, and the HTTP header is still open.

Generates:

Location: <curl>\n\n

9.17 owa_util.showpage procedure

This procedure prints out the HTML output of a procedure in SQL*Plus, SQL*DBA, or Oracle Server Manager. The procedure must use the htp or htf packages to generate the HTML page, and this procedure must be issued after the procedure has been called and before any other HTP or HTF subprograms are directly or indirectly called. This method is useful for generating pages filled with static data. This procedure uses dbms_output and is limited to 255 characters per line and an overall buffer size of 1,000,000 bytes.

Table 9-16 owa_util.showpage procedure

Properties	Definitions
Syntax:	owa_util.showpage;
Parameters:	None.
Generates:	The output of htp procedure is displayed in SQLPlus, SQLDBA, or Oracle Server Manager. For example: SQL> set serveroutput on SQL> spool gretzky.html SQL> execute hockey.pass("Gretzky") SQL> execute owa_util.showpage SQL> exit This would generate an HTML page that could be accessed from Web browsers.

Properties

Definitions

Syntax:

owa_util.showpage;

Parameters:

None.

Generates:

The output of htp procedure is displayed in SQL*Plus, SQL*DBA, or Oracle Server Manager. For example:

SQL> set serveroutput on
SQL> spool gretzky.html
SQL> execute hockey.pass("Gretzky")
SQL> execute owa_util.showpage
SQL> exit

This would generate an HTML page that could be accessed from Web browsers.

9.18 owa_util.showsource procedure

This procedure prints the source of the specified procedure, function, or package. If a procedure or function which belongs to a package is specified, then the entire package is displayed.

Table 9-17 owa_util.showsource procedure

Properties	Definitions
Syntax:	owa_util.showsource (cname in varchar2);
Parameters:	cname - name of the procedure or function.
Generates:	The source code of the specified function, procedure, or package.

9.19 owa_util.signature procedure

This procedure generates an HTML line followed by a signature line on the HTML document. If a parameter is specified, the procedure also generates a hypertext link to view the PL/SQL source for that procedure. The link calls the owa_util.showsource procedure.

Table 9-18 owa_util.signature procedure

Properties	Definitions
Syntax:	owa_util.signature; owa_util.signature (cname in varchar2);
Parameters:	cname - the function or procedure whose source you want to show.
Generates:	Without a parameter, the procedure generates a line that looks like the following: This page was produced by the PL/SQL Agent on August 9, 2001 09:30. With a parameter, the procedure generates a signature line in the HTML document that looks like the following: This page was produced by the PL/SQL Agent on 8/09/01 09:30 View PL/SQL Source

Properties

Definitions

Syntax:

owa_util.signature;
owa_util.signature (cname in varchar2);

Parameters:

cname - the function or procedure whose source you want to show.

Generates:

Without a parameter, the procedure generates a line that looks like the following:

This page was produced by the PL/SQL Agent on August 9, 2001 09:30.

With a parameter, the procedure generates a signature line in the HTML document that looks like the following:

This page was produced by the PL/SQL Agent on 8/09/01 09:30
View PL/SQL Source

9.20 owa_util.status_line procedure

This procedure sends a standard HTTP status code to the client. This procedure must come before any htp.print or htp.prn calls so that the status code is returned as part of the header, rather than as "content data".

Table 9-19 owa_util.status_line procedure

Properties	Definitions
Syntax:	owa_util.status_line( nstatus in integer, creason in varchar2 DEFAULT NULL bclose_header in boolean DEFAULT TRUE);
Parameters:	nstatus - the status code. creason - the string for the status code. bclose_header - whether or not to close the HTTP header. If TRUE, two newlines are sent, which closes the HTTP header. Otherwise, one newline is sent, and the HTTP header is still open.
Generates:	Status: <nstatus> <creason>\n\n

Properties

Definitions

Syntax:

owa_util.status_line(
   nstatus        in       integer,
   creason        in       varchar2   DEFAULT NULL
   bclose_header  in       boolean    DEFAULT TRUE);

Parameters:

nstatus - the status code.

creason - the string for the status code.

bclose_header - whether or not to close the HTTP header. If TRUE, two newlines are sent, which closes the HTTP header. Otherwise, one newline is sent, and the HTTP header is still open.

Generates:

Status: <nstatus> <creason>\n\n

9.21 owa_util.tablePrint function

This function generates either preformatted tables or HTML tables (depending on the capabilities of the user's browser) from database tables. RAW columns are supported, but LONG RAW columns are not. References to LONG RAW columns will print the result `Not Printable'. In this function, cattributes is the second, rather than the last, parameter.

Table 9-20 owa_util.tablePrint function

Properties	Definitions
Syntax:	owa_util.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 NULL) return boolean;
Parameters:	ctable - the database table. cattributes - other attributes to be included as-is in the tag. ntable_type - how to generate the table. Specify "`HTML_TABLE`" to generate the table using <TABLE> tags or "`PRE_TABLE`" to generate the table using the <PRE> tags. ccolumns - a comma-delimited list of columns from ctable to include in the generated table. cclauses - WHERE or ORDER BY clauses, which specify which rows to retrieve from the database table, and how to order them. ccol_aliases - a comma-delimited list of headings for the generated table. nrow_min - the first row, of those retrieved, to display. nrow_max - the last row, of those retrieved, to display.
Generates:	A preformatted or HTML table. Returns TRUE if there are more rows beyond the nrow_max requested, FALSE otherwise.
Examples:	For browsers that do not support HTML tables, create the following procedure: create or replace procedure showemps is ignore_more boolean; begin ignore_more := owa_util.tablePrint('emp', 'BORDER', OWA_UTIL.PRE_ TABLE); end; Requesting a URL such as: http://myhost:8080/ows-bin/hr/plsql/showemps returns to the following to the client: <PRE> ----------------------------------------------------------------- \| EMPNO \|ENAME \|JOB \|MGR \|HIREDATE \| SAL \| COMM \| DEPTNO \| ----------------------------------------------------------------- \| 7369\| SMITH \| CLERK \| 7902 \| 17-DEC-80 \| 800 \| \| 20 \| \| 7499\| ALLEN \| SALESMAN\| 7698 \| 20-FEB-81 \| 1600 \| 300 \| 30 \| \| 7521\| WARD \| SALESMAN\| 7698 \| 22-FEB-81 \| 1250 \| 500 \| 30 \| \| 7566\| JONES \| MANAGER \| 7839 \| 02-APR-81 \| 2975 \| \| 20 \| \| 7654\| MARTIN \| SALESMAN\| 7698 \| 28-SEP-81 \| 1250 \| 1400\| 30 \| \| 7698\| BLAKE \| MANAGER \| 7839 \| 01-MAY-81 \| 2850 \| \| 30 \| \| 7782\| CLARK \| MANAGER \| 7839 \| 09-JUN-81 \| 2450 \| \| 10 \| \| 7788\| SCOTT \| ANALYST \| 7566 \| 09-DEC-82 \| 3000 \| \| 20 \| \| 7839\| KING \| PRESIDENT \| \| 17-NOV-81 \| 5000 \| \| 10 \| \| 7844\| TURNER \| SALESMAN\| 7698 \| 08-SEP-81 \| 1500 \| 0 \| 30 \| \| 7876\| ADAMS \| CLERK \| 7788 \| 12-JAN-83 \| 1100 \| \| 20 \| \| 7900\| JAMES \| CLERK \| 7698 \| 03-DEC-81 \| 950 \| \| 30 \| \| 7902\| FORD \| ANALYST \| 7566 \| 03-DEC-81 \| 3000 \| \| 20 \| \| 7934\| MILLER \| CLERK \| 7782 \| 23-JAN-82 \| 1300 \| \| 10 \| ----------------------------------------------------------------- </PRE> To view the employees in department 10, and only their employee ids, names, and salaries, create the following procedure: create or replace procedure showemps_10 is ignore_more boolean; begin ignore_more := owa_util.tablePrint ('EMP', 'BORDER', OWA_UTIL.PRE_TABLE, 'empno, ename, sal', 'where deptno=10 order by empno', 'Employee Number, Name, Salary'); end;
	A request for a URL like http://myhost:8080/ows-bin/hr/plsql/showemps_10 would return the following to the client: <PRE> ------------------------------------- \| Employee Number \|Name\| Salary \| ------------------------------------- \| 7782\| CLARK\| 2450 \| \| 7839\| KING \| 5000 \| \| 7934\| MILLER \| 1300 \| ------------------------------------- </PRE> For browsers that support HTML tables, to view the department table in an HTML table, create the following procedure: create or replace procedure showdept is ignore_more boolean; begin ignore_more := owa_util.tablePrint('dept', 'BORDER'); end;
	A request for a URL like http://myhost:8080/ows-bin/hr/plsql/showdept would return the following to the client: <TABLE BORDER> <TR> <TH>DEPTNO</TH> <TH>DNAME</TH> <TH>LOC</TH> </TR> <TR> <TD ALIGN="LEFT">10</TD> <TD ALIGN="LEFT">ACCOUNTING</TD> <TD ALIGN="LEFT">NEW YORK</TD> </TR> <TR> <TD ALIGN="LEFT">20</TD> <TD ALIGN="LEFT">RESEARCH</TD> <TD ALIGN="LEFT">DALLAS</TD> </TR> <TR> <TD ALIGN="LEFT">30</TD> <TD ALIGN="LEFT">SALES</TD> <TD ALIGN="LEFT">CHICAGO</TD> </TR> <TR> <TD ALIGN="LEFT">40</TD> <TD ALIGN="LEFT">OPERATIONS</TD> <TD ALIGN="LEFT">BOSTON</TD> </TR> </TABLE> A Web browser would formatn this to look like the following table:

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO

9.22 owa_util.todate function

This function converts the owa_util.dateType data type to the standard Oracle database DATE type.

Table 9-21 owa_util.todate function

Properties	Definitions
Syntax:	owa_util.todate(p_dateArray in dateType) return date;
Parameters:	p_dateArray - the value to convert.
Generates	A standard DATE.

9.23 owa_util.who_called_me procedure

This procedure returns information (in the form of output parameters) about the PL/SQL code unit that invoked it.

Table 9-22 owa_util.who_called_me procedure

Properties	Definitions
Syntax:	owa_util.who_called_me( owner out varchar2 name out varchar2 lineno out number caller_t out varchar2);
Parameters:	owner - the owner of the program unit. name - the name of the program unit. This is the name of the package, if the calling program unit is wrapped in a package, and the name of the procedure or function if the calling program unit is a stand-alone procedure or function. If the calling program unit is part of an anonymous block, this is NULL. lineno - the line number within the program unit where the call was made. caller_t - the type of program unit that made the call. The possibilities are: package body, anonymous block. procedure, and function. Procedure and function are only for stand-alone procedures and functions.
Generates	Not applicable.

Properties

Definitions

Syntax:

owa_util.who_called_me(
   owner          out      varchar2
   name           out      varchar2
   lineno         out      number
   caller_t       out      varchar2);

Parameters:

owner - the owner of the program unit.

name - the name of the program unit. This is the name of the package, if the calling program unit is wrapped in a package, and the name of the procedure or function if the calling program unit is a stand-alone procedure or function. If the calling program unit is part of an anonymous block, this is NULL.

lineno - the line number within the program unit where the call was made.

caller_t - the type of program unit that made the call. The possibilities are: package body, anonymous block. procedure, and function. Procedure and function are only for stand-alone procedures and functions.

Generates

Not applicable.

9The owa_util Package

9.1 Summary

9.2 owa_util.bind_variables function

Table 9-1 owa_util.bind_variables function

9.3 owa_util.calendarprint procedure

Table 9-2 owa_util.calendarprint procedure

9.4 owa_util.cellsprint procedure

Table 9-3 owa_util.cellsprint procedure

9.5 owa_util.choose_date procedure

Table 9-4 owa_util.choose_date procedure

9.6 owa_util.dateType data type

Table 9-5 owa_util.dateType data type

9.7 owa_util.get_cgi_env function

Table 9-6 owa_util.get_cgi_env function

9.8 owa_util.get_owa_service_path function

Table 9-7 owa_util.get_owa_service_path function

9.9 owa_util.get_procedure function

Table 9-8 owa_util.get_procedure function

9.10 owa_util.http_header_close procedure

Table 9-9 owa_util.http_header_close procedure

9.11 owa_util.ident_arr data type

Table 9-10 owa_util.ident_arr data type

9.12 owa_util.ip_address data type

Table 9-11 owa_util.ip_address data type

9.13 owa_util.listprint procedure

Table 9-12 owa_util.listprint procedure

9.14 owa_util.mime_header procedure

Table 9-13 owa_util.mime_header procedure

9.15 owa_util.print_cgi_env procedure

Table 9-14 owa_util.print_cgi_env procedure

9.16 owa_util.redirect_url procedure

Table 9-15 owa_util.redirect_url procedure

9.17 owa_util.showpage procedure

Table 9-16 owa_util.showpage procedure

9.18 owa_util.showsource procedure

Table 9-17 owa_util.showsource procedure

9.19 owa_util.signature procedure

Table 9-18 owa_util.signature procedure

9.20 owa_util.status_line procedure

Table 9-19 owa_util.status_line procedure

9.21 owa_util.tablePrint function

Table 9-20 owa_util.tablePrint function

9.22 owa_util.todate function

Table 9-21 owa_util.todate function

9.23 owa_util.who_called_me procedure

Table 9-22 owa_util.who_called_me procedure

9
The owa_util Package