| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
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 to set up an Oracle wallet. Non-HTTPS fetches do not require an Oracle wallet.
See Also:
|
This chapter contains the following topics:
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.
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));
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 the UTL_HTTP package. You should not modify the fields.
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));
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 prefix private_. The fields are private and are intended for use by implementation of the UTL_HTTP package. You should not modify the fields.
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 156-5 shows the fields for the COOKIE and COOKIE_TABLE record types.
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".
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. 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.
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;
The UTL_HTTP package provides access to the HTTP protocol. The interfaces must be called in the order shown in Figure 156-1, or an exception will be raised.
Text description of the illustration arpls006.gif
The following can be called at any time:
UTL_HTTP package in the current session
UTL_HTTP package in the current session
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.
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.
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.
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.
The UTL_HTTP package provides subprograms to manipulate HTTP cookies.
The UTL_HTTP package provides subprograms to manipulate persistent connections.
The UTL_HTTP package provides subprograms to retrieve error information.
Table 156-6 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).
For REQUEST and REQUEST_PIECES, the request_failed exception is raised when any exception occurs and detailed_exception is disabled.
The following examples demonstrate how to use 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;
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;
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;
CREATE TABLE my_cookies ( session_id INTEGER, name VARCHAR2(256), value VARCHAR2(1024), domain VARCHAR2(256), expire DATE, path VARCHAR2(1024), secure VARCHAR2(1), version INTEGER ); CREATE SEQUENCE session_id; SET serveroutput ON SIZE 40000 REM Retrieve cookies from UTL_HTTP CREATE OR REPLACE FUNCTION save_cookies RETURN PLS_INTEGER AS cookies utl_http.cookie_table; my_session_id PLS_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 values (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 PLS_INTEGER) AS cookies utl_http.cookie_table; cookie utl_http.cookie; i PLS_INTEGER := 0; CURSOR c (c_session_id PLS_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; /
| Subprogram | Description |
|---|---|
|
Begins a new HTTP request. | |
|
Ends the HTTP request. | |
|
Sets an HTTP request header. The request header is sent to the Web server as soon as it is set. | |
|
Sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request. | |
|
Sets the character set of the request body when the media type is | |
|
Enables or disables support for the HTTP cookies in the request. | |
|
Sets the maximum number of times | |
|
Enables or disables support for the HTTP 1.1 persistent-connection in the request. | |
|
Writes a text line in the HTTP request body and ends the line with new-line characters (CRLF as defined in | |
|
Writes some binary data in the HTTP request body. | |
|
Writes some text data in the HTTP request body. |
| Subprogram | Description |
|---|---|
|
Retrieves the detailed SQLCODE of the last exception raised. | |
|
Retrieves the detailed SQLERRM of the last exception raised. |
| Subprogram | Description |
|---|---|
|
Adds the cookies maintained by | |
|
Begins a new HTTP request. | |
|
Clears all cookies maintained by the UTL_HTTP package (see HTTP Cookies and HTTP Cookies Subprograms) | |
|
Closes an HTTP persistent connection maintained by the UTL_HTTP package in the current database session (see HTTP Persistent Connections and HTTP Persistent Connections Subprograms) | |
|
Closes a group of HTTP persistent connections maintained by the UTL_HTTP package in the current database session (see HTTP Persistent Connections and HTTP Persistent Connections Subprograms) | |
|
Ends the HTTP request (see HTTP Requests and HTTP Requests Subprograms) | |
|
Ends the HTTP response. It completes the HTTP request and response (see HTTP Responses and HTTP Responses Subprograms) | |
|
Retrieves the HTTP authentication information needed for the request to be accepted by the Web server as indicated in the HTTP response header (see HTTP Responses and HTTP Responses Subprograms) | |
|
Retrieves the default character set of the body of all future HTTP requests (see Session Settings and Session Settings Subprograms) | |
|
Returns the number of cookies currently maintained by the UTL_HTTP package set by all Web servers (see HTTP Cookies and HTTP Cookies Subprograms) | |
|
Retrieves the current cookie support settings (see Session Settings and Session Settings Subprograms) | |
|
Returns all the cookies currently maintained by the UTL_HTTP package set by all Web servers (see HTTP Cookies and HTTP Cookies Subprograms) | |
|
Checks if the | |
|
Retrieves the detailed SQLCODE of the last exception raised (see Error Conditions and Error Conditions Subprograms) | |
|
Retrieves the detailed SQLERRM of the last exception raised (see Error Conditions and Error Conditions Subprograms) | |
|
Retrieves the follow-redirect setting in the current session (see Session Settings and Session Settings Subprograms) | |
|
Returns the nth HTTP response header name and value returned in the response (see HTTP Responses and HTTP Responses Subprograms) | |
|
Returns the HTTP response header value returned in the response given the name of the header (see HTTP Responses and HTTP Responses Subprograms) | |
|
Returns the number of HTTP response headers returned in the response (see HTTP Responses and HTTP Responses Subprograms) | |
|
Returns the number of network connections currently kept persistent by the UTL_HTTP package to the Web servers (see HTTP Persistent Connections and HTTP Persistent Connections Subprograms) | |
|
Sees whether or not future HTTP requests will support the HTTP 1.1 persistent connection; sets the maximum number of persistent connections maintained in the current database user session (see Session Settings and Session Settings Subprograms) | |
|
Checks if the persistent connection support is enabled and gets the maximum number of persistent connections in the current session (see Session Settings and Session Settings Subprograms) | |
|
Returns all the network connections currently kept persistent by the UTL_HTTP package to the Web servers (see HTTP Persistent Connections and HTTP Persistent Connections Subprograms) | |
|
Retrieves the current proxy settings (see Session Settings and Session Settings Subprograms) | |
|
Reads the HTTP response. When the function returns, the status line and the HTTP response headers have been read and processed (see HTTP Responses and HTTP Responses Subprograms) | |
|
Checks if the response error check is set or no (see Session Settings and Session Settings Subprograms)t | |
|
Retrieves the current network transfer timeout value (see Session Settings and Session Settings Subprograms) | |
|
Reads the HTTP response body in text form until the end of line is reached and returns the output in the caller-supplied buffer (see HTTP Responses and HTTP Responses Subprograms) | |
|
Reads the HTTP response body in binary form and returns the output in the caller-supplied buffer (see HTTP Responses and HTTP Responses Subprograms) | |
|
Reads the HTTP response body in text form and returns the output in the caller-supplied buffer (see HTTP Responses and HTTP Responses Subprograms) | |
|
Returns up to the first 2000 bytes of the data retrieved from the given URL. This function can be used directly in SQL queries (see Simple HTTP Fetches and Simple HTTP Fetches in a Single Call Subprograms) | |
|
Returns a PL/SQL table of 2000-byte pieces of the data retrieved from the given URL (see Simple HTTP Fetches and Simple HTTP Fetches in a Single Call Subprograms) | |
|
Sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request (see HTTP Requests and HTTP Requests Subprograms) | |
|
Sets the default character set of the body of all future HTTP requests when the media type is | |
|
Sets the character set of the request body when the media type is | |
|
Sets the character set of the response body when the media type is "text" but the character set is not specified in the "Content-Type" header (see HTTP Responses, HTTP Responses Subprograms, Session Settings and Session Settings Subprograms) | |
|
Enables or disables support for the HTTP cookies in the request (see HTTP Requests and HTTP Requests Subprograms) | |
|
Sets whether or not future HTTP requests will support HTTP cookies; sets the maximum number of cookies maintained in the current database user session (see Session Settings and Session Settings Subprograms) | |
|
Sets the | |
|
Sets the maximum number of times | |
|
Sets the maximum number of times | |
|
Sets an HTTP request header. The request header is sent to the Web server as soon as it is set (see HTTP Requests and HTTP Requests Subprograms) | |
|
Enables or disables support for the HTTP 1.1 persistent-connection in the request (see HTTP Requests and HTTP Requests Subprograms) | |
|
Sets the proxy to be used for requests of HTTP or other protocols (see Session Settings and Session Settings Subprograms) | |
|
Sets whether or not | |
|
Sets the timeout value for | |
|
Sets the Oracle Wallet used for all HTTP requests over Secured Socket Layer (SSL), that is, HTTPS (see Session Settings and Session Settings Subprograms) | |
|
Writes a text line in the HTTP request body and ends the line with new-line characters (CRLF as defined in UTL_TCP) (see HTTP Requests and HTTP Requests Subprograms) | |
|
Writes some binary data in the HTTP request body (see HTTP Requests and HTTP Requests Subprograms) | |
|
Writes some text data in the HTTP request body (see HTTP Requests and HTTP Requests Subprograms) |
This procedure adds the cookies maintained by UTL_HTTP.
UTL_HTTP.ADD_COOKIES ( cookies IN cookie_table);
| Parameter | Description |
|---|---|
|
|
The cookies to be added. |
The cookies that the package currently maintains are not cleared before new cookies are added.
This functions begins a new HTTP request. UTL_HTTP establishes the network connection to the target Web server or the proxy server and sends the HTTP request line. The PL/SQL program continues the request by calling some other interface to complete the request. The URL may contain the username and password needed to authenticate the request to the server. The format is
scheme://[user[:password]@]host[:port]/[...]
UTL_HTTP.BEGIN_REQUEST ( url IN VARCHAR2, method IN VARCHAR2 DEFAULT 'GET', http_version IN VARCHAR2 DEFAULT NULL) RETURN req;
The URL passed as an argument to this function is not examined for illegal characters, such as spaces, according to URL specification RFC 2396. You should escape those characters with the UTL_URL package to return illegal and reserved characters. URLs should consist of US-ASCII characters only. See Chapter 166, "UTL_URL" for a list of legal characters in URLs. Note that URLs should consist of US-ASCII characters only. The use of non-US-ASCII characters in a URL is generally unsafe.
An Oracle wallet must be set before accessing Web servers over HTTPS. See the set_wallet procedure on how to set up an Oracle wallet.
This procedure clears all cookies maintained by the UTL_HTTP package.
UTL_HTTP.CLEAR_COOKIES;
This procedure closes an HTTP persistent connection maintained by the UTL_HTTP package in the current database session.
UTL_HTTP.CLOSE_PERSISTENT_CONN ( conn IN connection);