Rem
Rem utlhttp.sql
Rem
Rem  Copyright (c) Oracle Corporation 1996, 1997, 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      utlhttp.sql - http requests from stored programs
Rem
Rem    DESCRIPTION
Rem     Package contains plsql covers for WRBX library
Rem     to make http requests from stored programs.
Rem
Rem    NOTES
Rem      See WRB ICX (Web Request Broker, Inter Cartridge eXchange) doc.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jmuller     09/16/99 - Fix bug 708690: final pass
Rem    rpang       09/03/99 - Should set wallet before making request
Rem    rpang       08/04/99 - Extended API to support HTTPS
Rem    jmuller     05/28/99 - Fix bug 708690: TAB -> blank
Rem    rdecker     08/20/98 - add proxy param to request calls
Rem    jmuller     08/21/98 - Fix bug 500845: add documentation on proxies     
Rem    gclossma    04/25/97 - raw => number for ptr_t
Rem    gclossma    01/30/97 - fix documentation
Rem    gclossma    01/28/97 - add fetch_more_data
Rem    gclossma    12/11/96 - add grant to public
Rem    gclossma    12/10/96 - for 7.3.3
Rem    gclossma    08/28/96 - utl_SQurL => utl_http
Rem    gclossma    08/19/96 - SQurL http callouts
Rem    gclossma    08/19/96 - Created
Rem

/* 
Package UTL_HTTP, for making HTTP (hyper-text transfer protocol) callouts
from PL/SQL and SQL.  It can be used to access data on the internet, or to call
Oracle Web Server Cartridges.  The package contains two similar entrypoints,
each of which takes a string URL (universal resource locator), contacts that
site, and returns the data (typically HTML -- hyper-text markup language)
obtained from that site.

This is the specification of packaged function UTL_HTTP.REQUEST:

        function request (url in varchar2, proxy in varchar2,
                          wallet_path in varchar2, wallet_password in varchar2) 
          return varchar2;

UTL_HTTP.REQUEST returns up to the first 2000 bytes of the data retrieved
from the given URL.  For example:

        SVRMGR> select utl_http.request('http://www.oracle.com/') from dual;
        UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/')                         
        -------------------------------------------------------------------
        <html>
        <head><title>Oracle Corporation Home Page</title>
        <!--changed Jan. 16, 19
        1 row selected.

The optional proxy parameter to request() can be used to specify a proxy 
server to use when making the HTTP request.

The optional wallet_path and wallet_password parameters to request() can be
used to specify a client-side wallet.  The client-side wall contains
the list of trusted certificate authorities required for HTTPS request.  The
format of wallet_path is 'file:/<local-dir-for-client-side-wallet>'.
wallet_password is the password required to open the wallet.

This is the specification of packaged function UTL_HTTP.REQUEST_PIECES,
which uses type UTL_HTTP.HTML_PIECES:

  type html_pieces is table of varchar2(2000) index by binary_integer;

  function request_pieces (url in varchar2, 
                           max_pieces natural default 32767,
                           proxy in varchar2 default NULL,
                           wallet_path in varchar2, wallet_password in varchar2)
        return html_pieces;

UTL_HTTP.REQUEST_PIECES returns a PLSQL-table of 2000-byte pieces of the 
data retrieved from the given URL.  The optional second argument places 
a bound on the number of pieces retrieved.  For example, the following
block retrieves up to 100 pieces of data (each 2000-bytes, except perhaps 
the last) from the URL.  It prints the number of pieces retrieved, and
the total length, in bytes, of the data retrieved.  

        set serveroutput on
        /
        declare 
          x utl_http.html_pieces;
        begin
          x := utl_http.request_pieces('http://www.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 dbms_output.put_line
                ((2000 * (x.count - 1)) + length(x(x.count)));
          end if;
        end;
        /

Here is the output:

        Statement processed.
        4 pieces were retrieved.
        with total length 
        7687

The optional proxy parameter to request_pieces() can be used to specify a 
proxy server to use when making the HTTP request.

The optional wallet_path and wallet_password parameters to request() can be
used to specify a client-side wallet.  The client-side wall contains
the list of trusted certificate authorities required for HTTPS request.  The
format of wallet_path is 'file:/<local-dir-for-client-side-wallet>'.
wallet_password is the password required to open the wallet.

Below is the specification for package UTL_HTTP.  It describes the 
exceptions that can be raised by functions REQUEST and REQUEST_PIECES:
*/

create or replace package utl_http is

  -- Package UTL_HTTP contains functions REQUEST and REQUEST_PIECES for
  -- making HTTP callouts from PLSQL programs.  

  -- Function REQUEST takes a URL as its argument.  Its return-type is a
  -- string of length 2000 or less, which contains up to the first 2000 bytes
  -- of the html result returned from the HTTP request to the argument URL.

  function request (url in varchar2, proxy in varchar2 default NULL,
                    wallet_path in varchar2 default NULL,
                    wallet_password in varchar2 default NULL) 
        return varchar2;
  pragma restrict_references (request, wnds, rnds, wnps, rnps);

  -- Function REQUEST_PIECES also takes a URL as its argument.  Its
  -- return-type is a PLSQL-table of type UTL_HTTP.HTML_PIECES.  Each 
  -- element of that PLSQL-table is a string of length  2000. The 
  -- final element may be shorter than 2000 characters. 

  type html_pieces is table of varchar2(2000) index by binary_integer;

  function request_pieces (url in varchar2, 
                        max_pieces natural default 32767,
                        proxy in varchar2 default NULL,
                        wallet_path in varchar2 default NULL,
                        wallet_password in varchar2 default NULL)
        return html_pieces;
  pragma restrict_references (request_pieces, wnds, rnds, wnps, rnps);

  -- The elements of the PLSQL-table returned by REQUEST_PIECES are 
  -- successive pieces of the data obtained from the HTTP request to that 
  -- URL.  Here is a typical URL:
  --            http://www.oracle.com
  -- So a call to REQUEST_PIECES could look like the example below. Note the 
  -- use of the plsql-table method COUNT to discover the number of pieces 
  -- returned, which may be zero or more:
  --
  -- declare pieces utl_http.html_pieces;
  -- begin 
  --   pieces := utl_http.request_pieces('http://www.oracle.com/'); 
  --   for i in 1 .. pieces.count loop
  --     .... -- process each piece
  --   end loop;
  -- end;
  --

  -- The second argument to REQUEST_PIECES, "MAX_PIECES", is optional.  It is 
  -- the maximum number of pieces (each 2000 characters in length, except for 
  -- the last, which may be shorter), that REQUEST_PIECES should return.  If 
  -- provided, that argument should be a positive integer. 

  -- Exceptional conditions:

  -- If initialization of the http-callout subsystem fails (for 
  -- environmental reasons, for example, lack of available memory)
  -- then exception UTL_HTTP.INIT_FAILED is raised:

  init_failed exception;

  -- When the HTTP call fails (e.g., because of failure of the HTTP daemon;
  -- or because of the argument to REQUEST  or REQUEST_PIECES cannot be 
  -- interpreted as a URL because it is NULL or has non-HTTP syntax) then 
  -- exception UTL_HTTP.REQUEST_FAILED is raised.  

  request_failed exception;

  -- Note that the above two exceptions, unless explicitly caught by an
  -- exception handler, will be reported by this generic message:
  --    ORA-06510: PL/SQL: unhandled user-defined exception
  -- which reports them as "user-defined" exceptions, although 
  -- they are defined in this system package.

  -- If any other exception is raised during the processing of the http 
  -- request (for example, an out-of-memory error), then function REQUEST 
  -- or REQUEST_PIECES reraises that exception.

  -- When no response is received from a request to the given URL
  -- (for example, because no site corresponding to that URL is contacted)
  -- then a formatted html error message may be returned.  For example:
  --  
  --  <HTML>
  --  <HEAD>
  --  <TITLE>Error Message</TITLE>
  --  </HEAD>
  --  <BODY>
  --  <H1>Fatal Error 500</H1>
  --  Can't Access Document:  http://home.nothing.comm.
  --  <P>
  --  <B>Reason:</B> Can't locate remote host:  home.nothing.comm.
  --  <P>
  --   
  --  <P><HR>
  --  <ADDRESS><A HREF="http://www.w3.org">
  --   CERN-HTTPD3.0A</A></ADDRESS>
  --  </BODY>
  --  </HTML>
  --  

  -- You should not expect for UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES
  -- to succeed in contacting a URL unless you can contact that URL by using 
  -- a browser on the same machine (and with the same privileges, environment 
  -- variables, etc.)  If REQUEST or REQUEST_PIECES fails (i.e., if it raises 
  -- an exception, or returns a HTML-formatted error message, yet you believe 
  -- that the URL argument is correct), please try contacting that same URL 
  -- with a browser, to verify network availability from your machine.
  -- Keep in mind that you may have a proxy server set in your browser that
  -- need to be set with each utl_http request call using the optional proxy
  -- parameter.

  -- A note on proxies: this package can also use environment variables to 
  -- specify its proxy behavior.  
  -- For example, on Unix, setting the environment variable
  -- 'http_proxy' to a URL specifies to use that service as the proxy server for
  -- HTTP requests.  Setting the environment variable 'no_proxy' to a domain
  -- name specifies to not use the HTTP proxy server for URL's in that domain.
end utl_http;
/
create or replace package body utl_http is

  subtype opaque is number;

  -- return codes from webserver icx entrypoints in ndwsi.c.
  NDWICX_FAIL constant binary_integer := 0;
  NDWICX_SUCC constant binary_integer := 1;
  NDWICX_MOREDATA constant binary_integer := 2;

--------------------------------------------------------
  -- PIHTTINIT: Initialization of icx for single call.
  -- Creates the "context" struct
  function pihttinit return opaque;
  pragma interface (c, pihttinit, "pihttinit");

--------------------------------------------------------
  -- PIHTTCLNP: Cleanup of icx at termination of call.
  -- Destroys the "context", releases all memory allocated by icx.
  procedure pihttclnp (context opaque);
  pragma interface (c, pihttclnp, "pihttclnp");

--------------------------------------------------------
  -- CREATE_REQUEST 
  function pihttcr_create_request 
        (context opaque, url in varchar2)
        return opaque;
  pragma interface (c, pihttcr_create_request, "pihttcr_create_request");

--------------------------------------------------------
  -- MAKE_REQUEST 
  function pihttmr_make_request
        (context opaque, 
        req opaque, piece out varchar2,
        piecesize in binary_integer) return binary_integer;
  pragma interface (c, pihttmr_make_request, "pihttmr_make_request");

--------------------------------------------------------
  -- FETCH_MORE_DATA 
  function pihttfm_fetch_more
        (context opaque, 
        req opaque, piece out varchar2,
        piecesize in binary_integer) return binary_integer;
  pragma interface (c, pihttfm_fetch_more, "pihttfm_fetch_more");

--------------------------------------------------------
  -- DESTROY_REQUEST 
  procedure pihttdr_destroy_request 
        (context opaque, request opaque);
  pragma interface (c, pihttdr_destroy_request, "pihttdr_destroy_request");

-------------------------------
  -- SET_PROXY
  function pihttsp_set_proxy 
        (session_context opaque, proxy varchar2) return binary_integer;
  pragma interface (c, pihttsp_set_proxy, "pihttsp_set_proxy");

-------------------------------
  -- SET_WALLET
  function pihttsw_set_wallet 
        (session_context opaque, req opaque, wallet_path varchar2,
         wallet_pwd varchar2) return binary_integer;
  pragma interface (c, pihttsw_set_wallet, "pihttsw_set_wallet");

-------------------------

  /* Returns HTML result from HTTP request to <url>, 
     as a PLSQL-table of varchar2(2000) pieces.
     Returns at most <max_pieces> pieces. */
  function request_pieces (url in varchar2, 
                           max_pieces natural default 32767,
                           proxy in varchar2 default NULL,
                           wallet_path in varchar2 default NULL,
                           wallet_password in varchar2)
        return html_pieces is
    ctx opaque;
    i binary_integer;
    req opaque;
    rc binary_integer;
    piece varchar2(2000); 
    pieces html_pieces;
  begin
    if url is null then raise request_failed; end if;

    ctx := pihttinit; -- pointer to ndwsictx struct
    if ctx is null then raise init_failed; end if;

    if proxy is not null
    then 
      rc := pihttsp_set_proxy(ctx, proxy);
      if NDWICX_FAIL = rc then raise request_failed; end if;
    end if;

    req := pihttcr_create_request(ctx, url);
    if req is null then raise request_failed; end if;

    if wallet_path is not null
    then
      rc := pihttsw_set_wallet(ctx, req, wallet_path, wallet_password);
      if NDWICX_FAIL = rc then raise request_failed; end if;
    end if;

    rc := NDWICX_SUCC; -- assumption
    if 0 < max_pieces 
    then
      rc := pihttmr_make_request(ctx, req, piece, 2000);
      if NDWICX_FAIL = rc then raise request_failed; end if;
      pieces(1) := piece; 
      i := 2;
      while NDWICX_MOREDATA = rc and i <= max_pieces loop
        rc := pihttfm_fetch_more(ctx, req, piece, 2000);
        pieces(i) := piece;
        i := i + 1;
      end loop;
    end if;

    if NDWICX_FAIL = rc then raise request_failed; end if;

    pihttdr_destroy_request(ctx, req);
    pihttclnp(ctx);
    
    return pieces;

  exception when others then
    if req is not null then pihttdr_destroy_request(ctx, req); end if;
    if ctx is not null then pihttclnp(ctx); end if;
    raise;
  end request_pieces;

  -- Returns up to the first 2000 bytes of HTML result 
  -- from HTTP request to <url>. 
  function request (url in varchar2, proxy in varchar2 default NULL,
                    wallet_path in varchar2 default NULL,
                    wallet_password in varchar2) 
        return varchar2 is
    ctx opaque;
    i binary_integer;
    req opaque;
    rc binary_integer;
    piece varchar2(2000); 
  begin
    if url is null then raise request_failed; end if;

    ctx := pihttinit; -- pointer to ndwsictx struct
    if ctx is null then raise init_failed; end if;


    if proxy is not null
    then 
      rc := pihttsp_set_proxy(ctx, proxy);
      if NDWICX_FAIL = rc then raise request_failed; end if;
    end if;

    req := pihttcr_create_request(ctx, url);
    if req is null then raise request_failed; end if;

    if wallet_path is not null
    then
      rc := pihttsw_set_wallet(ctx, req, wallet_path, wallet_password);
      if NDWICX_FAIL = rc then raise request_failed; end if;
    end if;

    rc := pihttmr_make_request(ctx, req, piece, 2000);

    if NDWICX_FAIL = rc then raise request_failed; end if;

    pihttdr_destroy_request(ctx, req);
    pihttclnp(ctx);
    
    return piece;

  exception when others then
    if req is not null then pihttdr_destroy_request(ctx, req); end if;
    if ctx is not null then pihttclnp(ctx); end if;
    raise;
  end request;

end utl_http;
/
grant execute on utl_http to public;
/
create public synonym utl_http for sys.utl_http;
/
