ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. Can be used to access data on the Internet over the HTTP protocol.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
HTTP protocol versions that can be used in the function begin_request
HTTP_VERSION_1_0
VARCHAR2(64)
HTTP/1.0
HTTP_VERSION_1_1
VARCHAR2(64)
HTTP/1.1
Default TCP/IP port numbers that a HTTP server listens
DEFAULT_HTTP_PORT
PLS_INTEGER
80
DEFAULT_HTTPS_PORT
PLS_INTEGER
443
Status codes of a HTTP response as defined in HTTP 1.1
HTTP_CONTINUE
PLS_INTEGER
100
HTTP_SWITCHING_PROTOCOLS
PLS_INTEGER
101
HTTP_OK
PLS_INTEGER
200
HTTP_CREATED
PLS_INTEGER
201
HTTP_ACCEPTED
PLS_INTEGER
202
HTTP_NON_AUTHORITATIVE_INFO
PLS_INTEGER
203
HTTP_NO_CONTENT
PLS_INTEGER
204
HTTP_RESET_CONTENT
PLS_INTEGER
205
HTTP_PARTIAL_CONTENT
PLS_INTEGER
206
HTTP_MULTIPLE_CHOICES
PLS_INTEGER
300
HTTP_MOVED_PERMANENTLY
PLS_INTEGER
301
HTTP_FOUND
PLS_INTEGER
302
HTTP_SEE_OTHER
PLS_INTEGER
303
HTTP_NOT_MODIFIED
PLS_INTEGER
304
HTTP_USE_PROXY
PLS_INTEGER
305
HTTP_TEMPORARY_REDIRECT
PLS_INTEGER
307
HTTP_BAD_REQUEST
PLS_INTEGER
400
HTTP_UNAUTHORIZED
PLS_INTEGER
401
HTTP_PAYMENT_REQUIRED
PLS_INTEGER
402
HTTP_FORBIDDEN
PLS_INTEGER
403
HTTP_NOT_FOUND
PLS_INTEGER
404
HTTP_NOT_ACCEPTABLE
PLS_INTEGER
406
HTTP_PROXY_AUTH_REQUIRED
PLS_INTEGER
407
HTTP_REQUEST_TIME_OUT
PLS_INTEGER
408
HTTP_CONFLICT
PLS_INTEGER
409
HTTP_GONE
PLS_INTEGER
410
HTTP_LENGTH_REQUIRED
PLS_INTEGER
411
HTTP_PRECONDITION_FAILED
PLS_INTEGER
412
HTTP_REQUEST_ENTITY_TOO_LARGE
PLS_INTEGER
413
HTTP_REQUEST_URI_TOO_LARGE
PLS_INTEGER
414
HTTP_UNSUPPORTED_MEDIA_TYPE
PLS_INTEGER
415
HTTP_REQ_RANGE_NOT_SATISFIABLE
PLS_INTEGER
416
HTTP_EXPECTATION_FAILED
PLS_INTEGER
417
HTTP_NOT_IMPLEMENTED
PLS_INTEGER
501
HTTP_BAD_GATEWAY
PLS_INTEGER
502
HTTP_SERVICE_UNAVAILABLE
PLS_INTEGER
503
HTTP_GATEWAY_TIME_OUT
PLS_INTEGER
504
HTTP_VERSION_NOT_SUPPORTED
PLS_INTEGER
505
Data Types
-- 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
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;
-- A PL/SQL record type that represents a HTTP cookie
TYPE cookie IS RECORD (
name VARCHAR2(256), -- cookie name
value VARCHAR2(1024), -- cookie value
domain VARCHAR2(256), -- domain for which the cookie applies
expire TIMESTAMP WITH TIME ZONE -- when should the cookie expire?
path VARCHAR2(1024), -- virtual path for which the cookie applies
secure BOOLEAN, -- transfer cookies by HTTPS only
version PLS_INTEGER, -- cookie specification version
comment VARCHAR2(1024)); -- comments about this cookie
-- PL/SQL table of cookies
TYPE cookie_table IS TABLE OF cookie INDEX BY BINARY_INTEGER;
-- VARCHAR2 table for returning HTML from request_pieces
TYPE html_pieces IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
-- A PL/SQL record type that represents a HTTP request
TYPE req IS RECORD (
url VARCHAR2(32767 byte), -- Requested URL
method VARCHAR2(64), -- Requested method
http_version VARCHAR2(64), -- Requested HTTP version
private_hndl PLS_INTEGER); -- For internal use only
-- PL/SQL record type that represents a HTTP response
TYPE resp IS RECORD (
status_code PLS_INTEGER, -- Response status code
reason_phrase VARCHAR2(256), -- Response reason phrase
http_version VARCHAR2(64), -- Response HTTP version
private_hndl PLS_INTEGER); -- For internal use only
/* Note: The "private_xxxx" field(s) in the req and resp record types are for internal use only and users should not try to modify them the HTTP information returned in the req and resp from the API begin_request and get_response are for read only.
Changing the field values in the records has no effect to request or reesponse when making calls to the API in this package. */
Dependencies
DBMS_AQADM_SYS
ORDX_HTTP_SOURCE
SPARQL_SERVICE_IMPL_T
DBMS_AQELM
PLITBLM
URIFACTORY
DBMS_GSM_POOLADMIN
RDFCTX_WS_EXTRACTOR
UTL_HTTP
DBMS_ISCHED
SDO_OLS
UTL_HTT_LIB
DBMS_PRVTAQIP
SDO_RDF
UTL_RAW
DRVDML
SEM_RDFCTX_DR
UTL_URL
HTTPURITYPE
SEM_RDFCTX_IR
Documented
Yes
Exceptions
Error Code
Reason
ORA-24247
network_access_denied: Network ACL not assigned
ORA-29261
bad_argument: A bad argument was passed to an API
ORA-29262
bad_url: The URL is bad
ORA-29263
protocol_error: A HTTP protocol error occurred
ORA-29264
unknown_scheme: The scheme of the URL is unknown
ORA-29265
header_not_found: The HTTP header is not found
ORA-29266
end_of_body: The end of response body is reached
ORA-29267
illegal_call: The API call is illegal at this stage
ORA-29268
http_client_error: A 4xx response code is returned
ORA-29269
http_server_error: A 5xx response code is returned
ORA-29270
too_many_requests: Too many open requests or responses
ORA-29272
init_failed: The UTL_HTTP pkg initialization failed
ORA-29273
request_failed: The HTTP request failed
ORA-29275
partial_multibyte_char: A partial multi-byte character found
ORA-29276
transfer_timeout: Transfer time-out occurred
First Available
7.3.4
Security Model
Owned by SYS with EXECUTE granted to PUBLIC and ORDPLUGINS. The Library recommends that the grant to PUBLIC be revoked immediately following installation as it poses a significant security risk.
Also be sure that you have created a Network Access Control list restricting or eliminating network access by this package.
utl_http.add_cookies(
cookies IN cookie_table,
request_context IN request_context_key DEFAULT NULL);
CREATE OR REPLACE PROCEDURE restore_cookies(this_session_id IN BINARY_INTEGER) AUTHID DEFINER AS
cookies utl_http.cookie_table;
cookie utl_http.cookie;
i PLS_INTEGER := 0;
CURSOR 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 restore_cookies;
/
Begins a new HTTP request. When the function returns, the UTL_HTTP package has established the network connection to the target Web server,
or the proxy server if a proxy server is to be used, and has sent the HTTP request line. The PL/SQL program should continue the request by calling some other API to complete the request.
utl_http.begin_request(
url IN VARCHAR2,
method IN VARCHAR2 DEFAULT 'GET',
http_version IN VARCHAR2 DEFAULT NULL,
request_context IN request_context_key DEFAULT NULL))
RETURN req;
set serveroutput on
DECLARE
req utl_http.req;
resp utl_http.resp;
value VARCHAR2(1024);
BEGIN
req := utl_http.begin_request('https://www.morganslibrary.org');
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;
/
Closes a group of HTTP persistent connections maintained by the UTL_HTTP package in the current database session. This procedure uses a pattern-match approach to decide which persistent connections to close.
utl_http.close_persistent_conns(
host IN VARCHAR2 DEFAULT NULL,
port IN PLS_INTEGER DEFAULT NULL,
proxy_host IN VARCHAR2 DEFAULT NULL,
proxy_port IN PLS_INTEGER DEFAULT NULL,
ssl IN BOOLEAN DEFAULT NULL);
Ends the HTTP response completing the HTTP request and response. Unless a HTTP 1.1 persistent connection is used in this request, the network connection is closed.
utl_http.end_response(r IN OUT NOCOPY resp);
See BEGIN_REQUEST Demo Above and the SET_AUTHENTICATION Demo Below
--================================ alt.
set serveroutput on
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);
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;
/
Retrieves the default content-encoding support for the specified encoding scheme for all future HTTP requests
utl_http.get_content_encoding_support(
scheme IN VARCHAR2,
enable OUT BOOLEAN DEFAULT FALSE);
DECLARE
b BOOLEAN;
BEGIN
utl_http.set_content_encoding_support('GZIP', FALSE);
IF b THEN
dbms_output.put_line('GZIP Encoding Is Enabled');
ELSE
dbms_output.put_line('GZIP Encoding Is Not Enabled');
END IF;
END;
/
Returns the all the cookies currently maintained by the UTL_HTTP package set by all Web servers
utl_http.get_cookies(
cookies IN OUT NOCOPY cookie_table,
request_context IN request_context_key DEFAULT NULL);
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;
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;
Checks if the UTL_HTTP package will raise a detailed exception
utl_http.get_detailed_excp_support(enable OUT BOOLEAN);
set serveroutput on
DECLARE
x BOOLEAN;
BEGIN
IF utl_http.get_detailed_excp_support THEN
dbms_output.put_line('Enabled');
ELSE
dbms_output.put_line('Disabled');
END IF;
END;
/
Reads the HTTP response and processes the status line and response headers. The status code, reason phrase and the HTTP protocol version are stored in the response record
utl_http.get_response(
r IN OUT NOCOPY req,
return_info_response IN BOOLEAN DEFAULT FALSE)
RETURN resp;
See BEGIN_REQUEST Demo Above and the SET_AUTHENTICATION Demo Below
Reads the HTTP response body in binary form and returns the output in the caller-supplied buffer. The end_of_body exception is raised if the end of the HTTP response body is reached.
utl_http.read_raw(
r IN OUT NOCOPY resp,
data OUT NOCOPY RAW,
len IN PLS_INTEGER DEFAULT NULL);
utl_http.read_raw_ptr(
r IN OUT NOCOPY resp,
data_ptr IN RAW,
len IN PLS_INTEGER DEFAULT NULL,
read_len OUT NOCOPY PLS_INTEGER);
PRAGMA restrict_references(read_raw_ptr, wnds, rnds, trust);
Fetches a Web page. The page is returned in a PL/SQL-table of VARCHAR2(2000) pieces.
The elements of the PLSQL-table returned by request_pieces are successive pieces of the data obtained from the HTTP request to that URL.
utl_http.request_pieces(
url IN VARCHAR2,
max_pieces IN NATURAL DEFAULT 32767,
proxy IN VARCHAR2 DEFAULT NULL,
wallet_path IN VARCHAR2 DEFAULT NULL,
wallet_password IN VARCHAR2 DEFAULT NULL)
RETURN html_pieces;
set serveroutput on
DECLARE
x utl_http.html_pieces;
len PLS_INTEGER;
BEGIN
x := utl_http.request_pieces('https://www.morganslibrary.org/', 100);
dbms_output.put_line(x.count || ' pieces were retrieved.');
dbms_output.put_line('with total length ');
len := 0;
FOR i IN 1..x.COUNT
LOOP
len := len + length(x(i));
END LOOP;
dbms_output.put_line(len);
END;
/
Sets the HTTP authentication information in the HTTP request header needed for the request to be authorized by the Web server
utl_http.set_authentication(
r IN OUT NOCOPY req,
username IN VARCHAR2,
password IN VARCHAR2 DEFAULT NULL,
scheme IN
VARCHAR2 DEFAULT 'Basic', -- the alternative value is "Amazon S3"
for_proxy IN BOOLEAN DEFAULT FALSE);
set serveroutput on
DECLARE
req utl_http.req;
resp utl_http.resp;
name VARCHAR2(255);
value VARCHAR2(1023);
v_msg VARCHAR2(80);
v_url VARCHAR2(32767) := '/';
BEGIN
-- request that exceptions are raised for error status codes
utl_http.set_response_error_check(enable => TRUE);
-- allow testing for exceptions like Utl_Http.Http_Server_Error
utl_http.set_detailed_excp_support(enable => TRUE);
FOR i IN 1..utl_http.get_header_count(r => resp)
LOOP
utl_http.get_header(r=>resp, n=>i, name=>name, value=>value);
dbms_output.put_line(name || ': ' || value);
END LOOP;
BEGIN
LOOP
utl_http.read_text(r => resp, data => v_msg);
dbms_output.put_line(v_msg);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
utl_http.end_response(r => resp);
EXCEPTION
WHEN utl_http.request_failed THEN
dbms_output.put_line('Request Failed: ' || utl_http.get_detailed_sqlerrm);
WHEN utl_http.http_server_error THEN
dbms_output.put_line('Server Error: ' || utl_http.get_detailed_sqlerrm);
WHEN utl_http.http_client_error THEN
dbms_output.put_line('Client Error: ' || htl_http.get_detailed_sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
Sets the HTTP authentication information in the HTTP request header needed for the request to be authorized by the Web server using the username and password credential stored in the Oracle wallet
utl_http.set_authentication_from_wallet(
r IN OUT NOCOPY req,
alias IN VARCHAR2,
scheme IN VARCHAR2 DEFAULT 'Basic',
for_proxy IN BOOLEAN DEFAULT FALSE);
Sets the default character set of the body of all future HTTP requests when the media type is "text" but the character set is not specified in the "Content-Type" header
Overload 1
utl_http.set_body_charset(charset IN VARCHAR2 DEFAULT NULL);
TBD
Sets the character set of the request body when the media type is "text" but the character set is not specified in the "Content-Type" header
Overload 2
utl_http.set_body_charset(
r IN OUT NOCOPY req,
charset IN VARCHAR2 DEFAULT NULL);
TBD
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
Overload 3
utl_http.set_body_charset(
r IN OUT NOCOPY resp,
charset IN VARCHAR2 DEFAULT NULL);
Enables or disables support for the HTTP cookies in the request
Overload 1
utl_http.set_cookie_support(
r IN OUT NOCOPY req,
enable IN BOOLEAN DEFAULT TRUE);
TBD
Sets whether future HTTP requests will support HTTP cookies, and the maximum number of cookies maintained in the current database user session
Overload 2
utl_http.set_cookie_support(
enable IN BOOLEAN,
max_cookies IN PLS_INTEGER DEFAULT 300,
max_cookies_per_site IN PLS_INTEGER DEFAULT 20);
Sets the maximum number of times the UTL_HTTP package should follow HTTP redirect instruction in the HTTP responses to requests in the function get_response.
Overload 1
utl_http.set_follow_redirect(max_redirects IN PLS_INTEGER DEFAULT 3);
TBD
Overload 2
utl_http.set_follow_redirect(
r IN OUT NOCOPY req,
max_redirects IN PLS_INTEGER DEFAULT 3);
Sets whether future HTTP requests should support the HTTP 1.1 persistent-connection or not, and the maximum numbers of persistent connections to be maintained in the current database user session.
Overload 1
utl_http.set_persistent_conn_support(
enable IN BOOLEAN,
max_conns IN PLS_INTEGER DEFAULT 0);
DECLARE
TYPE vc2_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
paths vc2_table;
PROCEDURE fetch_pages(paths IN vc2_table) AS
url_prefix VARCHAR2(256) := 'https://www.morganslibrary.org/';
req utl_http.req;
resp utl_http.resp;
data VARCHAR2(1024);
BEGIN
FOR i IN 1..paths.count LOOP
req := utl_http.begin_request(url_prefix || paths(i));
-- use persistent connection except for the last request
IF (i < paths.count) THEN
utl_http.set_persistent_conn_support(req, TRUE);
END IF;
resp := utl_http.get_response(req);
BEGIN
LOOP
utl_http.read_text(resp, data);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
utl_http.end_response(resp);
END LOOP;
END fetch_pages
Enables (or disables) the support for the HTTP 1.1 persistent-connection in this request
Overload 2
utl_http.set_persistent_conn_support(
r IN OUT NOCOPY req,
enable IN BOOLEAN DEFAULT FALSE);
DECLARE
TYPE vc2_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
paths vc2_table;
PROCEDURE fetch_pages(paths IN vc2_table) AS
url_prefix VARCHAR2(256) := 'https://www.morganslibrary.org/';
req utl_http.req;
resp utl_http.resp;
data VARCHAR2(1024);
pcn BOOLEAN := TRUE;
BEGIN
FOR i IN 1..paths.count
LOOP
req := utl_http.begin_request(url_prefix || paths(i));
-- use persistent connection except for the last request
IF (i < paths.count) THEN
utl_http.set_persistent_conn_support(req, pcn);
END IF;
resp := utl_http.get_response(req);
BEGIN
LOOP
utl_http.read_text(resp, data);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
utl_http.end_response(resp);
END LOOP;
END;
Writes text data in the HTTP request body. As soon as some data is sent as the HTTP request body, the HTTP request headers section is completed.
Text data is automatically converted from the database character set to the request body character set.
utl_http.write_text(
r IN OUT NOCOPY req,
data IN VARCHAR2 CHARACTER SET ANY_CS);
DECLARE
data VARCHAR2(1024) := '...';
req utl_http.req;
resp utl_http.resp;
BEGIN
req := utl_http.begin_request('https://www.morganslibrary.org/about', 'POST');
utl_http.set_header(req, 'Content-Length', length(data));
-- ask HTTP server to return "100 Continue" response
utl_http.set_header(req, 'Expect', '100-continue');
resp := utl_http.get_response(req, TRUE);
-- check for and dispose "100 Continue" response
IF (resp.status_code <> 100) THEN
utl_http.end_response(resp);
raise_application_error(20000, 'Request rejected');
END IF;
utl_http.end_response(resp);
-- now, send the request body
utl_http.write_text(req, data);
-- get the regular response
resp := utl_http.get_response(req);
utl_http.read_text(resp, data);