CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle UTL_HTTP
Version 11.1
 
General Information
Note: Makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. Can be used to access data on the Internet over the HTTP protocol.
Source {ORACLE_HOME}/rdbms/admin/utlhttp.sql
First Availability 7.3.4

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 UTL_HTT_LIB
DBMS_AQELM PLITBLM UTL_RAW
DBMS_PRVTAQIP URIFACTORY UTL_URL
HTTPURITYPE    

Exceptions
Exception Name

Error Code 

Reason

init_failed -29272 The UTL_HTTP pkg initialization failed
request_failed -29273 The HTTP request failed
bad_argument -29261 A bad argument was passed to an API
bad_url -29262 The URL is bad
protocol_error -29263 A HTTP protocol error occurred
unknown_scheme -29264 The scheme of the URL is unknown
header_not_found -29265 The HTTP header is not found
end_of_body -29266 The end of response body is reached
illegal_call -29267 The API call is illegal at this stage
http_client_error -29268 A 4xx response code is returned
http_server_error -29269 A 5xx response code is returned
too_many_requests -29270 Too many open requests or responses
partial_multibyte_char -29275 A partial multi-byte character found
transfer_timeout -29276 Transfer time-out occurred
network_access_denied -24247 Network ACL not assigned
Security Model Execute is granted to PUBLIC as AUTHID CURRENT_USER
 
ADD_COOKIES

Adds the cookies maintained by UTL_HTTP
utl_http.add_cookies(cookies IN cookie_table);
CREATE OR REPLACE PROCEDURE restore_cookies(this_session_id IN BINARY_INTEGER) 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;
/
 
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) 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.psoug.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
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);
TBD
 
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);
 
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.psoug.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 / See SET_AUTHENTICATION Demo
 
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);
TBD

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.psoug.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_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);
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 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
 
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
 
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 HTTP 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 / See SET_AUTHENTICATION Demo
 
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
 
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_TEXT
Reads the HTTP response body in text form and returns the output in the caller-supplied buffer. The end_of_body exception will be raised if the end of the HTTP response body is reached. Text data is automatically converted from the response body character set to the database character set. 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
 
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.psoug.org');
  resp := utl_http.get_response(req);

  value := utl_http.request('http://www.psoug.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.psoug.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',
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.psoug.org', no_proxy_domains=>'psoug.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_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_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
 
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
 
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.psoug.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.psoug.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_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
 
SET_RESPONSE_ERROR_CHECK
Sets whether get_response should raise an exception when the Web server returns a status code that indicates an error. utl_http.set_response_error_check(enable IN BOOLEAN DEFAULT FALSE);
See SET_AUTHENTICATION Demo
 
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.psoug.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_ADM
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [207 users online]    © 2010 psoug.org