96
UTL_HTTP

The UTL_HTTP package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. You can use it to access data on the Internet over HTTP.

With UTL_HTTP, you can write PL/SQL programs that communicate with Web (HTTP) servers. UTL_HTTP also contains a function that can be used in SQL queries. The package also supports HTTP over the Secured Socket Layer protocol (SSL), also known as HTTPS, directly or through an HTTP proxy. Other Internet-related data-access protocols (such as the File Transfer Protocol (FTP) or the Gopher protocol) are also supported using an HTTP proxy server that supports those protocols.

When the package fetches data from a Web site using HTTPS, it requires Oracle Wallet Manager to set up an Oracle wallet. Non-HTTPS fetches do not require an Oracle wallet.

This chapter discusses the following topics:

• UTL_HTTP Constants, Types and Flow
• UTL_HTTP Exceptions
• UTL_HTTP Examples
• Summary of UTL_HTTP Subprograms

UTL_HTTP Constants, Types and Flow

UTL_HTTP Constants

Table 96-1 UTL_HTTP Constants

Constant and Syntax	Purpose
HTTP_VERSION_1_0 CONSTANT VARCHAR2(10) := 'HTTP/1.0';	Denotes HTTP version 1.0 that can be used in the function begin_request.
HTTP_VERSION_1 CONSTANT VARCHAR2(10) := 'HTTP/1.1';	Denotes HTTP version 1.1 that can be used in the function begin_request.
DEFAULT_HTTP_PORT CONSTANT PLS_INTEGER := 80;	The default TCP/IP port (80) at which a Web server or proxy server listens
DEFAULT_HTTPS_PORT CONSTANT PLS_INTEGER := 443;	The default TCP/IP port (443) at which an HTTPS Web server listens
The following denote all the HTTP 1.1 status codes:
HTTP_CONTINUE CONSTANT PLS_INTEGER := 100;	-
HTTP_SWITCHING_PROTOCOLS CONSTANT PLS_INTEGER := 101;
HTTP_OK CONSTANT PLS_INTEGER := 200;	-
HTTP_CREATED CONSTANT PLS_INTEGER := 201;	-
HTTP_ACCEPTED CONSTANT PLS_INTEGER := 202;	-
HTTP_NON_AUTHORITATIVE_INFO CONSTANT PLS_INTEGER := 203;
HTTP_NO_CONTENT CONSTANT PLS_INTEGER := 204;	-
HTTP_RESET_CONTENT CONSTANT PLS_INTEGER := 205;	-
HTTP_PARTIAL_CONTENT CONSTANT PLS_INTEGER := 206;	-
HTTP_MULTIPLE_CHOICES CONSTANT PLS_INTEGER := 300;	-
HTTP_MOVED_PERMANENTLY CONSTANT PLS_INTEGER := 301;
HTTP_FOUND CONSTANT PLS_INTEGER := 302;	-
HTTP_SEE_OTHER CONSTANT PLS_INTEGER := 303;	-
HTTP_NOT_MODIFIED CONSTANT PLS_INTEGER := 304;	-
HTTP_USE_PROXY CONSTANT PLS_INTEGER := 305;	-
HTTP_TEMPORARY_REDIRECT CONSTANT PLS_INTEGER := 307;
HTTP_BAD_REQUEST CONSTANT PLS_INTEGER := 400;	-
HTTP_UNAUTHORIZED CONSTANT PLS_INTEGER := 401;	-
HTTP_PAYMENT_REQUIRED CONSTANT PLS_INTEGER := 402;	-
HTTP_FORBIDDEN CONSTANT PLS_INTEGER := 403;	-
HTTP_NOT_FOUND CONSTANT PLS_INTEGER := 404;	-
HTTP_NOT_ACCEPTABLE CONSTANT PLS_INTEGER := 406;	-
HTTP_PROXY_AUTH_REQUIRED CONSTANT PLS_INTEGER := 407;
HTTP_REQUEST_TIME_OUT CONSTANT PLS_INTEGER := 408;	-
HTTP_CONFLICT CONSTANT PLS_INTEGER := 409;	-
HTTP_GONE CONSTANT PLS_INTEGER := 410;	-
HTTP_LENGTH_REQUIRED CONSTANT PLS_INTEGER := 411;	-
HTTP_PRECONDITION_FAILED CONSTANT PLS_INTEGER := 412;
HTTP_REQUEST_ENTITY_TOO_LARGE CONSTANT PLS_INTEGER := 413;
HTTP_REQUEST_URI_TOO_LARGE CONSTANT PLS_INTEGER := 414;
HTTP_UNSUPPORTED_MEDIA_TYPE CONSTANT PLS_INTEGER := 415;
HTTP_REQ_RANGE_NOT_SATISFIABLE CONSTANT PLS_INTEGER := 416;
HTTP_EXPECTATION_FAILED CONSTANT PLS_INTEGER := 417;
HTTP_NOT_IMPLEMENTED CONSTANT PLS_INTEGER := 501;	-
HTTP_BAD_GATEWAY CONSTANT PLS_INTEGER := 502;	-
HTTP_SERVICE_UNAVAILABLE CONSTANT PLS_INTEGER := 503;
HTTP_GATEWAY_TIME_OUT CONSTANT PLS_INTEGER := 504;	-
HTTP_VERSION_NOT_SUPPORTED CONSTANT PLS_INTEGER := 505;-

UTL_HTTP Types

Use the following types with UTL_HTTP.

REQ Type

Use this PL/SQL record type to represent an HTTP request.

Syntax

TYPE req IS RECORD (
   url           VARCHAR2(32767),
   method        VARCHAR2(64),
   http_version  VARCHAR2(64),
);

Parameters

Table 96-2 REQ Type Parameters

Parameter	Description
url	The URL of the HTTP request. It is set after the request is created by begin_request.
method	The method to be performed on the resource identified by the URL. It is set after the request is created by begin_request.
http_version	The HTTP protocol version used to send the request. It is set after the request is created by begin_request.

Usage Notes

The information returned in REQ from the API begin_request is for read only. Changing the field values in the record has no effect on the request.

There are other fields in REQ record type whose names begin with the prefix private_. The fields are private and are intended for use by implementation of the UTL_HTTP package. You should not modify the fields.

RESP Type

This PL/SQL record type is used to represent an HTTP response.

Syntax

TYPE resp IS RECORD (
   status_code    PLS_INTEGER,
   reason_phrase  VARCHAR2(256),
   http_version   VARCHAR2(64),
);

Parameters

Table 96-3 RESP Type Parameters

Parameter	Description
status_code	The status code returned by the Web server. It is a 3-digit integer that indicates the results of the HTTP request as handled by the Web server. It is set after the response is processed by get_response.
reason_phrase	The short textual message returned by the Web server that describe the status code. It gives a brief description of the results of the HTTP request as handled by the Web server. It is set after the response is processed by get_response.
http_version	The HTTP protocol version used in the HTTP response. It is set after the response is processed by get_response.

Usage Notes

The information returned in RESP from the API get_response is read-only. There are other fields in the RESP record type whose names begin with the prefix private_. The fields are private and are intended for use by implementation of the UTL_HTTP package. You should not modify the fields.

COOKIE and COOKIE_TABLE Types

The COOKIE type is the PL/SQL record type that represents an HTTP cookie. The COOKIE_TABLE type is a PL/SQL index-by-table type that represents a collection of HTTP cookies.

Syntax

TYPE cookie IS RECORD (
   name  VARCHAR2(256),
   value  VARCHAR2(1024),
   domain  VARCHAR2(256),
   expire  TIMESTAMP WITH TIME ZONE,
   path  VARCHAR2(1024),
   secure  BOOLEAN,
   version  PLS_INTEGER,
   comment  VARCHAR2(1024)
);
TYPE cookie_table IS TABLE OF cookie INDEX BY binary_integer;

Fields of COOKIE Record Type

Table 96-4 shows the fields for the COOKIE and COOKIE_TABLE record types.

Table 96-4 Fields of COOKIE and COOKIE_TABLE Type

Field	Description
name	The name of the HTTP cookie
value	The value of the cookie
domain	The domain for which the cookie is valid
expire	The time by which the cookie will expire
path	The subset of URLs to which the cookie applies
secure	Should the cookie be returned to the Web server using secured means only.
version	The version of the HTTP cookie specification the cookie conforms. This field is NULL for Netscape cookies.
comment	The comment that describes the intended use of the cookie. This field is NULL for Netscape cookies.

Usage Notes

PL/SQL programs do not usually examine or change the cookie information stored in the UTL_HTTP package. The cookies are maintained by the package transparently. They are maintained inside the UTL_HTTP package, and they last for the duration of the database session only. PL/SQL applications that require cookies to be maintained beyond the lifetime of a database session can read the cookies using get_cookies, store them persistently in a database table, and re-store the cookies back in the package using add_cookies in the next database session. All the fields in the cookie record, except for the comment field, must be stored. Do not alter the cookie information, which can result in an application error in the Web server or compromise the security of the PL/SQL and the Web server applications. See "Example: Retrieving and Restoring Cookies".

CONNECTION Type

Use this PL/SQL record type to represent the remote hosts and TCP/IP ports of a network connection that is kept persistent after an HTTP request is completed, according to the HTTP 1.1 protocol specification. The persistent network connection may be reused by a subsequent HTTP request to the same host and port. The subsequent HTTP request may be completed faster because the network connection latency is avoided. connection_table is a PL/SQL table of connection.

For a direct HTTP persistent connection to a Web server, the host and port fields contain the host name and TCP/IP port number of the Web server. The proxy_host and proxy_port fields are not set. For an HTTP persistent connection that was previously used to connect to a Web server using a proxy, the proxy_host and proxy_port fields contain the host name and TCP/IP port number of the proxy server. The host and port fields are not set, which indicates that the persistent connection, while connected to a proxy server, is not bound to any particular target Web server. An HTTP persistent connection to a proxy server can be used to access any target Web server that is using a proxy.

The ssl field indicates if Secured Socket Layer (SSL) is being used in an HTTP persistent connection. An HTTPS request is an HTTP request made over SSL. For an HTTPS (SSL) persistent connection connected using a proxy, the host and port fields contain the host name and TCP/IP port number of the target HTTPS Web server and the fields will always be set. An HTTPS persistent connection to an HTTPS Web server using a proxy server can only be reused to make another request to the same target Web server.

Syntax

TYPE connection IS RECORD (
   host  VARCHAR2(256),
   port  PLS_INTEGER,
   proxy_host  VARCHAR2(256),
   proxy_port  PLS_INTEGER,
   ssl  BOOLEAN
);
TYPE connection_table IS TABLE OF connection INDEX BY BINARY_INTEGER;

UTL_HTTP Flow

The UTL_HTTP package provides access to the HTTP protocol. The API must be called in the order shown in Figure 96-1, or an exception will be raised.

Figure 96-1 Flow of the Core UTL_HTTP Package

Text description of the illustration arpls006.gif

The following can be called at any time:

• Non-protocol APIs that manipulate cookies
  • get_cookie_count
  • get_cookies
  • add_cookies
  • clear_cookies
• Persistent connections
  • get_persistent_conn_count
  • get_persistent_conns
  • close_persistent_conn
  • close_persistent_conns
• APIs that manipulate attributes and configurations of the UTL_HTTP package in the current session
  • set_proxy
  • get_proxy
  • set_cookie_support
  • get_cookie_support
  • set_follow_redirect
  • get_follow_redirect
  • set_body_charset
  • get_body_charset
  • set_persistent_conn_support
  • get_persistent_conn_support
  • set_detailed_excp_support
  • get_detailed_excp_support
  • set_wallet
  • set_transfer_timeout
  • get_transfer_timeout
• APIs that retrieve the last detailed exception code and message UTL_HTTP package in the current session
  • get_detailed_sqlcode
  • get_detailed_sqlerrm

    NOTE: Some of the request and response APIs bear the same name as the API that manipulates the attributes and configurations of the package in the current session. They are overloaded versions of the API that manipulate a request or a response.

UTL_HTTP Exceptions

Table 96-5 lists the exceptions that the UTL_HTTP package API can raise. By default, UTL_HTTP raises the exception request_failed when a request fails to execute. If the package is set to raise a detailed exception by set_detailed_excp_support, the rest of the exceptions will be raised directly (except for the exception end_of_body, which will be raised by read_text, read_line, and read_raw regardless of the setting).

Table 96-5 UTL_HTTP Exceptions

Exception	Error Code	Reason	Where Raised
request_failed	29273	The request fails to executes	Any HTTP request or response API when detailed_exception is disabled
bad_argument	29261	The argument passed to the API is bad	Any HTTP request or response API when detailed_exception is enabled
bad_url	29262	The requested URL is badly formed	begin_request, when detailed_exception is enabled
protocol_error	29263	An HTTP protocol error occurs when communicating with the Web server	set_header, get_response, read_raw, read_text, and read_line, when detailed_exception is enabled
unknown_scheme	29264	The scheme of the requested URL is unknown	begin_request and get_response, when detailed_exception is enabled
header_not_found	29265	The header is not found	get_header, get_header_by_name, when detailed_exception is enabled
end_of_body	29266	The end of HTTP response body is reached	read_raw, read_text, and read_line, when detailed_exception is enabled
illegal_call	29267	The call to UTL_HTTP is illegal at the current state of the HTTP request	set_header, set_authentication, and set_persistent_conn_support, when detailed_exception is enabled
http_client_error	29268	From get_response, the response status code indicates that a client error has occurred (status code in 4xx range). Or from begin_request, the HTTP proxy returns a status code in the 4xx range when making an HTTPS request through the proxy.	get_response, begin_request when detailed_exception is enabled
http_server_error	29269	From get_response, the response status code indicates that a client error has occurred (status code in 5xx range). Or from begin_request, the HTTP proxy returns a status code in the 5xx range when making an HTTPS request through the proxy.	get_response, begin_request when detailed_exception is enabled
too_many_requests	29270	Too many requests or responses are open	begin_request, when detailed_exception is enabled
partial_multibyte_exception	29275	No complete character is read and a partial multibyte character is found at the end of the response body	read_text and read_line, when detailed_exception is enabled
transfer_timeout	29276	No data is read and a read timeout occurred	read_text and read_line, when detailed_exception is enabled

For REQUEST and REQUEST_PIECES(), the request_failed exception is raised when any exception occurs and detailed_exception is disabled.

UTL_HTTP Examples

The following examples demonstrate how to use UTL_HTTP.

Example: Using UTL_HTTP

SET serveroutput ON SIZE 40000
  
DECLARE
  req   utl_http.req;
  resp  utl_http.resp;
  value VARCHAR2(1024);
BEGIN

  utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');

  req := utl_http.begin_request('http://www-hr.corp.my-company.com');
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  resp := utl_http.get_response(req);
  LOOP
    utl_http.read_line(resp, value, TRUE);
    dbms_output.put_line(value);
  END LOOP;
  utl_http.end_response(resp);
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
END;

Example: Retrieving HTTP Response Headers

SET serveroutput ON SIZE 40000
  
DECLARE
  req   utl_http.req;
  resp  utl_http.resp;
  name  VARCHAR2(256);
  value VARCHAR2(1024);
BEGIN

  utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');
 
  req := utl_http.begin_request('http://www-hr.corp.my-company.com');
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  resp := utl_http.get_response(req);

  dbms_output.put_line('HTTP response status code: ' || resp.status_code);
  dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);

  FOR i IN 1..utl_http.get_header_count(resp) LOOP
    utl_http.get_header(resp, i, name, value);
    dbms_output.put_line(name || ': ' || value);
  END LOOP;
  utl_http.end_response(resp);
END;

Example: Handling HTTP Authentication

SET serveroutput ON SIZE 40000
  
CREATE OR REPLACE PROCEDURE get_page (url      IN VARCHAR2,
                                      username IN VARCHAR2 DEFAULT NULL,
                                      password IN VARCHAR2 DEFAULT NULL,
                                      realm    IN VARCHAR2 DEFAULT NULL) AS
  req       utl_http.req;
  resp      utl_http.resp;
  my_scheme VARCHAR2(256);
  my_realm  VARCHAR2(256);
  my_proxy  BOOLEAN;
BEGIN

  -- Turn off checking of status code. We will check it by ourselves.
  utl_http.http_response_error_check(FALSE);

  req := utl_http.begin_request(url);
  IF (username IS NOT NULL) THEN
    utl_http.set_authentication(req, username, password); -- Use HTTP Basic 
Authen. Scheme
  END IF;

  resp := utl_http.get_response(req);
  IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN
    utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy);
    IF (my_proxy) THEN
       dbms_output.put_line('Web proxy server is protected.');
       dbms_output.put('Please supplied the required ' || my_scheme || ' 
authentication username/password for realm ' || my_realm || ' for the proxy 
server.');
    ELSE
      dbms_output.put_line('Web page ' || url || ' is protected.');
      dbms_output.put('Please supplied the required ' || my_scheme || ' 
authentication username/password for realm ' || my_realm || ' for the Web 
page.');
    END IF;
    utl_http.end_response(resp);
    RETURN;
  END IF;

  FOR i IN 1..utl_http.get_header_count(resp) LOOP
    utl_http.get_header(resp, i, name, value);
    dbms_output.put_line(name || ': ' || value);
  END LOOP;
  utl_http.end_response(resp);

END;

Example: Retrieving and Restoring Cookies

CREATE TABLE my_cookies (
    session_id  BINARY_INTEGER,
    name        VARCHAR2(256),
    value       VARCHAR2(1024),
    domain      VARCHAR2(256),
    expire      DATE,
    path        VARCHAR2(1024),
    secure      VARCHAR2(1),
    version     BINARY_INTEGER
);

CREATE SEQUENCE session_id;

SET serveroutput ON SIZE 40000

REM Retrieve cookies from UTL_HTTP

CREATE OR REPLACE FUNCTION save_cookies RETURN BINARY_INTEGER AS
  cookies        utl_http.cookie_table;
  my_session_id  BINARY_INTEGER;
  secure         VARCHAR2(1);
BEGIN

  /* assume that some cookies have been set in previous HTTP requests. */

  utl_http.get_cookies(cookies);
  select session_id.nextval into my_session_id from dual;

  FOR i in 1..cookies.count LOOP
    IF (cookies(i).secure) THEN
      secure := 'Y';
    ELSE
      secure := 'N';
    END IF;
    insert into my_cookies
    value (my_session_id, cookies(i).name, cookies(i).value, cookies(i).domain,
           cookies(i).expire, cookies(i).path, secure, cookies(i).version);
  END LOOP;

  RETURN my_session_id;

END;

REM Retrieve cookies from UTL_HTTP

CREATE OR REPLACE PROCEDURE restore_cookies (this_session_id IN BINARY_INTEGER) 
AS
  cookies        utl_http.cookie_table;
  cookie         utl_http.cookie;
  i              PLS_INTEGER := 0;
  CORSOR c (c_session_id BINARY_INTEGER) IS
    SELECT * FROM my_cookies WHERE session_id = c_session_id;
BEGIN

  FOR r IN c(this_session_id) LOOP
    i := i + 1;
    cookie.name   := r.name;
    cookie.value  := r.value;
    cookie.domain := r.domain;
    cookie.expire := r.expire;
    cookie.path   := r.path;
    IF (r.secure = 'Y') THEN
      cookie.secure := TRUE;
    ELSE
      cookie.secure := FALSE;
    END IF;
    cookie.version := r.version;
    cookies(i) := cookie;
  END LOOP;

  utl_http.clear_cookies;
  utl_http.add_cookies(cookies);

END;