This package first appeared in 11g and is used to gather and show
statistics related to Oracle Streams. There are two subprograms,
COLLECT_STATS and SHOW_STATS. Every parameter in COLLECT_STATS has a
default value, as does every parameter in SHOW_STATS. With that in
mind, there is no need to list every parameter if one wants to use
something other than the default value. The way to accomplish this
is to use named notation. List the parameter name followed by "=>"
followed by the new value.
The SPADV part of the package takes its name from the Streams
Performance Advisor. The advisor is a new feature and the best source
of documentation for it (as of this writing) is in
OracleŽ Streams
Concepts and Administration 11g Release 1 (11.1). The output from
SHOW_STATS is spreadsheet ready, and one definitely needs the decoder
table to interpret all of the abbreviations used in the output.
UTL_TCP
The UTL_TCP package enables PL/SQL to communicate with external
servers using TCP/IP. The servers must also be using TCP/IP. The
package is useful for accessing Internet protocols and email. The
newer UTL_MAIL is a wrapper around this package. Examine the sample
code for UTL_SMTP and one can see traces of UTL_TCP in place there as
well.
The subprograms are based on get, read, write, open and close. The
read and write options can handle text lines, binary data, and text
data. The output from UTL_TCP requests can be captured and spooled
into a file. The contents of the file will be fairly similar to what
was obtained using URL_HTTP.
Here is an example set of code to GET lines from a Web site.
set serveroutput on
spool C:\Temp\UTL_TCP_DEMO.html
DECLARE
conn
utl_tcp.connection;
retval PLS_INTEGER;
BEGIN
conn := utl_tcp.open_connection
(remote_host => 'www.dba-oracle.com',
remote_port => 80,
charset => 'US7ASCII');
retval := utl_tcp.write_line(conn,
'GET / HTTP/1.0');
retval := utl_tcp.write_line(conn);
BEGIN
LOOP
dbms_output.put_line(utl_tcp.get_line(conn,
TRUE));
END LOOP;
EXCEPTION
WHEN utl_tcp.end_of_input THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
BEGIN
utl_tcp.flush(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
utl_tcp.close_connection(conn);
END;
/
spool off
In essence, this replicates the example shown in UTL_HTTP. The output
file will have a few more artifacts from the package, but once cleaned
up or commented out, the resulting HTML file will render normally in a
browser. Paths to images may not resolve, so the image box may be seen
on the page, but all other text and formatting will appear as seen on
the actual page itself.
In comparison, using the UTL_HTTP package to scrape a Web site is
easier as it requires fewer lines of code. Most of the default
parameters are adequate. One that may need to be varied is the
timeout parameter. The default of 0 means not to wait,
and a null value means to wait forever. Those conditions may be
extreme, so trying a small non-zero value for timeout may help
connection attempts succeed more often than when set to 0.
To find out more about this utility for the DBA, please refer
to Chapter 6.
UTL_URL
As the last of the UTL packages, UTL_URL is useful for escaping and
unescaping URLs with spaces in them. There have been URLs with "%20"
in between words. That escape sequence represents a space, and a space
is an illegal character, so it has to be escaped. The notation is
based on %hex-code format.
The following is an example of escaping spaces within a URL.
select
utl_url.escape('http://www.acme.com/url
with space.html')
from
dual;
UTL_URL.ESCAPE('HTTP://WWW.ACME.COM/URLWITHSPACE.HTML')
--------------------------------------------------------------
http://www.acme.com/url%20with%20space.html
Likewise, the unescape function returns the URL back to its original
form.
select
utl_url.unescape('http://www.acme.com/url%20with%20space.html')
from
dual;
UTL_URL.UNESCAPE('HTTP://WWW.ACME.COM/URL%20WITH%20SPACE.HTML')
--------------------------------------------------------------------
http://www.acme.com/url with space.html
and include the URL for the page.
