78
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.

The package contains a set of APIs that enables users to write PL/SQL programs that communicate with Web (HTTP) servers. UTL_HTTP also contains a function that can be used in SQL queries. Besides HTTP, it also supports HTTP over the Secured Socket Layer protocol (SSL), also known as HTTPS, directly or via 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.

See Also: Chapter 84, "UTL_URL" Chapter 82, "UTL_SMTP" Oracle Advanced Security Administrator's Guide for more information on Wallet Manager

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 78-1 lists the defined constants for UTL_HTTP.

Table 78-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 78-2 shows the parameters for the REQ type.

Table 78-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 78-3 shows the parameters for the RESP type.

Table 78-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 78-4 shows the fields for the COOKIE and COOKIE_TABLE record types.

Table 78-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;