A Simple Web Client

Cloud  computing and software as a service providers have lots of capacity and attractive rates, so many organizations are taking their applications to the cloud.  After an application becomes cloud-based, many users need to pull some of the data to feed to other systems.  In this post, we'll look at a simple PL/SQL web client  that connects to a web service and downloads data. 

For our data,  we'll use the Data Download Program at the Federal Reserve Bank.  There's a lot of statistical data on the Federal Reserve site, and we will use that data as the source for our simple web client.   The data files are small and free to download, so the Bank gives us a good source of data to use.

From the Data Download Program we click on the Foreign Exchange Rates (G.5 / H.10) link, then we get a page that lets us choose which package we would like.  The H10 radio button is selected by default, so clicking the Download button takes us to the download page.  We could download a CSV into our browser and save the result as an OpenOffice or Excel spreadsheet.  But for production purposes, following these links and downloading data through a web browser is a tedious chore. Notice the Direct Download for Automated Systems link?   We'll copy that link and use it for our demonstration. 

create or replace procedure simple_web_client as
    
  timeout number := 60;  -- timeout in seconds

  /* Below is the URL from the Direct Download for Automated Systems.  Just copy the link
     and paste the text.
  */
  v_url varchar2(1024) :=
  'http://www.federalreserve.gov/datadownload/Output.aspx?rel=H10&series=122e3bcb627e8e53f1bf72a1a09cfb81&lastObs=10&from=&to=&filetype=csv&label=include&layout=seriescolumn&type=package';
   
  v_line_count number := 0;
  v_line_in varchar2(1024);
  v_header varchar2(1024);

  /* UTL_HTTP.REQ is set by the BEGIN_REQUEST function.  REQ includes 
     the URL, method, and http_version.
  */
  req   UTL_HTTP.REQ;

  /* UTL_HTTP.RESP is set by the BEGIN_RESPONSE function.  RESP includes   
     the status, a brief message, and the http version.
  */
  resp  UTL_HTTP.RESP;
    
begin
    
  utl_http.set_transfer_timeout(timeout);         -- set the timeout before opening the request
  req := utl_http.begin_request(v_url);           -- Pass our URL, get a req object back
  
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

  resp := utl_http.get_response(req);             -- Pass our req object, get a resp object back
  utl_http.read_line(resp, v_header, true);       -- Read line of resp into v_header

  dbms_output.put_line('CSV Header = ' || v_header);
  v_line_count := v_line_count + 1;

  loop

    utl_http.read_line(resp, v_line_in, true);    -- csv data
    v_line_count := v_line_count + 1;
    dbms_output.put_line('CSV Data = ' || v_line_in);

  end loop;

exception
  when utl_http.end_of_body then                  -- end of the data
      dbms_output.put_line('Lines read, including header = ' || v_line_count );
      util_http.end_response(resp);               -- close the response object

  when others then
    dbms_output.put_line('Unhandled exception, lines = ' || v_line_count  );
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
 
    -- Do not leave anything open.  Close everything and ignore errors.
    begin
       utl_http.end_response(resp);
       utl_http.end_request(req);
    exception
       when others then null;
    end;
    
end simple_web_client;

Now we execute our client using SQLPLUS or SQL/Developer. If we connect properly, our output will look something like this:

anonymous block completed
CSV Header = "Series Description","Nominal Broad Dollar Index ","Nominal Major Currencies Dollar Index ","Nominal Other Important Trading Partners Dollar Index "
CSV Data = "Unit:","Index:_1973_Mar_100","Index:_1973_Mar_100","Index:_1997_Jan_100"
CSV Data = "Multiplier:","1","1","1"
CSV Data = "Currency:","NA","NA","NA"
CSV Data = "Unique Identifier: ","H10/H10/JRXWTFB_N.B","H10/H10/JRXWTFN_N.B","H10/H10/JRXWTFO_N.B"
CSV Data = "Time Period","JRXWTFB_N.B","JRXWTFN_N.B","JRXWTFO_N.B"
CSV Data = 2013-10-14,ND,ND,ND
CSV Data = 2013-10-15,101.1463,75.7635,128.2686
CSV Data = 2013-10-16,101.0694,75.7839,128.0691
CSV Data = 2013-10-17,100.4167,74.9717,127.6647
CSV Data = 2013-10-18,100.3842,74.8882,127.7015
CSV Data = 2013-10-21,100.6396,75.0142,128.1114
CSV Data = 2013-10-22,100.3595,74.7093,127.8816
CSV Data = 2013-10-23,100.4550,74.8150,127.9575
CSV Data = 2013-10-24,100.5485,74.8527,128.1189
CSV Data = 2013-10-25,100.5134,74.9733,127.8807
Lines read, including header = 16

The Oracle utl_http packages makes reading data from a webserver as simple as reading data from a file. With a simple web client, we can read any data that's available from a web server. Our web service example used CSV-formatted data, but we could download XML data as easily. And for a real hands-free operation, schedule your client using DBMS_SCHEDULE or DBMS_JOB.

There's just one caveat when using the utl_http functions: depending on your DBAs and your installation, you may not have access to use the network functions. If you get an error like

ORA-24247: network access denied by access control list (ACL) 

then it's time to visit the DBAs and request that they add your schema or your user account to the network ACL.