In terms of scale, the documentation behind the UTL_HTTP package is
monstrous compared to many other packages. Given the huge amount of
data under the covers of a Web page, it is not surprising that the
number of subprograms for this package would be quite numerous.
What is a quick and dirty use of UTL_HTTP? The simplest and fastest
example is one’s ability to capture the source of a Web page by using
PL/SQL. Coupled with spooling the output, one now has a simple means
of capturing the source of virtually any Web page.
If the developer can spool to a file, she can load the file into the
database. If there is a BLOB in the database, write it out to the file
system. This workflow should bring to mind the idea of creating a
rudimentary source control system. Not only can the developer store
and generate HTML files based on a combination of UTL_FILE and
UTL_HTTP, but they can be edited as well.
There are applications where the HTML for Web pages served in a
framework is stored in the database. How those pages render is
slightly different than creating HTML files on the file system, but
the concept is the same – the database serves up the source for a
page. So look at a quick example of capturing the source for a page.
Use the home page of dba-oracle.com. This small body of code will
generate almost 900 lines of HTML code and uses eight subprograms in
the UTL_HTTP package.
spool get_page.html
DECLARE
req
UTL_HTTP.REQ;
resp
UTL_HTTP.RESP;
value
VARCHAR2(1024);
BEGIN
req :=
UTL_HTTP.BEGIN_REQUEST('http://dba-oracle.com');
UTL_HTTP.SET_HEADER(req, 'User-Agent',
'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/
spool off
Here is the top part of the captured HTML source.
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>
<head>
<META
name="verify-v1"
content="03pBD3fe1Hr9cZGVzdKBKWKwK7myXtC2l7tXPLFZzbI=" />
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft
FrontPage 6.0">
<title>Oracle Consulting, Oracle Support and
Oracle Training by BC</title>
<meta name="keywords" content="Oracle
Consulting, Oracle support, Oracle Consultants,Oracle
Education,Oracle
contracting,Oracle consulting,Oracle
consultant,Oracle Training,Oracle dba support,Oracle
architecture,Oracle
classes,Oracle,Oracle architecture,Oracle
tuning, Oracle data warehousing">
<meta name="description" content="Burleson
Oracle consulting, Oracle training and Oracle support">
</head>
Any Web page that can be accessed via the browser, minus those
requiring authentication, can be captured. With this ability, one can
sample pages and check to see if there are any differences. But what
about HTTPS pages? For these pages, one needs to configure Wallet
Manager. On Windows, this can be found under Start > Programs > the
Oracle home > Integrated Management Tools > Wallet Manager, and in
UNIX, use one found in $ORACLE_HOME/bin. Use of Wallet Manager enables
the developer to capture secure Web pages, secure in the sense that
this is what one is allowed to access via the wallet information.
If the returned page is large, use the REQUEST_PIECES function. This
function returns a PL/SQL table of 2000 bytes. In the prior example,
how many pieces were returned and what was the length? Using the
example in Oracle’s documentation, and correcting the error for the
length output (use “len,” not “i” in the last DBMS_OUTPUT), the
following occurs:
DECLARE
x
UTL_HTTP.HTML_PIECES;
len PLS_INTEGER;
BEGIN
x :=
UTL_HTTP.REQUEST_PIECES('http://dba-oracle.com/', 100);
DBMS_OUTPUT.PUT_LINE(x.count || '
pieces were retrieved.');
DBMS_OUTPUT.PUT_LINE('with total
length ');
IF x.count < 1 THEN
DBMS_OUTPUT.PUT_LINE('0');
ELSE
len := 0;
FOR i in 1..x.count LOOP
len := len + length(x(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE(len);
END IF;
END;
/
23 pieces were retrieved.
with total length
44356
The output using this against
www.oracle.com is 22 pieces with length 43086, which is quite
different than what is shown in Oracle’s documentation (4 pieces and
length 7687).
