Oracle UTL_HTTP Built-In Package
Versions 7.3.4 - 21c

Security Advisory
This package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. According to Oracle it can be used to access data on the Internet over the HTTP protocol. It can be used to access a lot more than "data on the Internet" when, as it is by default, EXECUTE is granted to PUBLIC.

We can do no better advising you about the risks of having this package available then to tell you about an event that actually happed in the 2000s in the US Pacific Northwest. The event happened as the result of two Oracle DBAs realizing that their database had internet connectivity, had the ability via the UTL_HTTP package to download website pages, and that there were terabytes of available space on their SAN. The rest is history.

Take a look at the code demo below for the BEGIN_REQUEST procedure. With very little PL/SQL this package downloads one page of Dan's MorgansLibrary.org website. Now consider how a little extra PL/SQL looking for anchor tags (a href) or pictures files (.jpg) or movie files (.mp4) could be used to identify the base locations for specific types of content. A little SUBSTR, a little INSTR, a little work with Posix Regular Expressions. Know where other pages are by reading links and you can download more pages, more pictures, more sound and/or movie files.

Can you see the security issue yet?

These two DBAs bought a 24 hour subscription to a porn site and downloaded terabytes of content.

Now you see the issue.
 
Recommended Security Rules

 NEVER
  • Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC
 WITH GREAT CARE
  • Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
  • Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO
 CAUTIONS
  • Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES
 
How Oracle Works
Most people think the Oracle Database is a relational database: It isn't. The product is an application development environment that contains the most efficient commercially available data mapping engine (aka a database). The "database" is the most capable application development environment ever created. Part of what makes it so powerful is the wealth of built in tools of which this is one of them.

But, as with tools like knives, axes, matches, tools must be used responsibly and in the case of this package Oracle Corp. unfortunately, granted EXECUTE to PUBLIC. To protect your database you must revoke EXECUTE from PUBLIC, you must create Network Access Control lists, and if you have Container Database you should create a Lockdown Profile.
 
UTL_HTTP Package Information
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_LARGEPLS_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 UTL_URL
DBMS_AQELM PLITBLM WWV_FLOW_DISP_PAGE_PLUGS
DBMS_ISCHED SDO_OLS WWV_FLOW_PRINT_UTIL
DBMS_PRVTAQIP URIFACTORY WWV_FLOW_WEBSERVICES_API
HTMLDB_UTIL UTL_HTT_LIB WWV_FLOW_WEB_SERVICES
HTTPURITYPE UTL_RAW WWV_FLOW_WS_GEOCODE
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.
Source {ORACLE_HOME}/rdbms/admin/utlhttp.sql
Subprograms
ADD_COOKIES GET_FOLLOW_REDIRECT SET_AUTHENTICATION
BEGIN_REQUEST GET_HEADER SET_AUTHENTICATION_FROM_WALLET
CLEAR_COOKIES GET_HEADER_BY_NAME SET_BODY_CHARSET
CLOSE_PERSISTENT_CONN GET_HEADER_COUNT SET_CONTENT_ENCODING_SUPPORT
CLOSE_PERSISTENT_CONNS GET_PERSISTENT_CONNS SET_COOKIE_SUPPORT
CREATE_REQUEST_CONTEXT GET_PERSISTENT_CONN_COUNT SET_DETAILED_EXCP_SUPPORT
DESTROY_REQUEST_CONTEXT GET_PERSISTENT_CONN_SUPPORT SET_FOLLOW_REDIRECT
END_REQUEST GET_PROXY SET_HEADER
END_RESPONSE GET_RESPONSE SET_PERSISTENT_CONN_SUPPORT
GET_AUTHENTICATION GET_RESPONSE_ERROR_CHECK SET_PROPERTY
GET_BODY_CHARSET GET_TRANSFER_TIMEOUT SET_PROXY
GET_CONTENT_ENCODING_SUPPORT READ_LINE SET_RESPONSE_ERROR_CHECK
GET_COOKIES READ_RAW SET_TRANSFER_TIMEOUT
GET_COOKIE_COUNT READ_RAW_PTR SET_WALLET
GET_COOKIE_SUPPORT READ_TEXT WRITE_LINE
GET_DETAILED_EXCP_SUPPORT REQUEST WRITE_RAW
GET_DETAILED_SQLCODE REQUEST_PIECES WRITE_TEXT
GET_DETAILED_SQLERRM    
 
ADD_COOKIES
Adds the cookies maintained by UTL_HTTP 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;
/
 
BEGIN_REQUEST
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('http://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;
/
 
CLEAR_COOKIES
Clears all the cookies currently maintained by the UTL_HTTP package utl_http.clear_cookies(request_context IN request_context_key DEFAULT NULL);
See ADD_COOKIES Demo Above
 
CLOSE_PERSISTENT_CONN
Closes a HTTP persistent connection in the current session utl_http.close_persistent_conn(conn IN connection);
DECLARE
 cur_conn utl_http.connection;
BEGIN
  cur_conn.host := 'washington.edu';
  cur_conn.port := 80;

  utl_http.close_persistent_conn(cur_conn);
END;
/
 
CLOSE_PERSISTENT_CONNS
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);
exec utl_http.close_persistent_conns(host => 'washington.edu', proxy_port => 80);
 
CREATE_REQUEST_CONTEXT
Creates a request context. A request context is a context that holds a wallet and a cookie for private use in making a HTTP request. utl_http.create_request_context(
wallet_path          IN VARCHAR2    DEFAULT NULL,
wallet_password      IN VARCHAR2    DEFAULT NULL,
enable_cookies       IN BOOLEAN     DEFAULT TRUE,
max_cookies          IN PLS_INTEGER DEFAULT 300,
max_cookies_per_site IN PLS_INTEGER DEFAULT 20)
RETURN request_context_key;
TBD
 
DESTROY_REQUEST_CONTEXT
Destroys a request context. A request cannot be destroyed when it is in use by a HTTP request or response utl_http.destroy_request_context(request_context IN OUT request_context_key);
TBD
 
END_REQUEST
Ends the HTTP request utl_http.end_request(r IN OUT NOCOPY req);
DECLARE
 req utl_http.req;
BEGIN
  req := utl_http.begin_request('http://www.morganslibrary.org');
  utl_http.end_request(req);
END;
/
 
END_RESPONSE
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
 
GET_AUTHENTICATION
Retrieves the HTTP authentication information needed for the request to be accepted by the Web server as indicated in the HTTP response header utl_http.get_authentication(
r         IN OUT NOCOPY resp,
scheme       OUT NOCOPY VARCHAR2,
realm        OUT NOCOPY VARCHAR2,
for_proxy IN            BOOLEAN DEFAULT FALSE);
set serveroutput on

DECLARE
 req  utl_http.req;
 resp utl_http.resp;
 s    VARCHAR2(100);
 rlm  VARCHAR2(200);
BEGIN
  req := utl_http.begin_request('http://www.morganslibrary.org');
  resp := utl_http.get_response(req);

  utl_http.get_authentication(resp, s, rlm);
  dbms_output.put_line(s);
  dbms_output.put_line(rlm);

  utl_http.end_response(resp);
END;
/

--================================ 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;
/
 
GET_BODY_CHARSET
Gets the default character of the body of all HTTP requests for use by the UTL_URL package.

Overload 1		 utl_http.get_body_charset(charset OUT NOCOPY VARCHAR2);
set severoutput on

DECLARE
 x VARCHAR2(20);
BEGIN
  utl_http.get_body_charset(x);
  dbms_output.put_line(x);
END;
/
Overload 2 utl_http.get_body_charset RETURN VARCHAR2;
SELECT utl_http.get_body_charset
FROM dual;
 
GET_CONTENT_ENCODING_SUPPORT
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;
/
 
GET_COOKIES
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;

    INSERT INTO my_cookies
    (session_id, name, value, domain, expire, path, secure, version)
    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;
  COMMIT;

  RETURN my_session_id;
END save_cookies;
/
 
GET_COOKIE_COUNT
Returns the number of cookies currently maintained by the UTL_HTTP package set by all Web servers utl_http.get_cookie_count(request_context IN request_context_key DEFAULT NULL)
RETURN PLS_INTEGER;
SELECT utl_http.get_cookie_count
FROM dual;
 
GET_COOKIE_SUPPORT
This procedure retrieves the current cookie support settings utl_http.get_cookie_support(
(enable              OUT BOOLEAN,
max_cookies          OUT PLS_INTEGER,
max_cookies_per_site OUT PLS_INTEGER);
TBD
 
GET_DETAILED_EXCP_SUPPORT
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;
/
 
GET_DETAILED_SQLCODE
Retrieves the detailed SQLCODE of the last exception raised utl_http.get_detailed_sqlcode RETURN PLS_INTEGER;
SELECT utl_http.get_detailed_sqlcode
FROM dual;
 
GET_DETAILED_SQLERRM
Retrieves the detailed SQLERRM of the last exception raised utl_http.get_detailed_sqlerrm RETURN VARCHAR2;
SELECT utl_http.get_detailed_sqlerrm
FROM dual;
 
GET_FOLLOW_REDIRECT
Retrieves the follow-redirect setting in the current session utl_http.get_follow_redirect(max_redirects OUT PLS_INTEGER);
set serveroutput on

DECLARE
 i PLS_INTEGER;
BEGIN
  utl_http.get_follow_redirect(i);
  dbms_output.put_line('Maximum Redirects Is: ' || TO_CHAR(i));
END;
/
 
GET_HEADER
Returns the n-th HTTP response header name and value returned in the response utl_http.get_header(
r     IN OUT NOCOPY resp,
n     IN            PLS_INTEGER,
name     OUT NOCOPY VARCHAR2,
value    OUT NOCOPY VARCHAR2);
See SET_AUTHENTICATION Demo Below
 
GET_HEADER_BY_NAME
Returns the HTTP response header value returned in the response given the name of the header utl_http.get_header_by_name(
r     IN OUT NOCOPY resp,
name  IN            VARCHAR2,
value    OUT NOCOPY VARCHAR2,
n     IN            PLS_INTEGER DEFAULT 1);
TBD
 
GET_HEADER_COUNT
Returns the number of HTTP response headers returned in the response utl_http.get_header_count(r IN OUT NOCOPY resp) RETURN PLS_INTEGER;
See SET_AUTHENTICATION Demo Below
 
GET_PERSISTENT_CONNS
Returns all the network connections currently kept persistent by the UTL_HTTP package to the Web servers utl_http.get_persistent_conns(connections IN OUT NOCOPY connection_table);
TBD
 
GET_PERSISTENT_CONN_COUNT
Returns the number of network connections currently kept persistent by the UTL_HTTP package to the Web servers utl_http.get_persistent_conn_count RETURN PLS_INTEGER;
SELECT utl_http.get_persistent_conn_count
FROM dual;
 
GET_PERSISTENT_CONN_SUPPORT
Checks if the persistent connection support is enabled and returns the maximum number of persistent connections maintained in the current session utl_http.get_persistent_conn_support(
enable    OUT BOOLEAN,
max_conns OUT PLS_INTEGER);
TBD
 
GET_PROXY
Retrieves the current proxy settings utl_http.get_proxy(
proxy            OUT NOCOPY VARCHAR2,
no_proxy_domains OUT NOCOPY VARCHAR2);
TBD
 
GET_RESPONSE
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
 
GET_RESPONSE_ERROR_CHECK
Checks if response error check is set utl_http.get_response_error_check(enable OUT BOOLEAN);
TBD
 
GET_TRANSFER_TIMEOUT
Retrieves the default time-out value for all future HTTP requests utl_http.get_transfer_timeout(timeout OUT PLS_INTEGER DEFAULT 60);
set serveroutput on

DECLARE
 x PLS_INTEGER;
BEGIN
  utl_http.get_transfer_timeout(x);
  dbms_output.put_line(x);
END;
/
 
READ_LINE
Reads the HTTP response body in text form until the end of line is reached and returns the output in the caller-supplied buffer utl_http.read_line(
r           IN OUT NOCOPY resp,
data           OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
remove_crlf IN            BOOLEAN DEFAULT FALSE);
See BEGIN_REQUEST Demo Above
 
READ_RAW
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);
TBD
 
READ_RAW_PTR
Undocumented 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);
TBD
 
READ_TEXT
Reads the HTTP response body in text form and returns the output in the caller-supplied buffer utl_http.read_text(
r    IN OUT NOCOPY resp,
data    OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
len  IN            PLS_INTEGER DEFAULT NULL);
See SET_AUTHENTICATION Demo Below
 
REQUEST
Fetches a Web page. This function returns the first 2000 bytes of the page at most. utl_http.request(
url             IN VARCHAR2,
proxy           IN VARCHAR2 DEFAULT NULL,
wallet_path     IN VARCHAR2 DEFAULT NULL,
wallet_password IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 req   utl_http.req;
 resp  utl_http.resp;
 value VARCHAR2(32000);
BEGIN
  req := utl_http.begin_request('http://www.morganslibrary.org');
  resp := utl_http.get_response(req);

  value := utl_http.request('http://www.morganslibrary.org/');
  dbms_output.put_line(value);

  utl_http.end_response(resp);
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
END;
/
 
REQUEST_PIECES
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('http://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;
/
 
SET_AUTHENTICATION
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);

  utl_http.set_proxy(
  proxy=>'www-proxy.morganslibrary.org', no_proxy_domains=>'morganslibrary.org');

  req := utl_http.begin_request(url => v_url, method => 'GET');
  -- or use method => 'POST' and utl_http.write_text
  -- to create an arbitrarily long msg

  utl_http.set_authentication(r => req, username => 'SomeUser',
    password => 'SomePassword', scheme => 'Basic', for_proxy => FALSE);

  utl_http.set_header(r=>req,name=>'User-Agent',value=>'Mozilla/4.0');

  resp := utl_http.get_response(r => req);

  dbms_output.put_line('Status code: ' || resp.status_code);
  dbms_output.put_line('Reason phrase: ' || resp.reason_phrase);

  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;
/
 
SET_AUTHENTICATION_FROM_WALLET
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);
TBD
 
SET_BODY_CHARSET
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);
TBD
 
SET_CONTENT_ENCODING_SUPPORT
Sets the default content-encoding support for the specified encoding scheme for all future HTTP requests

Overload 1		 utl_http.set_content_encoding_support(
scheme IN VARCHAR2,
enable IN BOOLEAN DEFAULT FALSE);
exec utl_http.set_content_encoding_support('GZIP', FALSE);
Overload 2 utl_http.set_content_encoding_support(
r      IN OUT NOCOPY req,
scheme IN            VARCHAR2,
enable IN            BOOLEAN DEFAULT FALSE);
TBD
 
SET_COOKIE_SUPPORT
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);
TBD
 
SET_DETAILED_EXCP_SUPPORT
Sets the UTL_HTTP package to raise a detailed exception utl_http.set_detailed_excp_support(enable IN BOOLEAN DEFAULT FALSE);
See SET_AUTHENTICATION Demo Above
 
SET_FOLLOW_REDIRECT
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);
TBD
 
SET_HEADER
Sets a HTTP request header. The request header is sent to the Web server as soon as it is set utl_http.set_header(
r     IN OUT NOCOPY req,
name  IN            VARCHAR2,
value IN            VARCHAR2 DEFAULT NULL);
See BEGIN_REQUEST Demo Above
 
SET_PERSISTENT_CONN_SUPPORT
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) := 'http://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

BEGIN
  utl_http.set_persistent_conn_support(FALSE, 1);
  paths(1) := '...';
  paths(2) := '...';
  ...
  fetch_pages(paths);
END;
/
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) := 'http://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;

BEGIN
  utl_http.set_persistent_conn_support(FALSE, 1);
  paths(1) := '...';
  paths(2) := '...';
  fetch_pages(paths);
END;
/
 
SET_PROPERTY
Sets an HTTP request property utl_http.set_property(
r     IN OUT NOCOPY req,
name  IN            VARCHAR2,
value IN            VARCHAR2 DEFAULT NULL);
TBD
 
SET_PROXY
Sets the proxy to be used for requests of the HTTP or other protocols utl_http.set_proxy(
proxy            IN VARCHAR2,
no_proxy_domains IN VARCHAR2 DEFAULT NULL);
See SET_AUTHENTICATION Demo Above
 
SET_RESPONSE_ERROR_CHECK
Sets whether get_response should raise an exception when the Web server returns a status code that indicates an error

Overload 1		 utl_http.set_response_error_check(enable IN BOOLEAN DEFAULT FALSE);
See SET_AUTHENTICATION Demo Above
Overload 2 utl_http.set_response_error_check(
r      IN OUT NOCOPY req,
enable IN            BOOLEAN DEFAULT FALSE);
TBD
 
SET_TRANSFER_TIMEOUT
Sets the default time-out value for  reading response
Overload 1		 utl_http.set_transfer_timeout(timeout IN PLS_INTEGER DEFAULT 60);
TBD
Overload 2 utl_http.set_transfer_timeout(
r       IN OUT NOCOPY req,
timeout IN            PLS_INTEGER DEFAULT 60);
TBD
 
SET_WALLET
Sets the Oracle wallet to be used for all HTTP requests over SSL, namely HTTPS utl_http.set_wallet(
path     IN VARCHAR2,
password IN VARCHAR2 DEFAULT NULL);
DECLARE
 wloc VARCHAR2(4000);
BEGIN
  SELECT wrl_parameter
  INTO wloc
  FROM gv$encryption_wallet;

  utl_http.set_wallet(wloc);
END;
/
 
WRITE_LINE
Writes a text line in the HTTP request body and ends the line with new-line characters (CRLF as defined in UTL_TCP) utl_http.write_line(
r    IN OUT NOCOPY req,
data IN            VARCHAR2 CHARACTER SET ANY_CS);
TBD
 
WRITE_RAW
Writes binary 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. utl_http.write_raw(r IN OUT NOCOPY req, data IN RAW);
TBD
 
WRITE_TEXT
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('http://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);

  utl_http.end_response(resp);
END;
/

Related Topics
DBMS_NETWORK_ACL_ADMIN
Lockdown Profiles