58
UTL_HTTP

UTL_HTTP makes hyper-text transfer protocol (HTTP) callouts from PL/SQL and SQL. You can use it to access data on the Internet or to call Oracle Web Server cartridges.

UTL_HTTP contains two similar entrypoints: REQUEST and REQUEST_PIECES. They each take a string universal resource locator (URL), contact that site, and return the data (typically HTML -- hyper-text markup language) obtained from that site.

Exceptions

Table 58-1 UTL_HTTP Package Exceptions

Exception	Description
INIT_FAILED	Initialization of the HTTP-callout subsystem fails (for environmental reasons, such as lack of available memory).
REQUEST_FAILED	The HTTP call fails (for example, because of failure of the HTTP daemon, or because the argument to `REQUEST` or `REQUEST_PIECES` cannot be interpreted as a URL because it is `NULL` or has non-HTTP syntax).

The above two exceptions, unless explicitly caught by an exception handler, are reported by this generic message: ORA-06510: PL/SQL: unhandled user-defined exception. This 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>

Usage Notes

You should not expect REQUEST or 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 (for example, if it raises an exception, or if it returns an HTML-formatted error message, yet you believe that the URL argument is correct), then try contacting that same URL with a browser, to verify network availability from your machine. Remember that you may have a proxy server set in your browser that needs to be set with each REQUEST or REQUEST_PIECES call using the optional proxy parameter.

Note:
UTL_HTTP 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.

Summary of Subprograms

Table 58-2 UTL_HTTP Package Subprograms

Subprogram	Description
REQUEST function	Returns up to the first 2000 bytes of the data retrieved from the given URL.
REQUEST_PIECES function	Returns a PL/SQL-table of 2000-byte pieces of the data retrieved from the given URL.

REQUEST function

This function returns up to the first 2000 bytes of the data retrieved from the given URL.

Syntax

UTL_HTTP.REQUEST (
   url   IN VARCHAR2,
   proxy IN VARCHAR2 DEFAULT NULL) 
  RETURN VARCHAR2;

Pragmas

pragma restrict_references (request, wnds, rnds, wnps, rnps);

Parameters

Table 58-3 REQUEST Function Parameters

Parameter	Description
url	Universal resource locator.
proxy	(Optional) Specifies a proxy server to use when making the HTTP request.

Returns

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.

Exceptions

INIT_FAILED
REQUEST_FAILED

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.

If you are behind a firewall, include the proxy parameter. For example, from within the Oracle firewall, where there might be a proxy server named www-proxy.us.oracle.com:

SVRMGR> SELECT 
utl_http.request('http://www.oracle.com', 'www-proxy.us.oracle.com') FROM dual;

REQUEST_PIECES function

This function returns a PL/SQL table of 2000-byte pieces of the data retrieved from the given URL.

Syntax

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

UTL_HTTP.REQUEST_PIECES (
   url        IN VARCHAR2, 
   max_pieces NATURAL     DEFAULT 32767,
   proxy      IN VARCHAR2 DEFAULT NULL)
  RETURN HTML_PIECES;

Pragmas

pragma restrict_references (request_pieces, wnds, rnds, wnps, rnps);

Parameters

Table 58-4 REQUEST_PIECES Function Parameters

Parameter	Description
url	Universal resource locator.
max_pieces	(Optional) 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, then that argument should be a positive integer.
proxy	(Optional) Specifies a proxy server to use when making the HTTP request.

Returns

REQUEST_PIECES returns a PL/SQL table of type UTL_HTTP.HTML_PIECES. Each element of that PL/SQL table is a string of length 2000. The final element may be shorter than 2000 characters.

The elements of the PL/SQL table returned by REQUEST_PIECES are successive pieces of the data obtained from the HTTP request to that URL.

Exceptions

INIT_FAILED
REQUEST_FAILED

Example

A call to REQUEST_PIECES could look like the example below. Note the use of the PL/SQL 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;

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;
/
-- Output
Statement processed.
4 pieces were retrieved.
with total length 
7687

58UTL_HTTP

Exceptions

Table 58-1 UTL_HTTP Package Exceptions

Usage Notes

Summary of Subprograms

Table 58-2 UTL_HTTP Package Subprograms

REQUEST function

Syntax

Pragmas

Parameters

Table 58-3 REQUEST Function Parameters

Returns

Exceptions

Example

REQUEST_PIECES function

Syntax

Pragmas

Parameters

Table 58-4 REQUEST_PIECES Function Parameters

Returns

Exceptions

Example

Example

58
UTL_HTTP