From Oracle
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.
When the package fetches data from a Web site using HTTPS, it requires Oracle Wallet Manager which can be created by either Oracle Wallet Manager or the orapki utility. Non-HTTPS fetches do not require an Oracle wallet.
See Also:
-
Oracle Database Advanced Security Administrator's Guide for more information on Wallet Manager
This chapter contains the following topics:
-
-
Overview
-
Security Model
-
Constants
-
Datatypes
-
Operational Notes
-
Exceptions
-
Examples
-
-
-
Session Settings Subprograms
-
HTTP Requests Subprograms
-
HTTP Request Contexts Subprograms
-
HTTP Responses Subprograms
-
HTTP Cookies Subprograms
-
HTTP Persistent Connections Subprograms
-
Error Conditions Subprograms
-
Using UTL_HTTP
This section contains topics which relate to using the UTL_HTTP
package.
Overview
With the UTL_HTTP
package, you can write PL/SQL programs that communicate with Web (HTTP) servers. And UTL_HTTP
contains a function that can be used in SQL queries.
The package supports HTTP over the Secured Socket Layer protocol (SSL), also known as HTTPS. It also supports SSL client authentication by sending the client-certificate in a wallet to authenticate with the remote Web server.
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.
Security Model
This package is an invoker's rights package and the invoking user will need the connect
privilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as the use-client-certificates
or the use-passwords
privilege to authenticate himself with the remote Web server using the credentials stored in an Oracle wallet.
Note:
For more information, see Managing Fine-grained Access to External Network Services in Oracle Database Security GuideConstants
The UTL_HTTP
package uses the constants shown in following tables.
Table 225-1 UTL_HTTP Constants - HTTP Versions
Name | Type | Value | Description |
---|---|---|---|
|
|
|
Denotes HTTP version 1.0 that can be used in the function |
|
|
|
Denotes HTTP version 1.1 that can be used in the function |
Table 225-2 UTL_HTTP Constants - Default Ports
Name | Type | Value | Description |
---|---|---|---|
|
|
|
The default TCP/IP port (80) at which a Web server or proxy server listens |
|
|
|
The default TCP/IP port (443) at which an HTTPS Web server listens |
Table 225-3 UTL_HTTP Constants - HTTP 1.1 Status Codes
Name | Type | Value | Description |
---|---|---|---|
|
|
|
The client should continue with its request. This interim response is used to inform the client that the initial part of the request has been received and has not yet been rejected by the server. |
|
|
|
The server understands and is willing to comply with the client's request, through the Upgrade message header field, for a change in the application protocol being used on this connection. The server will switch protocols to those defined by the response's Upgrade header field immediately after the empty line which terminates the 101 response. |
|
|
|
The request has succeeded. The information returned with the response is dependent on the method used in the request |
|
|
|
The request has been fulfilled and resulted in a new resource being created. |
|
|
|
The request has been accepted for processing, but the processing has not been completed. The request might or might not eventually be acted upon, as it might be disallowed when processing actually takes place. |
|
|
|
The returned metainformation in the entity-header is not the definitive set as available from the origin server, but is gathered from a local or a third-party copy. |
|
|
|
The server has fulfilled the request but does not need to return an entity-body, and might want to return updated metainformation. |
|
|
|
The server has fulfilled the request and the user agent should reset the document view which caused the request to be sent. The response must not include an entity. |
|
|
|
The server has fulfilled the partial GET request for the resource. |
|
|
|
The requested resource corresponds to any one of a set of representations, each with its own specific location, and agent- driven negotiation information is being provided so that the user (or user agent) can select a preferred representation and redirect its request to that location. |
|
|
|
The requested resource has been assigned a new permanent URI and any future references to this resource should use one of the returned URIs. |
|
|
|
The requested resource resides temporarily under a different URI. |
|
|
|
The response to the request can be found under a different URI and should be retrieved using a GET method on that resource. |
|
|
|
If the client has performed a conditional GET request and access is allowed, but the document has not been modified, the server responds with this status code. |
|
|
|
The requested resource must be accessed through the proxy given by the Location field. The Location field gives the URI of the proxy. |
|
|
|
The requested resource resides temporarily under a different URI. |
|
|
|
The request could not be understood by the server due to malformed syntax. |
|
|
|
The request requires user authentication. The client may repeat the request with a suitable Authorization header field. If the request already included Authorization credentials, then the 401 response indicates that authorization has been refused for those credentials. |
|
|
|
This code is reserved for future use. |
|
|
|
The server understood the request, but is refusing to fulfill it. |
|
|
|
The server has not found anything matching the Request-URI. |
|
|
|
The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request. |
|
|
|
This code is similar to 401 (Unauthorized), but indicates that the client must first authenticate itself with the proxy. |
|
|
|
The client did not produce a request within the time that the server was prepared to wait. |
|
|
|
The request could not be completed due to a conflict with the current state of the resource. |
|
|
|
The requested resource is no longer available at the server and no forwarding address is known. |
|
|
|
The server refuses to accept the request without a defined |
|
|
|
The precondition given in one or more of the request-header fields evaluated to false when it was tested on the server. |
|
|
|
The server is refusing to process a request because the request entity is larger than the server is willing or able to process. |
|
|
|
The server is refusing to service the request because the Request-URI is longer than the server is willing to interpret. |
|
|
|
The server is refusing to service the request because the entity of the request is in a format not supported by the requested resource for the requested method. |
|
|
|
A server returns a response with this status code if a request included a Range request-header field, and none of the range-specifier values in this field overlap the current extent of the selected resource, and the request did not include an If-Range request-header field. |
|
|
|
The expectation given in an Expect request-header field could not be met by this server, or, if the server is a proxy, the server has unambiguous evidence that the request could not be met by the next-hop server. |
|
|
|
The server does not support the functionality required to fulfill the request. |
|
|
|
The server, while acting as a gateway or proxy, received an invalid response from the upstream server it accessed in attempting to fulfill the request |
|
|
|
The server is currently unable to handle the request due to a temporary overloading or maintenance of the server. |
|
|
|
The server, while acting as a gateway or proxy, did not receive a timely response from the upstream server specified by the URI (for example, HTTP, FTP, LDAP) or some other auxiliary server (for example, DNS) it needed to access in attempting to complete the request. |
|
|
|
The server does not support, or refuses to support, the HTTP protocol version that was used in the request message. |
Datatypes
REQ Type
Use this PL/SQL record type to represent an HTTP request.
TYPE req IS RECORD ( url VARCHAR2(32767), method VARCHAR2(64), http_version VARCHAR2(64));
Table 225-4 REQ Type Parameters
Parameter | Description |
---|---|
|
The URL of the HTTP request. It is set after the request is created by |
|
The method to be performed on the resource identified by the URL. It is set after the request is created by |
|
The HTTP protocol version used to send the request. It is set after the request is created by |
The information returned in REQ
from the interface 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 theUTL_HTTP
package. You should not modify the fields.
REQUEST_CONTEXT_KEY Type
This type is used to represent the key to a request context. A request context is a context that holds a private wallet and cookie table to make a HTTP request. This private wallet and cookie table, unlike the session-wide ones maintained in the package, will not be shared with other HTTP requests within the database session.
SUBTYPE request_context_key IS PLS_INTEGER;
To provide enhanced security, UTL_HTTP
allows PL/SQL programs to create request contexts. A request context is a private context that holds a wallet and a cookie table that will not be shared with other programs in the same database session when making HTTP requests and receiving HTTP responses. PL/SQL programs should use request contexts when they need to use wallets or cookies that contain sensitive information such as authentication credentials.
RESP Type
This PL/SQL record type is used to represent an HTTP response.
TYPE resp IS RECORD ( status_code PLS_INTEGER, reason_phrase VARCHAR2(256), http_version VARCHAR2(64));
Table 225-5 RESP Type Parameters
Parameter | Description |
---|---|
|
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 |
|
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 |
|
The HTTP protocol version used in the HTTP response. It is set after the response is processed by |
The information returned in RESP
from the interface GET_RESPONSE
is read-only. There are other fields in the RESP
record type whose names begin with the prefixprivate_.
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.
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;
Table 225-6 shows the fields for the COOKIE
and COOKIE_TABLE
record types.
Table 225-6 Fields of COOKIE and COOKIE_TABLE Type
Field | Description |
---|---|
|
The name of the HTTP cookie |
|
The value of the cookie |
|
The domain for which the cookie is valid |
|
The time by which the cookie will expire |
|
The subset of URLs to which the cookie applies |
|
Should the cookie be returned to the Web server using secured means only. |
|
The version of the HTTP cookie specification the cookie conforms. This field is |
|
The comment that describes the intended use of the cookie. This field is |
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 "Retrieving and Restoring Cookies".
CONNECTION Type
Use the 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. Theproxy_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, theproxy_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.
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;
Operational Notes
Operational Flow
The UTL_HTTP
package provides access to the HTTP protocol. The interfaces must be called in the order shown in Figure 225-1, or an exception will be raised.
Figure 225-1 Flow of the Core UTL_HTTP Package
Description of "Figure 225-1 Flow of the Core UTL_HTTP Package"
The following can be called at any time:
-
Non-protocol interfaces 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
-
-
Interfaces 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
-
-
Interfaces 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 interfaces bear the same name as the interface that manipulates the attributes and configurations of the package in the current session. They are overloaded versions of the interface that manipulate a request or a response.
-
Simple HTTP Fetches
REQUEST
and REQUEST_PIECES
take a string uniform resource locator (URL), contact that site, and return the data (typically HTML) obtained from that site.
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, and so on.)
If REQUEST
or REQUEST_PIECES
fails (for example, if it raises an exception, or if it returns an HTML-formatted error message, but you believe that the URL argument is correct), then try contacting that same URL with a browser to verify network availability from your machine. 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 uses that service as the proxy server for HTTP requests. Setting the environment variable
no_proxy
to a domain name does not use the HTTP proxy server for URLs in that domain. When the
UTL_HTTP
package is executed in the Oracle database server, the environment variables are the ones that are set when the database instance is started.
HTTP Requests
The HTTP Requests group of subprograms begin an HTTP request, manipulate attributes, and send the request information to the Web server. When a request is created, it inherits the default settings of the HTTP cookie support, follow-redirect, body character set, persistent-connection support, and transfer timeout of the current session. The settings can be changed by calling the request interface.
See Also:
HTTP Requests SubprogramsHTTP Responses
The HTTP Responses group of subprograms manipulate an HTTP response obtained from GET_RESPONSE and receive response information from the Web server. When a response is created for a request, it inherits settings of the HTTP cookie support, follow-redirect, body character set, persistent-connection support, and transfer timeout from the request. Only the body character set can be changed by calling the response interface.
See Also:
HTTP Responses SubprogramsHTTP Cookies
The UTL_HTTP
package provides subprograms to manipulate HTTP cookies.
See Also:
HTTP Cookies SubprogramsHTTP Persistent Connections
The UTL_HTTP
package provides subprograms to manipulate persistent connections.
See Also:
HTTP Persistent Connections SubprogramsError Conditions
The UTL_HTTP
package provides subprograms to retrieve error information.
See Also:
Error Conditions SubprogramsSession Settings
Session settings manipulate the configuration and default behavior of UTL_HTTP
when HTTP requests are executed within a database user session. When a request is created, it inherits the default settings of the HTTP cookie support, follow-redirect, body character set, persistent-connection support, and transfer timeout of the current session. Those settings can be changed later by calling the request interface. When a response is created for a request, it inherits those settings from the request. Only the body character set can be changed later by calling the response interface.
See Also:
Session Settings SubprogramsRequest Context
The UTL_HTTP
package maintains a common wallet and cookie table within the database session that all HTTP requests and responses share. This makes it easy for users to share the wallet or to maintain application state in the cookies within the session. However, if an application stores private information in the wallet or in the cookies that it does not want to share with other applications in the same database session, it may define a request context to hold its own wallet and cookie table and use this request context to make HTTP requests.
See Also:
HTTP Requests SubprogramsExternal Password Store
The UTL_HTTP
package allows HTTP password credentials to be stored in an Oracle wallet's external password store. The external password store provides an easy but secure storage for passwords and frees the application developers from the need to maintain their own storage.
See Also:
SET_AUTHENTICATION_FROM_WALLET ProcedureExceptions
Table 225-7 lists the exceptions that the UTL_HTTP
package interface 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 225-7 UTL_HTTP Exceptions
Exception | Error Code | Reason | Where Raised |
---|---|---|---|
|
|
The argument passed to the interface is bad |
Any HTTP request or response interface when |
|
|
The requested URL is badly formed |
|
|
|
The end of HTTP response body is reached |
|
|
|
The header is not found |
|
|
|
From |
|
|
|
From |
|
|
|
Access to the remote network host or credentials in an Oracle wallet is denied |
|
|
|
The call to |
|
|
|
No complete character is read and a partial multibyte character is found at the end of the response body |
|
|
|
An HTTP protocol error occurs when communicating with the Web server |
|
|
|
The request fails to executes |
Any HTTP request or response interface when |
|
|
Too many requests or responses are open |
|
|
|
No data is read and a read timeout occurred |
|
|
|
The scheme of the requested URL is unknown |
|
NOTE:
Thepartial_multibyte_char
and
transfer_timeout
exceptions are duplicates of the same exceptions defined in
UTL_TCP.
They are defined in this package so that the use of this package does not require the knowledge of the
UTL_TCP
. As those exceptions are duplicates, an exception handle that catches the
partial_multibyte_char
and
transfer_timeout
exceptions in this package also catch the exceptions in the
UTL_TCP.
For REQUEST
and REQUEST_PIECES
, the request_failed
exception is raised when any exception occurs and detailed_exception
is disabled.
Examples
The following examples demonstrate how to use UTL_HTTP
.
General Usage
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;