| Syntax | owa_util.signature; |
| Purpose | Prints an HTML line followed by a signature line on the HTML document. |
| Parameters | none |
| Generates | Prints a signature line in the HTML document that might look like the following: "This page was produced by the Oracle Web Agent on August 9, 1995 09:30" |
| Syntax | owa_util.signature (cname); |
| Purpose | Allows the programmer to create a signature line on the bottom of the HTML document that has a hypertext link to view the PL/SQL source for that procedure. |
| Parameters | cname in varchar2 |
| Generates | Prints a signature line on the bottom of the HTML document that has a link to the actual PL/SQL source for that procedure. The link calls the procedure showsource. The line would look like the following: |
| "This page was produced by the Oracle Web Agent on 6/14/95 09:30" View PL/SQL Source | |
| Syntax | owa_util.showsource (cname); |
| Purpose | Prints the source of the specified PL/SQL procedure, function, or package. If a procedure or function which belongs to a package is specified, then the entire package is displayed. |
| Parameters | cname in varchar2 |
| Generates | Generates the source code of the specified PL/SQL procedure. |
| Syntax | owa_util.showpage; |
| Purpose | This procedure allows a user to view the HTML output of a PL/SQL procedure call from SQL*Plus, SQL*DBA, or Oracle Server Manager. The PL/SQL procedure must use HTP and/or HTF to generate the HTML page, and owa_util.showpage must be issued after the PL/SQL 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 thus is limited to 255 characters per line and an overall buffer size of 1,000,000 bytes. | |
| Parameters | none |
| Generates | One can use this procedure to generate static pages in SQL*Plus that can then be accessed as a standard HTML page. 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 which could be accessed from Web clients. | |
| Syntax | owa_util.get_cgi_env(param_name in varchar2); |
| Purpose | Allows programmer to retrieve the value of the specified CGI environment variable in the PL/SQL procedure. |
| Note that param_name is case-insensitive. | |
| Parameters | param_name in varchar2 |
| Returns | Returns value of specified CGI environment variable for PL/SQL procedure. If the value is not set, returns null |
| Syntax | owa_util.print_cgi_env; |
| Purpose | enables programmer to print all of the CGI environment variables made available by the Web Agent to the PL/SQL procedures. This utility is good for testing purposes. |
| Parameters | none |
| Generates | Prints CGI environment variables made available by the Web Agent to the PL/SQL procedures. |
| Syntax | owa_util.mime_header; |
| Purpose | Enables programmer to change the default MIME header that the Web Agent returns. This must come before any htp.print or htp.prn calls in order to signal the Web Agent not to use the default. |
| Parameters | ccontent_type in varchar2 |
| Generates | Content-type: <ccontent_type>\n\n |
| Syntax | owa_util.get_owa_service_path; |
| Purpose | Returns the name of the currently active path with its full virtual path, plus the currently active service. For example, a call to get_owa_service_path could return /ows-bin/myservice/owa/. |
| Parameters | none |
| Returns | The service path. The datatype is varchar2. |
| Syntax | owa_util.tablePrint; |
| Purpose | Enables programmers to print Oracle tables as either preformatted or HTML tables, depending upon Web browser capabilities. Note that RAW COLUMNS are supported, however LONG RAW are not. References to LONG RAW columns will print the result 'Not Printable'. In this case, cattributes is the second, rather than the last, parameter. |
| Parameters | 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 |
| Note that ntable_type can be either owa_util.html_table or owa_util.pre_table. | |
| Generates | Prints out either a preformatted or HTML table. |
| Returns | True or False as to whether there are more rows available beyond the nrow_max requested. |
| Example | |
create or replace procedure showemps is
ignore_more boolean;
begin
ignore_more := owa_util.tablePrint('emp', 'BORDER', OWA_UTIL.PRE_TABLE);
end;
and requesting a URL like this example: http://myhost:8080/ows-bin/hr/owa/showemps
returns 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 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/owa/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 do 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/owa/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 |
Here is an example of customized packages using non-standard <BLINK> and imaginary <SHOUT>tags:
create package nsf as
function blink(cbuf in varchar2) return varchar2;
function shout(cbuf in varchar2) return varchar2;
end;
create package body nsf as
function blink(cbuf in varchar2) return varchar2 is
begin return ('<BLINK>' || cbuf || '</BLINK>');
end;
function shout(cbuf in varchar2) return varchar2 is
begin return ('<SHOUT>' || cbuf || '</SHOUT>');
end;
end;
create package nsp as
procedure blink(cbuf in varchar2);
procedure shout(cbuf in varchar2);
end;
create package body nsp as
procedure blink(cbuf in varchar2) is
begin htp.print(nsf.blink(cbuf)); end;
procedure shout(cbuf in varchar2) is
begin htp.print(nsf.shout(cbuf)); end;
end;Now you can begin to use these procedures and functions in your own procedure.
create procedure nonstandard as
begin
nsp.blink('Gee this hurts my eyes!');
htp.print('And I might ' || nsf.shout('get mad!'));
end;
For more examples of using the Developer's Toolkit, see the section, "Passing Parameters to PL/SQL"![[*]](jump.gif)
,"Oracle Web Agent".