UTL_HTTP , 2 of 2

Summary of Subprograms

Table 61-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 data retrieved from the given URL.

Syntax

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

Pragmas

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

Parameters

Table 61-3 shows the parameters for the REQUEST function.

Table 61-3 REQUEST Function Parameters

Parameter	Description
url	Universal resource locator.
`proxy`	(Optional) Specifies a proxy server to use when making the HTTP request.
`wallet_path`	(Optional) Specifies a client-side wallet. The client-side wallet 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`	(Optional) Specifies the password required to open the wallet.

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

SQLPLUS> 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:

SQLPLUS> 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)
   wallet_path
   wallet_password
  RETURN HTML_PIECES;

Pragmas

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

Parameters

Table 61-4 shows the parameters for the REQUEST function.

Table 61-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.
`wallet_path`	(Optional) Specifies a client-side wallet. The client-side wallet 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`	(Optional) Specifies the password required to open the wallet.

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