The owa_util Package

 

owa_util.bind_variables Prepares a SQL query and binds variables to it
owa_util.calendarprint Prints a calendar
owa_util.cellsprint Prints the contents of a query in an HTML table
owa_util.choose_date Generates HTML form elements that allow the user to select a date
owa_util.dateType Data type to hold date information
owa_util.get_cgi_env Returns the value of the specified CGI environment variable
owa_util.get_owa_service_path Returns the full virtual path for the cartridge
owa_util.get_procedure Returns the name of the procedure that is invoked by the PL/SQL Agent
owa_util.http_header_close Closes the HTTP header
owa_util.ident_arr
owa_util.ip_address
owa_util.listprint Generates a HTML form element that contains data from a query.
owa_util.mime_header Generates the Content-type line in the HTTP header
owa_util.print_cgi_env Generates a list of all CGI environment variables and their values
owa_util.redirect_url Generates the Location line in the HTTP header
owa_util.showpage Prints a page generated by the htp and htf packages in SQL*Plus
owa_util.showsource Prints the source for the specified subprogram
owa_util.signature Prints a line that says that the page is generated by the PL/SQL Agent
owa_util.status_line Generates the Status line in the HTTP header
owa_util.tablePrint Prints the data from a table in the database as an HTML table
owa_util.todate Converts dateType data to the standard PL/SQL date type
owa_util.who_called_me Returns information on the caller of the procedure

 

owa_util.bind_variables function

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;

Purpose

This function prepares a SQL query by binding variables to it, and stores the output in an opened cursor. You normally use this function as a parameter to a procedure to which you desire to send a dynamically generated query. You can specify up to 25 bind variables.

Parameters

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

bv1Name - the name of the variable

bv2Value - the value of the variable

Return Value

An integer identifying the opened cursor.

 

owa_util.calendarprint procedure

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');

Purpose

This procedure creates a calendar in HTML. Each date in the calendar can contain any number of hypertext links. To achieve this effect, design your query as follows:

  • The first column should be a DATE. This is used to correlate the information produced by the query with the calendar output automatically generated by the procedure. Note: the query output must be sorted on this column using ORDER BY.
  • The second column contains the text, if any, you want printed for that date.
  • The third column contains the destination for automatically 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.

Parameters

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

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.

 

owa_util.cellsprint procedure

Syntax 

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    number 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);






Purpose 


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 four versions to this procedure. The first and second versions display rows (up to the specified maximum) returned by the query or cursor. The third and fourth versions allow you to exclude the specified number of rows from the HTML table. You can also use the third and fourth versions to scroll through result sets by saving the last row seen in a hidden form element.



Parameters 


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.



Generates 



<tr><td>QueryResultItem</td><td>QueryResultItem</td></tr>

<tr><td>QueryResultItem</td><td>QueryResultItem</td></tr>










 




owa_util.choose_date procedure 



Syntax 



owa_util.choose_date(

	p_name in varchar2,

	p_date in date DEFAULT SYSDATE);





Purpose 


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 should be a owa_util.dateType data type. You can use the owa_util.todate function to convert the owa_util.dateType data type value to the standard Oracle7 DATE data type.



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="" SIZE="1">





<OPTION value="01">1





<OPTION value="02">2





<OPTION value="03">3





<OPTION value="04">4





<OPTION value="05">5





<OPTION value="06">6





<OPTION value="07">7





<OPTION value="08">8





<OPTION value="09">9





<OPTION value="10">10





<OPTION value="11">11





<OPTION value="12">12





<OPTION value="13">13





<OPTION value="14">14





<OPTION value="15">15





<OPTION value="16">16





<OPTION value="17">17





<OPTION value="18">18





<OPTION value="19">19





<OPTION value="20">20





<OPTION value="21">21





<OPTION value="22">22





<OPTION value="23">23





<OPTION SELECTED value="24">24





<OPTION value="25">25





<OPTION value="26">26





<OPTION value="27">27





<OPTION value="28">28





<OPTION value="29">29





<OPTION value="30">30





<OPTION value="31">31





</SELECT>





-





<SELECT NAME="p_name" SIZE="1">





<OPTION value="01">JAN





<OPTION SELECTED value="02">FEB





<OPTION value="03">MAR





<OPTION value="04">APR





<OPTION value="05">MAY





<OPTION value="06">JUN





<OPTION value="07">JUL





<OPTION value="08">AUG





<OPTION value="09">SEP





<OPTION value="10">OCT





<OPTION value="11">NOV





<OPTION value="12">DEC





</SELECT>





-





<SELECT NAME="p_name" SIZE="1">





<OPTION value="1992">1992





<OPTION value="1993">1993





<OPTION value="1994">1994





<OPTION value="1995">1995





<OPTION value="1996">1996





<OPTION SELECTED value="1997">1997





<OPTION value="1998">1998





<OPTION value="1999">1999





<OPTION value="2000">2000





<OPTION value="2001">2001





<OPTION value="2002">2002





</SELECT>















 




owa_util.dateType data type 


This data type holds date information. It is defined as:



type dateType  is table of varchar2(10) index by binary_integer




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.







 




owa_util.get_cgi_env function 



Syntax 



owa_util.get_cgi_env(param_name in varchar2) return varchar2;





Purpose 


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 Cartridge, can make use of CGI enviroment variables.



Parameters 


param_name - the name of the CGI environment variable. It is case-insensitive.



Return Value 


The value of the specified CGI environment variable. If the variable is not defined, the function returns NULL.







 




owa_util.get_owa_service_path function 



Syntax 



owa_util.get_owa_service_path return varchar2;





Purpose 


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



Parameters 


none



Return Value 


A virtual path of the PL/SQL Cartridge that is handling the request.







 




owa_util.get_procedure function 



Syntax 



owa_util.get_procedure return varchar2;





Purpose 


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



Parameters 


none



Return Value 


The name of a procedure, including the package name if the procedure is defined in a package.







 




owa_util.http_header_close procedure 



Syntax 



owa_util.http_header_close;





Purpose 


This procedure generates a newline character to close the HTTP header.


Use this procedure if you have not explicitly 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.



Parameters 


none



Generates 


A newline character, which closes the HTTP header.







 




owa_util.ident_arr data type 


This data type is defined as:



type ident_arr is table of varchar2(30) index by binary_integer










 




owa_util.ip_address data type 


This data type is defined as:



type ip_address is table of integer index by binary_integer




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







 




owa_util.listprint procedure 



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);





Purpose 


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



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>










 




owa_util.mime_header procedure 



Syntax 



owa_util.mime_header(

	ccontent_type      in    varchar2 DEFAULT `text/html',

	bclose_header        in     boolean DEFAULT TRUE);





Purpose 


This procedure changes the default MIME header that the PL/SQL Agent returns.


This procedure must come before any htp.print or htp.prn calls in order to direct the PL/SQL Agent not to use the default.



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 is still open.



Generates 



Content-type: <ccontent_type>\n\n










 




owa_util.print_cgi_env procedure 



Syntax 



owa_util.print_cgi_env;





Purpose 


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



Parameters 


none



Generates 


A list in the following format:



cgi_env_var_name = value\n










 




owa_util.redirect_url procedure 



Syntax 



owa_util.redirect_url(

	curl               in    varchar2

	bclose_header      in    boolean DEFAULT TRUE);





Purpose 


This procedure specifies that the Web 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.print or htp.prn calls in order to tell the PL/SQL Agent to do the redirect.



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










 




owa_util.showpage procedure 



Syntax 



owa_util.showpage;





Purpose 


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.


Note that this procedure uses dbms_output and is limited to 255 characters per line and an overall buffer size of 1,000,000 bytes.



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.







 




owa_util.showsource procedure 



Syntax 



owa_util.showsource (cname in varchar2);





Purpose 


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.



Parameters 


cname - name of the procedure or function



Generates 


The source code of the specified function, procedure, or package.







 




owa_util.signature procedure 



Syntax 



owa_util.signature;





owa_util.signature (cname in varchar2);





Purpose 


This procedure generates an HTML line followed by a signature line on theHTML 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.



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, 1995 09:30




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



This page was produced by the PL/SQL Agent on 6/14/95 09:30

View PL/SQL Source










 




owa_util.status_line procedure 



Syntax 



owa_util.status_line(

	nstatus         in    integer,

	creason         in    varchar2 DEFAULT NULL

	bclose_header   in    boolean DEFAULT TRUE);





Purpose 


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



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










 




owa_util.tablePrint function 



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;





Purpose 


This function generates either preformatted or HTML tables (depending on the capabilities of the user's browser) from database tables. Note that 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.



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


Example 



To view just 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_10would 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> 




which a web browser can format to look like this:




DEPTNO


DNAME


LOC



10


ACCOUNTING


NEW YORK



20


RESEARCH


DALLAS



30


SALES


CHICAGO












 




owa_util.todate function 



Syntax 



owa_util.todate(p_dateArray in dateType) return date;





Purpose 


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



Parameters 


p_dateArray - the value to convert



Generates 


A standard DATE.







 




owa_util.who_called_me procedure 



Syntax 



owa_util.who_called_me(

	owner        out    varchar2

	name         out    varchar2

	lineno       out    number

	caller_t     out    varchar2);





Purpose 


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



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 standalone 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 used only for standalone procedures and functions.












 

Home