Appendix AN

Overview

In this section, we'll look at UTL_HTTP and what it can be used for. Additionally, I would like to introduce a new and improved UTL_HTTP built on the SocketType implemented in the UTL_TCP section. It gives performance comparable to the native UTL_HTTP but provides many more features.

The UTL_HTTP package supplied with the database is relatively simplistic - it has two versions:

  • UTL_HTTP.REQUEST: returns up to the first 2,000 bytes of the content of a URL as a function return value.

  • UTL_HTTP.REQUEST_PIECES: returns a PL/SQL table of VARCHAR2(2000) elements. If you concatenated all of the pieces together - you would have the content of the page.

The UTL_HTTP package is missing the following functionality however:

  • You cannot inspect the HTTP headers. This makes error reporting impossible. You cannot tell the difference between a Not Found and Unauthorized for example.

  • You cannot POST information to a web server that requires POSTing data. You can only use the GET syntax. Additionally HEAD is not supported in the protocol.

  • You cannot retrieve binary information using UTL_HTTP.

  • The request pieces API is non intuitive, the use of CLOBs and BLOBs to return the data as a 'stream' would be much more intuitive (and give us access to binary data).

  • It does not support cookies.

  • It does not support basic authentication.

  • It has no methods for URL encoding data.

One thing that UTL_HTTP does support that we will not in our re-implementation is SSL. Using the Oracle Wallet manager, it is possible to perform a HTTPS request (HTTPS is using SSL with HTTP). We will demonstrate using UTL_HTTP over SSL, but will not implement it in our own HTTP_PKG. Due to its size, the source code of the HTTP_PKG body will be omitted from this chapter - it is available in its entirety on the Apress website at http://www.apress.com/

UTL_HTTP Functionality

We will look at the UTL_HTTP functionality first as we will support its syntax on our own HTTP_PKG. The simplest form of UTL_HTTP is as follows. In this example, myserver is the name I have given to the web server. Of course, you should try this example using a web server to which you have access:

ops$tkyte@DEV816> select utl_http.request( 'http://myserver/' ) from dual;      UTL_HTTP.REQUEST('HTTP://MYSERVER/') ----------------------------------------------------------- <HTML> <HEAD> <TITLE>Oracle Service Industries</TITLE> </HEAD> <FRAMESET COLS="130,*" border=0> <FRAME src="/books/1/580/1/html/2/navtest.html" NAME="sidebar" frameborder=0> <FRAME src="/books/1/580/1/html/2/folder_home.html" NAME="body" frameborder="0" marginheight="0" marginwidth="0"> </FRAMESET> </BODY> </HTML> 

I can simply run UTL_HTTP.REQUEST and send it a URL. UTL_HTTP will connect to that web server and GET the requested page and then return the first 2,000 characters of it. As mentioned above, don't try to use the URL I have given above, it is for my web server inside of Oracle. You won't be able to get to it, the request will time out with an error message.

Most networks today are protected by firewalls. If the page I wanted was only available via a firewall proxy server; I can request that as well. A discussion of firewalls and proxy servers is beyond the scope of this book. However, if you know the hostname of your proxy server, you can retrieve a page from the internet via this method:

ops$tkyte@DEV816> select utl_http.request( 'http://www.yahoo.com', 'www-proxy') from dual;      UTL_HTTP.REQUEST('HTTP://WWW.YAHOO.COM','WWW-PROXY') ------------------------------------------------------------------- <html><head><title>Yahoo!</title><base href=http://www.yahoo.com/><meta http- equiv="PICS-Label" content='(PICS-1.1 http://www.rsac.org/ratingsv01.html" l gen true for "http://www.yahoo.com" r (n 0 s 0 v 0 l 0))'></head><body><center><form action=http://search.yahoo.com/bin/search><map name=m><area coords="0,0,52,52" href=r/a1><area coords="53,0,121,52" href=r/p1><area coords="122,0,191,52" href=r 

The second parameter to UTL_HTTP.REQUEST and REQUEST_PIECES is the name of a proxy server. If your proxy server is not running on the standard port 80, we can add the port as follows (In the code this is myserver on port 8000):

ops$tkyte@DEV816> select utl_http.request( 'http://www.yahoo.com',   2  'myserver:8000' ) from dual   3  /      UTL_HTTP.REQUEST('HTTP://WWW.YAHOO.COM','MYSERVER:8000') ------------------------------------------------------- <html><head><title>Yahoo!</title><base href=http://www.yahoo.com/ 

So, by simply adding the :8000 to the proxy server name, we are able to connect to that proxy server. Now, let's look at the REQUEST_PIECES interface:

ops$tkyte@DEV816> declare   2      pieces  utl_http.html_pieces;   3      n       number default 0;   4      l_start number default dbms_utility.get_time;   5  begin   6      pieces :=   7           utl_http.request_pieces( url => 'http://www.oracle.com/',   8                                    max_pieces => 99999,   9                                    proxy => 'www-proxy' );  10      for i in 1 .. pieces.count  11      loop  12          loop  13              exit when pieces(i) is null;  14              dbms_output.put_line( substr(pieces(i),1,255) );  15              pieces(i) := substr( pieces(i), 256 );  16          end loop;  17      end loop;  18  end;  19  /      <head> <title>Oracle Corporation</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <meta name="description" content="Oracle Corporation provides the software that powers the Internet. For more information about Oracle, pleas e call 650/506-7000."> 

The request pieces API cannot be called from SQL since it does not return a SQL type but rather a PL/SQL table type. So, REQUEST_PIECES is only useful inside of a PL/SQL block itself. In the above, we are requesting the web page http://www.oracle.com/ and we are requesting the first 99,999 chunks, which are each of the size 2,000 bytes. We are using the proxy server www-proxy. We must tell REQUEST_PIECES how many 2,000 byte chunks we are willing to accept, typically I set this to a very large number as I want the entire page back. If the information that you want from the page is always in the first 5000 bytes, you could request just 3 chunks to get it.

Adding SSL to UTL_HTTP

UTL_HTTP also supports using SSL (Secure Sockets Layer). If you are not familiar with SSL and what it is used for, you can find a brief description at http://www.rsasecurity.com/rsalabs/faq/5-1-2.html Both the REQUEST and REQUEST_PIECES functions in UTL_HTTP support the retrieval of URLs that are protected by SSL. However, the available documentation on doing so can be described as sparse at best. SSL support is provided by using the last two parameters in the UTL_HTTP.REQUEST and UTL_HTTP.REQUEST_PIECES procedures. These parameters are the WALLET_PATH and WALLET_PASSWORD.

Oracle uses the wallet as a metaphor for how a person stores their security credentials; just like you would keep your driver's license and credit cards in your wallet for identification purposes, the Oracle wallet stores the credentials needed by the SSL protocol. The WALLET_PATH is the directory where your wallet is stored on the database server machine. This wallet is password protected to prevent someone from using your credentials. This is the purpose of the WALLET_PASSWORD parameter, it is used to access the wallet. The password prevents people from copying the wallet directory and trying to impersonate you, as they will be unable to open and access the wallet. This is analogous to using a PIN for using an ATM machine. If someone steals your bank card, they need to have your PIN to get to your accounts.

The wallet, or the concept of a wallet is used not only by the Oracle database, but also in Web browsers. The important aspect is that when you connect to a site, e.g., http://www.amazon.com/ how do you know that it is really Amazon.com? You have to get their certificate, which is digitally signed by someone. That someone is called a Certificate Authority or CA. How does my browser or database know to trust the CA that signed that certificate? For example, I could create a certificate for Amazon.com and sign it from hackerAttackers.com. My browser and database should not accept this certificate even though it is a legitimate X.509 certificate.

The answer to this trust issue is the wallet stores a set of trusted certificates. A trusted certificate is a certificate from a CA that you trust. The Oracle wallet comes with some common trusted certificates. You also have the ability to add certificates as necessary. Your browser does the same thing. If you ever connect to a site where your browser does not have the CA in its wallet, you will get a pop-up window that notifies you of this as well as a wizard that allows you to proceed or abort your connection.

Let's see some examples of how to use SSL. First, we need to create a new wallet. You can invoke the OWM (Oracle Wallet Manager) program on UNIX, or launch it from the Windows START menu on Windows(it is in ORACLE HOME|NETWORK ADMINISTRATION). The screen you receive to do this will look like this:

click to expand

All you need to do is click on the NEW icon (the green 'cube') located on the left hand side of the display. It will prompt you for a password for this wallet, you are making up the password at this point so enter whatever password you would like. You may get a warning about a directory not existing, if you do, you should simply ignore it. It is the expected behavior if you have never created a wallet before. OWM at will then ask you to create a certificate request:

click to expand

you do not need to this. The certificate request is so you can get a certificate for yourself. This would be used in SSL v.3 where the server needs the identification of the client. Most Web sites do not authenticate users via certificates, but rather by using a username and password. This is because an e - commerce site doesn't care who is buying from them as long as they get the money. But you do care that you are sending the money (and credit card information) to the correct entity, so we use SSL v.2 to identify the server for example, Amazon.com, and to provide all the encryption of data. So, click NO in response to this and save the wallet by clicking on the SAVE WALLET (the yellow floppy disk) icon and we are ready to go.

Let's go to Amazon.com first. Amazon's certificate was signed by Secure Server Certificate Authority, RSA Data Security, Inc. This is one of the defaults in the Oracle wallet.

tkyte@TKYTE816> declare   2     l_output long;   3   4     l_url varchar2(255) default   5           'https://www.amazon.com/exec/obidos/flex-sign-in/';   6   7     l_wallet_path varchar2(255) default   8           'file:C:\Documents and Settings\Thomas Kyte\ORACLE\WALLETS';   9  10  11  begin  12    l_output := utl_http.request  13               ( url             => l_url,  14                 proxy           => 'www-proxy.us.oracle.com',  15                 wallet_path     => l_wallet_path,  16                 wallet_password => 'oracle'  17               );  18    dbms_output.put_line(trim(substr(l_output,1,255)));  19  end;  20  /                <html> <head> <title>Amazon.com Error Page</title> </head> <body bgcolor="#FFFFFF" link="#003399" alink="#FF9933" vlink="#996633" text="#000000"> <a name="top"><!--Top of Page--></a> <table border=0 width=100% cellspacing=0 cellpadding=0> <tr      PL/SQL procedure successfully completed. 

Don't worry about getting this error page; this is accurate. The reason for receiving this error page is that there is no session information being passed. We are just testing that the connection worked here; we retrieved an SSL protected document.

Let's try another site. How about E*Trade?

tkyte@TKYTE816> declare   2     l_output long;   3   4     l_url varchar2(255) default   5           'https://trading.etrade.com/';   6   7     l_wallet_path varchar2(255) default   8           'file:C:\Documents and Settings\Thomas Kyte\ORACLE\WALLETS';   9  10  11  begin  12    l_output := utl_http.request  13               ( url             => l_url,  14                 proxy           => 'www-proxy.us.oracle.com',  15                 wallet_path     => l_wallet_path,  16                 wallet_password => 'oracle'  17               );  18    dbms_output.put_line(trim(substr(l_output,1,255)));  19  end;  20  / declare * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.UTL_HTTP", line 174 ORA-06512: at line 12 

That apparently does not work. E*Trade has a certificate signed by. www.verisign com/CPS Incorp.by Ref, which is not a default trusted certificate. In order to access this page, we'll have to add that certificate to our Oracle wallet - assuming of course that we trust Verisign! Here is the trick. Go to the site (https://trading.etrade.com/). Double-click the PADLOCK icon on the bottom right corner of the window (in Microsoft Internet Explorer). This will pop-up a window that looks similar to this one:

click to expand

Select the Certification Path tab on the top of this screen. This lists the certificate you are viewing here it is the one for E*Trade (trading.etrade.com), as well as who issued the certificate. We need to add the person who signed the certificate (the issuer) to our trusted certificates in the Oracle wallet. The issuer is www.verisign.com/CPS Incorp.by Ref. LIABILITY LTD as depicted by the tree-like hierarchy.

click to expand

Click the View Certificate button while www.verisign.com/CPS Incorp. by Ref. is highlighted. This shows information for the issuer's certificate. Click the Details tab and you should see:

click to expand

Now we need to Click the Copy to File button. Save the file locally as a Base-64 encoded X.509 (CER) file. The following screen shows the selection you should make, you can name the file anything you choose and save it anywhere. We'll be importing it in a moment, just remember where you save it to:

click to expand

Now we can import this into our Oracle Wallet. Open the wallet in OWM and right click on the Trusted Certificates - this will present you with a popup menu that has Import Trusted Certificate:

click to expand

You will select that option and in the next dialog that comes up, choose Select a file that contains the certificate

click to expand

Use the standard 'file open' dialog that comes up to choose the certificate you just saved. Your screen should now look something like this:

click to expand

Now, save the wallet using clicking on the SAVE WALLET (the yellow floppy disk) icon and let's try our example again:

tkyte@TKYTE816> declare   2     l_output long;   3   4     l_url varchar2(255) default   5           'https://trading.etrade.com/cgi-bin/gx.cgi/AppLogic%2bHome';   6   7     l_wallet_path varchar2(255) default   8           'file:C:\Documents and Settings\Thomas Kyte\ORACLE\WALLETS';   9  10  11  begin  12    l_output := utl_http.request  13               ( url             => l_url,  14                 proxy           => 'www-proxy.us.oracle.com',  15                 wallet_path     => l_wallet_path,  16                 wallet_password => 'oracle'  17               );  18    dbms_output.put_line(trim(substr(l_output,1,255)));  19  end;  20  / <HTML> <HEAD> <META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <TITLE>E*TRADE</TITLE> <SCRIPT LANGUAGE="Javascript" TYPE="text/javascript">         <!--              function mac_comment(){                 var agt=navigator.userAgent.toLowerCase();                 var is_mac      PL/SQL procedure successfully completed. 

This time we are successful. Now we know how to use and extend the Oracle wallet to do secure HTTPS.

Really Using UTL_HTTP

Well, besides getting the content of a web page which is extremely useful - what else can we do with UTL_HTTP? Well, a common use for it is an easy way to make it so that PL/SQL can run a program - sort of like a HOST command. Since almost every web server can run cgi-bin programs and UTL_HTTP can send URLs, we can in effect have PL/SQL execute host commands by configuring the commands we wanted to execute as cgi-bin programs under the webserver.

What I like to do in this case is setup a web server running on IP address 127.0.0.1 - which is the TCP/IP loop back. This IP address is only accessible if you are physically logged onto the machine the web server is running on. In that fashion, I can set up cgi-bin programs for my PL/SQL programs to run that no one else can - unless they break in to my server machine in which case I have much larger problems to deal with.

One use I've made of this facility in the past is to send e-mail from PL/SQL. Let's say you have the Oracle 8i database without Java in it. Without Java you cannot use either UTL_TCP or UTL_SMTP - they both rely on the Java option in the database. So, without UTL_SMTP and/or UTL_TCP, how could we send an email? With UTL_HTTP and UTL_FILE - I can set up a cgi-bin program that receives a single input in the QUERY_STRING environment variable. The single input would be a file name. We will use /usr/lib/sendmail to send that file (on Windows I could use the public domain utility 'blat' to send mail, available from http://www.interlog.com/~tcharron/blat.html). Once I have that set up, I can simply run my host command via:

        results := utl_http.request(                 'http://127.0.0.1/cgi-bin/smail?filename.text' );      

The cgi-bin program I set up, smail, would return a 'web page' that indicated success or failure, I would look at the result's variable to see if the mail was sent successfully or not. The full fledged implementation of this on Unix could be:

scott@ORA8I.WORLD> create sequence sm_seq   2  / Sequence created.      scott@ORA8I.WORLD> create or replace procedure sm( p_to in varchar2,   2                                  p_from in varchar2,   3                                  p_subject in varchar2,   4                                  p_body in varchar2 )   5  is   6      l_output        utl_file.file_type;   7      l_filename      varchar2(255);   8      l_request       varchar2(2000);   9  begin  10      select 'm' || sm_seq.nextval || '.EMAIL.' || p_to  11        into l_filename  12        from dual;  13  14      l_output := utl_file.fopen  15                  ( '/tmp', l_filename, 'w', 32000 );  16  17      utl_file.put_line( l_output, 'From: ' || p_from );  18      utl_file.put_line( l_output, 'Subject: ' || p_subject );  19      utl_file.new_line( l_output );  20      utl_file.put_line( l_output, p_body );  21      utl_file.new_line( l_output );  22      utl_file.put_line( l_output, '.' );  23  24      utl_file.fclose( l_output );  25  26      l_request := utl_http.request  27                   ( 'http://127.0.0.1/cgi-bin/smail?' || l_filename );  28  29      dbms_output.put_line( l_request );  30  end sm;  31  /      Procedure created. 

You should refer to the section on UTL_SMTP to understand why I formatted the email as I did here with the From: and To: header records. In this routine we are using a sequence to generate a unique filename. We encode the recipient in the name of the file itself. Then, we write the email to an OS file. Lastly, we use UTL_HTTP to run our host command and pass it the name of the file. We simply print out the result of this in this test case - we would really be inspecting the value of l_result to ensure the e-mail was sent successfully.

The simple cgi-bin program could be:

#!/bin/sh      echo "Content-type: text/plain" echo ""      echo $QUERY_STRING to=`echo $QUERY_STRING|sed 's/.*EMAIL\.//'` echo $to      (/usr/lib/sendmail $to < /tmp/$QUERY_STRING) 1>> /tmp/$$.log 2>&1 cat /tmp/$$.log rm /tmp/$$.log rm /tmp/$QUERY_STRING 

The shell script starts by printing out the HTTP headers that we need in order to return a document; that is what the first two echoes are doing. Then, we are simply printing out the value of the QUERY_STRING (this is where the web server puts our inputs - the portion after the ? in the URL). We then extract the email address from the QUERY_STRING by using the sed (Stream EDitor) to remove everything in front of the word EMAIL/ in the filename. We then run sendmail in a subshell so as to be able to capture both its stdout and stderr output streams. I cat (type to stdout) the contents of the log we captured from sendmail. In this case I wanted both stdout and stderr to be returned as the contents of the web page, so the PL/SQL code could get any error messages and such. The easiest way to do that was to redirect both output streams to a temporary file and then type that file to stdout. We then clean up our temp files and return.

I can now test this via:

scott@ORA8I.WORLD> begin   2      sm( 'tkyte@us.oracle.com',   3          'tkyte@us.oracle.com',   4          'testing',   5          'hello world!' );   6  end;   7  / m1.EMAIL.tkyte@us.oracle.com tkyte@us.oracle.com 

This shows us the QUERY_STRING and the To: environment variables, and since there is no other test (no error messages) we know the e-mail was sent.

So, this small example shows how UTL_HTTP can be used indirectly for something it was not really designed for; to allow PL/SQL to run HOST commands. You would set up a special purpose web server on IP Address 127.0.0.1, configure a cgi-bin type directory within it and place the commands you want PL/SQL developers to run in there. Then you have a secure way to allow PL/SQL to run the equivalent of SQL*PLUS's host command.

A Better UTL_HTTP

Given that we have the SocketType class developed in the UTL_TCP section (or just access to UTL_TCP alone) and knowledge of the HTTP protocol, we can make an improved UTL_HTTP package. We will call our implementation HTTP_PKG. It will support the "old fashioned" UTL_HTTP interface of REQUEST and REQUEST_PIECES but will also add support for:

We can implement all of this in PL/SQL using our SocketType from the UTL_TCP section and we can do it with a fairly small amount of code. The specification for our new HTTP_PKG package follows. We'll look at the specification and some examples that use it. What will not be in this book is the code that implements the body of the HTTP_PKG. This code is available and documented on the Apress website, (http://www.apress.com/), it is about 15 printed pages long hence it is not included here. The package specification is as follows. The first two functions, REQUEST and REQUEST_PIECES, are functionally equivalent (minus SSL support) to functions found in the UTL_HTTP package in versions 7.3.x, 8.0.x, and 8.1.5, we even will raise the same sort of named exceptions they would:

tkyte@TKYTE816> create or replace package http_pkg   2  as   3      function request( url in varchar2,   4                        proxy in varchar2 default NULL )   5      return varchar2;   6   7      type html_pieces is table of varchar2(2000)   8         index by binary_integer;   9  10      function request_pieces(url in varchar2,  11                              max_pieces natural default 32767,  12                              proxy in varchar2 default NULL)  13      return html_pieces;  14  15      init_failed exception;  16      request_failed exception; 

The next procedure is GET_URL. It invokes the standard HTTP GET command on a web server. The inputs are:

 19      procedure get_url( p_url        in varchar2,  20                         p_proxy      in varchar2 default NULL,  21                         p_status           out number,  22                         p_status_txt       out varchar2,  23                         p_httpHeaders   in out CorrelatedArray,  24                         p_content    in out clob );  25  26  27      procedure get_url( p_url        in     varchar2,  28                         p_proxy      in     varchar2 default NULL,  29                         p_status           out number,  30                         p_status_txt       out varchar2,  31                         p_httpHeaders   in out CorrelatedArray,  32                         p_content    in out blob ); 

The next procedure is HEAD_URL. Invokes the standard HTTP HEAD syntax on a web server. The inputs and outputs are identical to get_url above (except no content is retrieved). This function is useful to see if a document exists, what its mime-type is, or if it has been recently changed, without actually retrieving the document itself:

 34      procedure head_url( p_url        in varchar2,  35                          p_proxy      in varchar2 default NULL,  36                          p_status        out number,  37                          p_status_txt       out varchar2,  38                          p_httpHeaders   out CorrelatedArray ); 

The next function URL encode is used when building GET parameter lists or building POST CLOBs. It is used to escape special characters in URLs (for example, a URL may not contain a whitespace, a % sign, and so on). Given input such as Hello World, then urlencode will return Hello%20World'

40      function urlencode( p_str in varchar2 ) return varchar2; 

Procedure Add_A_Cookie allows you to easily set a cookie value to be sent to a web server. You need only know the name and value of the cookie. The formatting of the HTTP header record is performed by this routine. The p_httpHeaders variable you send in/out of this routine would be sent in/out of the <Get|Head|Post>_url routines:

 42      procedure add_a_cookie  43               ( p_name in varchar2,  44                 p_value in varchar2,  45                 p_httpHeaders in out CorrelatedArray ); 

The next procedure Set_Basic_Auth allows you to enter a username/password to access a protected page. The formatting of the HTTP header record is performed by this routine. The p_httpHeaders variable you send in/out of this routine would be sent in/out of the <Get|Head|Post>_url routines as well:

 47      procedure set_basic_auth  48                ( p_username in varchar2,  49                  p_password in varchar2,  50                  p_httpHeaders in out CorrelatedArray ); 

The procedure set_post_parameter is used when retrieving a URL that needs a large (greater than 2,000 or so bytes) set of inputs. It is recommended that the POST method be used for large requests. This routine allows you to add parameter after parameter to a POST request. This post request is built into a CLOB which you supply:

 52      procedure set_post_parameter  53                ( p_name in varchar2,  54                  p_value in varchar2,  55                  p_post_data in out clob,  56                  p_urlencode in boolean default FALSE ); 

The next two routines are identical to GET_URL above with the addition of the p_post_data input p_post_data is a CLOB built by repeated calls to set_post_parameter above. The remaining inputs/outputs are defined the same as they were for GET_URL:

 58      procedure post_url  59                (p_url        in     varchar2,  60                 p_post_data  in     clob,  61                 p_proxy      in     varchar2 default NULL,  62                 p_status           out number,  63                 p_status_txt       out varchar2,  64                 p_httpHeaders   in out CorrelatedArray,  65                 p_content    in out clob );  66  67      procedure post_url  68                (p_url        in     varchar2,  69                 p_post_data  in     clob,  70                 p_proxy      in     varchar2 default NULL,  71                 p_status           out number,  72                 p_status_txt       out varchar2,  73                 p_httpHeaders   in out CorrelatedArray,  74                 p_content    in out blob );  75  76  77  end;  78  /      Package created.      

So, the specification of the package is done; the procedures defined within it are rather straightforward. I can do things like GET_URL to get a URL. This will use the HTTP GET syntax to retrieve the contents of a web page into a temporary BLOB or CLOB. I can HEAD_URL to get the headers for a URL. Using this I could look at the mime type for example to decide if I wanted to use a CLOB (text/html) to get the URL or a BLOB (image/gif). I can even POST_URL to post large amounts of data to a URL. There are the other helper functions to set cookies in the header, to base 64 encode the username and password for basic authentication and so on.

Now, assuming you have downloaded the implementation of HTTP_PKG (the package body consists of about 500 lines of PL/SQL code) we are ready to try it out. We'll introduce a pair of utility routines that will be useful to test with first. Print_clob below simply prints out the entire contents of a CLOB using DBMS_OUTPUT. Print_Headers does the same for the HTTP headers we retrieve above in our CorrelatedArray type (an object type that is part of the HTTP_PKG implementation). In the following, the procedure P is the P procedure I introduced in the DBMS_OUTPUT section to print long lines:

ops$tkyte@DEV816> create or replace procedure print_clob( p_clob in clob )   2  as   3       l_offset number default 1;   4  begin   5     loop   6       exit when l_offset > dbms_lob.getlength(p_clob);   7       dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );   8       l_offset := l_offset + 255;   9     end loop;  10  end;  11  /      ops$tkyte@DEV816> create or replace   2  procedure print_headers( p_httpHeaders correlatedArray )   3  as   4  begin   5       for i in 1 .. p_httpHeaders.vals.count loop   6           p( initcap( p_httpHeaders.vals(i).name ) || ': ' ||   7                      p_httpHeaders.vals(i).value );   8       end loop;   9       p( chr(9) );  10  end;  11  / 

Now onto the test:

ops$tkyte@DEV816> begin   2       p( http_pkg.request( 'http://myserver/' ) );   3  end;   4  / <HTML> <HEAD> <TITLE>Oracle Service Industries</TITLE> </HEAD> <FRAMESET COLS="130,*" border=0> <FRAME src="/books/1/580/1/html/2/navtest.html" NAME="sidebar" frameborder=0> <FRAME src="/books/1/580/1/html/2/folder_home.html" NAME="body" frameborder="0" marginheight="0" marginwidth="0"> </FRAMESET>      </BODY> </HTML> ops$tkyte@DEV816> declare   2       pieces    http_pkg.html_pieces;   3  begin   4       pieces :=   5           http_pkg.request_pieces( 'http://www.oracle.com',   6                                     proxy=>'www-proxy1');   7   8       for i in 1 .. pieces.count loop   9           p( pieces(i) );  10       end loop;  11  end;  12  / <head> <title>Oracle Corporation</title> <meta http-equiv="Content-Type" content="text/html;   

The above two routines simply show that the UTL_HTTP methods of REQUEST and REQUEST_PIECES function as expected with our new package. Their functionality is identical. Now we will invoke our URLENCODE function that translates 'bad characters into escape sequences in URLs and POST data:

ops$tkyte@DEV816> select   2  http_pkg.urlencode( 'A>C%{hello}\fadfasdfads~`[abc]:=$+''"' )   3  from dual;      HTTP_PKG.URLENCODE('A>C%{HELLO}\FADFASDFADS~`[ABC]:=$+''"') --------------------------------------------------------------- A%3EC%25%7Bhello%7D%5Cfadfasdfads%7E%60%5Babc%5D%3A%3D%24%2B%27%22 

That shows that characters like > and % are escaped into %3E and %25 respectively and other sequences such as the word hello are not escaped. This allows us to use any of these special characters in our HTTP requests safely.

Now we will see the first of the new HTTP URL procedures. This procedure call will return Yahoo's home page via a proxy server, www-proxy1, (you'll need to replace this with your own proxy server of course). Additionally, we get to see the HTTP status returned - 200 indicates success. We also see the HTTP headers Yahoo returned to us. The mime-type will always be in there and that tells us what type of content we can expect. Lastly, the content is returned and printed out:

ops$tkyte@DEV816> declare   2       l_httpHeaders      correlatedArray;   3       l_status     number;   4       l_status_txt     varchar2(255);   5       l_content      clob;   6  begin   7       http_pkg.get_url( 'http://www.yahoo.com/',   8                         'www-proxy1',   9                          l_status,  10                          l_status_txt,  11                          l_httpHeaders,  12                          l_content );  13  14       p( 'The status was ' || l_status );  15       p( 'The status text was ' || l_status_txt );  16       print_headers( l_httpHeaders );  17       print_clob( l_content );  18  end;  19  / The status was 200 The status text was HTTP/1.0 200 OK      Date: Fri, 02 Feb 2001 19:13:26 GMT Connection: close Content-Type: text/html      <html><head><title>Yahoo!</title><base href=http://www.yahoo.com/><meta http- equiv="PICS-Label"      

Next, we will try the HEAD request against the home page of a sample site, let's call it Sample.com, and see what we can discover:

ops$tkyte@DEV816> declare   2       l_httpHeaders      correlatedArray;   3       l_status     number;   4       l_status_txt     varchar2(255);   5  begin   6       http_pkg.head_url( 'http://www.sample.com/',   7                          'www-proxy1',   8                           l_status,   9                           l_status_txt,  10                           l_httpHeaders );  11  12       p( 'The status was ' || l_status );  13       p( 'The status text was ' || l_status_txt );  14       print_headers( l_httpHeaders );  15  end;  16  / The status was 200 The status text was HTTP/1.1 200 OK      Server: Microsoft-IIS/5.0 Date: Fri, 02 Feb 2001 19:13:26 GMT Connection: Keep-Alive Content-Length: 1270 Content-Type: text/html Set-Cookie: ASPSESSIONIDQQQGGNQU=PNMNCIBACGKFLHGKLLBPEPMD; path=/ Cache-Control: private 

From the headers, it is obvious that Wrox is running Windows with Microsoft IIS. Further, they are using ASP's as indicated by the cookie they sent back to us. If we were to have retrieved that page, it would have had 1,270 bytes of content.

Now we would like to see how cookies might work. Here I am using a standard procedure that is used with OAS (the Oracle Application Server) and iAS (Oracle's Internet Application Server); the cookiejar sample that shows how to use cookies in a PL/SQL web procedure. The routine cookiejar looks at the cookie value and if set, increments it by one and returns it to the client. We'll see what how that would work using our package. We are going to send the value 55 to the server and we are expecting it to send us 56 back:

ops$tkyte@DEV816> declare   2       l_httpHeaders     correlatedArray;   3       l_status     number;   4       l_status_txt     varchar2(255);   5       l_content     clob;   6  begin   7       http_pkg.add_a_cookie( 'COUNT', 55, l_httpHeaders );   8       http_pkg.get_url   9       ( 'http://myserver.acme.com/wa/webdemo/owa/cookiejar',  10          null,  11          l_status,  12          l_status_txt,  13          l_httpHeaders,  14          l_content );  15  16       p( 'The status was ' || l_status );  17       p( 'The status text was ' || l_status_txt );  18       print_headers( l_httpHeaders );  19       print_clob( l_content );  20  end;  21  / The status was 200 The status text was HTTP/1.0 200 OK      Content-Type: text/html Date: Fri, 02 Feb 2001 19:14:48 GMT Allow: GET, HEAD Server: Oracle_Web_listener2.1/1.20in2 Set-Cookie: COUNT=56; expires=Saturday, 03-Feb-2001 22:14:48 GMT      <HTML> <HEAD> <TITLE>C is for Cookie</TITLE> </HEAD> <BODY> <HR> <IMG src="/books/1/580/1/html/2//ows-img/ows.gif"> <H1>C is for Cookie</H1> <HR> You have visited this page <STRONG>56</STRONG> times in the last 24 hours.   

As you can see, the cookie value of 55 was transmitted and the server incremented it to 56. It then sent us back the modified value along with an expiration date.

Next, we would like to see how to access a page that requires a username and password. This is done via the following:

ops$tkyte@DEV816> declare   2      l_httpHeaders   correlatedArray;   3      l_status        number;   4      l_status_txt    varchar2(255);   5      l_content       clob;   6  begin   7      http_pkg.set_basic_auth( 'tkyte', 'tiger', l_httpheaders );   8      http_pkg.get_url   9      ( 'http://myserver.acme.com:80/wa/intranets/owa/print_user',  10         null,  11         l_status,  12         l_status_txt,  13         l_httpHeaders,  14         l_content );  15  16      p( 'The status was ' || l_status );  17      p( 'The status text was ' || l_status_txt );  18      print_headers( l_httpHeaders );  19      print_clob(l_content);  20  end;  21  / The status was 200 The status text was HTTP/1.0 200 OK      Content-Type: text/html Date: Fri, 02 Feb 2001 19:49:17 GMT Allow: GET, HEAD Server: Oracle_Web_listener2.1/1.20in2      remote user = tkyte 

Here, I just set up a DAD (Database Access Descriptor) that did not store the username/password with the DAD. This means the web server is expecting the request to contain the username/password to use. Here I passed my credentials to a routine that simply printed out the REMOTE_USER cgi-environment variable in PL/SQL (the name of the remotely connected user).

Lastly, we would like to demonstrate the POST'ing of data. Here I am using a URL from Yahoo again. Yahoo makes it easy to get stock quotes in a spreadsheet format. Since the list of stock symbols you might be interested in could get quite large, I would suggest POSTing this data. Here is an example that gets a couple of stock quotes from Yahoo using HTTP. The data will be returned in CSV (Comma Separated Values) for easy parsing and loading into a table for example:

ops$tkyte@DEV816> declare   2      l_httpHeaders   correlatedArray;   3      l_status        number;   4      l_status_txt    varchar2(255);   5      l_content       clob;   6      l_post          clob;   7  begin   8      http_pkg.set_post_parameter( 'symbols','orcl ^IXID ^DJI ^SPC',   9                                    l_post, TRUE );  10      http_pkg.set_post_parameter( 'format', 'sl1d1t1c1ohgv',  11                                    l_post, TRUE );  12      http_pkg.set_post_parameter( 'ext', '.csv',  13                                    l_post, TRUE );  14      http_pkg.post_url( 'http://quote.yahoo.com/download/quotes.csv',  15                          l_post,  16                         'www-proxy',  17                          l_status,  18                          l_status_txt,  19                          l_httpHeaders,  20                          l_content );  21  22      p( 'The status was ' || l_status );  23      p( 'The status text was ' || l_status_txt );  24      print_headers( l_httpHeaders );  25      print_clob( l_content );  26  end;  27  / The status was 200 The status text was HTTP/1.0 200 OK      Date: Fri, 02 Feb 2001 19:49:18 GMT Cache-Control: private Connection: close Content-Type: application/octet-stream      "ORCL",28.1875,"2/2/2001","2:34PM",-1.875,29.9375,30.0625,28.0625,26479100 "^IXID",1620.60,"2/2/2001","2:49PM",-45.21,1664.55,1667.46,1620.40,N/A "^DJI",10899.33,"2/2/2001","2:49PM",-84.30,10982.71,11022.78,10888.01,N/A "^SPC",1355.17,"2/2/2001","2:49PM",-18.30,1373.53,1376.16,1354.21,N/A 

Summary

In this section, we have seen how to use the built-in UTL_HTTP package. We have seen how through a little creative thinking, we can use UTL_HTTP not only to grab data from the web, but to enable PL/SQL to have the equivalent of a HOST command. With just a couple of lines of code, we made it possible that any release of Oracle from 7.3 on up can easily send mail using UTL_FILE and UTL_HTTP.

We then investigated how to use UTL_HTTP over SSL - a concept not well documented in the Oracle Supplied Packages Guide (or any other document for that matter). We learned how to make any SSL enabled website available to our PL/SQL routines using the Oracle Wallet Manager.

Additionally, we've seen how we can take a good idea and make it better. In comparison to the section on DBMS_OBFUSCATION_TOOLKIT where we 'wrapped' the functionality of that package to make it easier and more flexible to use, here we totally re-implemented a package giving it additional functionality it never had. This came at a certain price, we do not support SSL in our implementation, but it is useful in many cases nonetheless.

We could easily take this a step further and user either Java or a C based External Procedure to add full support for SSL as well. There are also various third party and public domain classes and libraries out there to do just that.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net