UTL_HTTP¶

The UTL_HTTP package provides a way to use the HTTP or HTTPS protocol to retrieve information found at an URL. Advanced Server supports the following functions and procedures:

Function/Procedure	Return Type	Description
`BEGIN_REQUEST(url, method, http_version)`	`UTL_HTTP.REQ`	Initiates a new HTTP request.
`END_REQUEST(r IN OUT)`	n/a	Ends an HTTP request before allowing it to complete.
`END_RESPONSE(r IN OUT)`	n/a	Ends the HTTP response.
`END_OF_BODY(r IN OUT)`	n/a	Ends package body
`GET_BODY_CHARSET`	`VARCHAR2`	Returns the default character set of the body of future HTTP requests.
`GET_BODY_CHARSET(charset OUT)`	n/a	Returns the default character set of the body of future HTTP requests.
`GET_FOLLOW_REDIRECT(max_redirects OUT)`	n/a	Current setting for the maximum number of redirections allowed.
`GET_HEADER(r IN OUT, n, name OUT, value OUT)`	n/a	Returns the nth header of the HTTP response.
`GET_HEADER_BY_NAME(r IN OUT, name, value OUT, n)`	n/a	Returns the HTTP response header for the specified name.
`GET_HEADER_COUNT(r IN OUT)`	`INTEGER`	Returns the number of HTTP response headers.
`GET_RESPONSE(r IN OUT)`	`UTL_HTTP.RESP`	Returns the HTTP response.
`GET_RESPONSE_ERROR_CHECK(enable OUT)`	n/a	Returns whether or not response error check is set.
`GET_TRANSFER_TIMEOUT(timeout OUT)`	n/a	Returns the transfer timeout setting for HTTP requests.
`READ_LINE(r IN OUT, data OUT, remove_crlf)`	n/a	Returns the HTTP response body in text form until the end of line.
`READ_RAW(r IN OUT, data OUT, len)`	n/a	Returns the HTTP response body in binary form for a specified number of bytes.
`READ_TEXT(r IN OUT, data OUT, len)`	n/a	Returns the HTTP response body in text form for a specified number of characters.
`REQUEST(url)`	`VARCHAR2`	Returns the content of a web page.
`REQUEST_PIECES(url, max_pieces)`	`UTL_HTTP. HTML_PIECES`	Returns a table of 2000-byte segments retrieved from an URL.
`SET_BODY_CHARSET(charset)`	n/a	Sets the default character set of the body of future HTTP requests.
`SET_FOLLOW_REDIRECT(max_redirects)`	n/a	Sets the maximum number of times to follow the redirect instruction.
`SET_FOLLOW_REDIRECT(r IN OUT, max_redirects)`	n/a	Sets the maximum number of times to follow the redirect instruction for an individual request.
`SET_HEADER(r IN OUT, name, value)`	n/a	Sets the HTTP request header.
`SET_RESPONSE_ERROR_CHECK(enable)`	n/a	Determines whether or not HTTP 4xx and 5xx status codes are to be treated as errors.
`SET_TRANSFER_TIMEOUT(timeout)`	n/a	Sets the default, transfer timeout value for HTTP requests.
`SET_TRANSFER_TIMEOUT(r IN OUT, timeout)`	n/a	Sets the transfer timeout value for an individual HTTP request.
`WRITE_LINE(r IN OUT, data)`	n/a	Writes CRLF terminated data to the HTTP request body in TEXT form.
`WRITE_RAW(r IN OUT, data)`	n/a	Writes data to the HTTP request body in BINARY form.
`WRITE_TEXT(r IN OUT, data)`	n/a	Writes data to the HTTP request body in TEXT form.

Advanced Server’s implementation of UTL_HTTP is a partial implementation when compared to Oracle’s version. Only those functions and procedures listed in the table above are supported.

Note

In Advanced Server, an HTTP 4xx or HTTP 5xx response produces a database error; in Oracle, this is configurable but FALSE by default.

In Advanced Server, the UTL_HTTP text interfaces expect the downloaded data to be in the database encoding. All currently-available interfaces are text interfaces. In Oracle, the encoding is detected from HTTP headers; in the absence of the header, the default is configurable and defaults to ISO-8859-1.

Advanced Server ignores all cookies it receives.

The UTL_HTTP exceptions that can be raised in Oracle are not recognized by Advanced Server. In addition, the error codes returned by Advanced Server are not the same as those returned by Oracle.

UTL_HTTP Exception Codes

If a call to a UTL_HTTP procedure or function raises an exception, you can use the condition name to catch the exception. The UTL_HTTP package reports the following exception codes compatible with Oracle databases:

Exception Code	Condition Name	Description	Raised Where
-29266	`END_OF_BODY`	The end of HTTP response body is reached	`READ_LINE, READ_RAW`, and `READ_TEXT` functions

To use the UTL_HTTP.END_OF_BODY exception, first you need to run the utl_http_public.sql file from the contrib/utl_http directory of your installation directory.

There are various public constants available with UTL_HTTP. These are listed in the following tables.

The following table contains UTL_HTTP public constants defining HTTP versions and port assignments.

HTTP VERSIONS
HTTP_VERSION_1_0	CONSTANT VARCHAR2(64) := ‘HTTP/1.0’;
HTTP_VERSION_1_1	CONSTANT VARCHAR2(64) := ‘HTTP/1.1’;
STANDARD PORT ASSIGNMENTS
DEFAULT_HTTP_PORT	CONSTANT INTEGER := 80;
DEFAULT_HTTPS_PORT	CONSTANT INTEGER := 443;

The following table contains UTL_HTTP public status code constants.

1XX INFORMATIONAL
HTTP_CONTINUE	CONSTANT INTEGER := 100;
HTTP_SWITCHING_PROTOCOLS	CONSTANT INTEGER := 101;
HTTP_PROCESSING	CONSTANT INTEGER := 102;
2XX SUCCESS
HTTP_OK	CONSTANT INTEGER := 200;
HTTP_CREATED	CONSTANT INTEGER := 201;
HTTP_ACCEPTED	CONSTANT INTEGER := 202;
HTTP_NON_AUTHORITATIVE_INFO	CONSTANT INTEGER := 203;
HTTP_NO_CONTENT	CONSTANT INTEGER := 204;
HTTP_RESET_CONTENT	CONSTANT INTEGER := 205;
HTTP_PARTIAL_CONTENT	CONSTANT INTEGER := 206;
HTTP_MULTI_STATUS	CONSTANT INTEGER := 207;
HTTP_ALREADY_REPORTED	CONSTANT INTEGER := 208;
HTTP_IM_USED	CONSTANT INTEGER := 226;
3XX REDIRECTION
HTTP_MULTIPLE_CHOICES	CONSTANT INTEGER := 300;
HTTP_MOVED_PERMANENTLY	CONSTANT INTEGER := 301;
HTTP_FOUND	CONSTANT INTEGER := 302;
HTTP_SEE_OTHER	CONSTANT INTEGER := 303;
HTTP_NOT_MODIFIED	CONSTANT INTEGER := 304;
HTTP_USE_PROXY	CONSTANT INTEGER := 305;
HTTP_SWITCH_PROXY	CONSTANT INTEGER := 306;
HTTP_TEMPORARY_REDIRECT	CONSTANT INTEGER := 307;
HTTP_PERMANENT_REDIRECT	CONSTANT INTEGER := 308;

4XX CLIENT ERROR
HTTP_BAD_REQUEST	CONSTANT INTEGER := 400;
HTTP_UNAUTHORIZED	CONSTANT INTEGER := 401;
HTTP_PAYMENT_REQUIRED	CONSTANT INTEGER := 402;
HTTP_FORBIDDEN	CONSTANT INTEGER := 403;
HTTP_NOT_FOUND	CONSTANT INTEGER := 404;
HTTP_METHOD_NOT_ALLOWED	CONSTANT INTEGER := 405;
HTTP_NOT_ACCEPTABLE	CONSTANT INTEGER := 406;
HTTP_PROXY_AUTH_REQUIRED	CONSTANT INTEGER := 407;
HTTP_REQUEST_TIME_OUT	CONSTANT INTEGER := 408;
HTTP_CONFLICT	CONSTANT INTEGER := 409;
HTTP_GONE	CONSTANT INTEGER := 410;
HTTP_LENGTH_REQUIRED	CONSTANT INTEGER := 411;
HTTP_PRECONDITION_FAILED	CONSTANT INTEGER := 412;
HTTP_REQUEST_ENTITY_TOO_LARGE	CONSTANT INTEGER := 413;
HTTP_REQUEST_URI_TOO_LARGE	CONSTANT INTEGER := 414;
HTTP_UNSUPPORTED_MEDIA_TYPE	CONSTANT INTEGER := 415;
HTTP_REQ_RANGE_NOT_SATISFIABLE	CONSTANT INTEGER := 416;
HTTP_EXPECTATION_FAILED	CONSTANT INTEGER := 417;
HTTP_I_AM_A_TEAPOT	CONSTANT INTEGER := 418;
HTTP_AUTHENTICATION_TIME_OUT	CONSTANT INTEGER := 419;
HTTP_ENHANCE_YOUR_CALM	CONSTANT INTEGER := 420;
HTTP_UNPROCESSABLE_ENTITY	CONSTANT INTEGER := 422;
HTTP_LOCKED	CONSTANT INTEGER := 423;
HTTP_FAILED_DEPENDENCY	CONSTANT INTEGER := 424;
HTTP_UNORDERED_COLLECTION	CONSTANT INTEGER := 425;
HTTP_UPGRADE_REQUIRED	CONSTANT INTEGER := 426;
HTTP_PRECONDITION_REQUIRED	CONSTANT INTEGER := 428;
HTTP_TOO_MANY_REQUESTS	CONSTANT INTEGER := 429;
HTTP_REQUEST_HEADER_FIELDS_TOO_LARGE	CONSTANT INTEGER := 431;
HTTP_NO_RESPONSE	CONSTANT INTEGER := 444;
HTTP_RETRY_WITH	CONSTANT INTEGER := 449;
HTTP_BLOCKED_BY_WINDOWS_PARENTAL_CONTROLS	CONSTANT INTEGER := 450;
HTTP_REDIRECT	CONSTANT INTEGER := 451;
HTTP_REQUEST_HEADER_TOO_LARGE	CONSTANT INTEGER := 494;
HTTP_CERT_ERROR	CONSTANT INTEGER := 495;
HTTP_NO_CERT	CONSTANT INTEGER := 496;
HTTP_HTTP_TO_HTTPS	CONSTANT INTEGER := 497;
HTTP_CLIENT_CLOSED_REQUEST	CONSTANT INTEGER := 499;

5XX SERVER ERROR
HTTP_INTERNAL_SERVER_ERROR	CONSTANT INTEGER := 500;
HTTP_NOT_IMPLEMENTED	CONSTANT INTEGER := 501;
HTTP_BAD_GATEWAY	CONSTANT INTEGER := 502;
HTTP_SERVICE_UNAVAILABLE	CONSTANT INTEGER := 503;
HTTP_GATEWAY_TIME_OUT	CONSTANT INTEGER := 504;
HTTP_VERSION_NOT_SUPPORTED	CONSTANT INTEGER := 505;
HTTP_VARIANT_ALSO_NEGOTIATES	CONSTANT INTEGER := 506;
HTTP_INSUFFICIENT_STORAGE	CONSTANT INTEGER := 507;
HTTP_LOOP_DETECTED	CONSTANT INTEGER := 508;
HTTP_BANDWIDTH_LIMIT_EXCEEDED	CONSTANT INTEGER := 509;
HTTP_NOT_EXTENDED	CONSTANT INTEGER := 510;
HTTP_NETWORK_AUTHENTICATION_REQUIRED	CONSTANT INTEGER := 511;
HTTP_NETWORK_READ_TIME_OUT_ERROR	CONSTANT INTEGER := 598;
HTTP_NETWORK_CONNECT_TIME_OUT_ERROR	CONSTANT INTEGER := 599;

HTML_PIECES¶

The UTL_HTTP package declares a type named HTML_PIECES, which is a table of type VARCHAR2 (2000) indexed by BINARY INTEGER. A value of this type is returned by the REQUEST_PIECES function.

TYPE html_pieces IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

REQ¶

The REQ record type holds information about each HTTP request.

TYPE req IS RECORD (
    url             VARCHAR2(32767),    -- URL to be accessed
    method          VARCHAR2(64),       -- HTTP method
    http_version    VARCHAR2(64),       -- HTTP version
    private_hndl    INTEGER             -- Holds handle for this request
);

RESP¶

The RESP record type holds information about the response from each HTTP request.

TYPE resp IS RECORD (
    status_code     INTEGER,            -- HTTP status code
    reason_phrase   VARCHAR2(256),      -- HTTP response reason phrase
    http_version    VARCHAR2(64),       -- HTTP version
    private_hndl    INTEGER             -- Holds handle for this response
);

BEGIN_REQUEST¶

The BEGIN_REQUEST function initiates a new HTTP request. A network connection is established to the web server with the specified URL. The signature is:

BEGIN_REQUEST(<url> IN VARCHAR2, <method> IN VARCHAR2 DEFAULT
'GET ', <http_version> IN VARCHAR2 DEFAULT NULL) RETURN
UTL_HTTP.REQ

The BEGIN_REQUEST function returns a record of type UTL_HTTP.REQ.

Parameters

url

url is the Uniform Resource Locator from which UTL_HTTP will return content.

method

method is the HTTP method to be used. The default is GET.

http_version

http_version is the HTTP protocol version sending the request. The specified values should be either HTTP/1.0 or HTTP/1.1. The default is null in which case the latest HTTP protocol version supported by the UTL_HTTP package is used which is 1.1.

END_REQUEST¶

The END_REQUEST procedure terminates an HTTP request. Use the END_REQUEST procedure to terminate an HTTP request without completing it and waiting for the response. The normal process is to begin the request, get the response, then close the response. The signature is:

END_REQUEST(<r> IN OUT UTL_HTTP.REQ)

Parameters

r

r is the HTTP request record.

END_RESPONSE¶

The END_RESPONSE procedure terminates the HTTP response. The END_RESPONSE procedure completes the HTTP request and response. This is the normal method to end the request and response process. The signature is:

END_RESPONSE(<r> IN OUT UTL_HTTP.RESP)

Parameters

r

r is the HTTP response record.

GET_BODY_CHARSET¶

The GET_BODY_CHARSET program is available in the form of both a procedure and a function. A call to GET_BODY_CHARSET returns the default character set of the body of future HTTP requests.

The procedure signature is:

GET_BODY_CHARSET(<charset> OUT VARCHAR2)

The function signature is:

GET_BODY_CHARSET() RETURN VARCHAR2

This function returns a VARCHAR2 value.

Parameters

charset

charset is the character set of the body.

Examples

The following is an example of the GET_BODY_CHARSET function.

edb=# SELECT UTL_HTTP.GET_BODY_CHARSET() FROM DUAL;
 get_body_charset
------------------
 ISO-8859-1
(1 row)

GET_FOLLOW_REDIRECT¶

The GET_FOLLOW_REDIRECT procedure returns the current setting for the maximum number of redirections allowed. The signature is:

GET_FOLLOW_REDIRECT(<max_redirects> OUT INTEGER)

Parameters

max_redirects

max_redirects is maximum number of redirections allowed.

GET_HEADER¶

The GET_HEADER procedure returns the nth header of the HTTP response. The signature is:

GET_HEADER(<r> IN OUT UTL_HTTP.RESP, <n> INTEGER, <name> OUT
VARCHAR2, <value> OUT VARCHAR2)

Parameters

r

r is the HTTP response record.

n

n is the nth header of the HTTP response record to retrieve.

name

name is the name of the response header.

value

value is the value of the response header.

Examples

The following example retrieves the header count, then the headers.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
    v_name          VARCHAR2(30);
    v_value         VARCHAR2(200);
    v_header_cnt    INTEGER;
BEGIN
 -- Initiate request and get response
    v_req := UTL_HTTP.BEGIN_REQUEST('www.enterprisedb.com');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);

 -- Get header count
    v_header_cnt := UTL_HTTP.GET_HEADER_COUNT(v_resp);
    DBMS_OUTPUT.PUT_LINE('Header Count: ' || v_header_cnt);

 -- Get all headers
    FOR i IN 1 .. v_header_cnt LOOP
        UTL_HTTP.GET_HEADER(v_resp, i, v_name, v_value);
        DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_value);
    END LOOP;

 -- Terminate request
    UTL_HTTP.END_RESPONSE(v_resp);
END;

The following is the output from the example.

Header Count: 23
Age: 570
Cache-Control: must-revalidate
Content-Type: text/html; charset=utf-8
Date: Wed, 30 Apr 2015 14:57:52 GMT
ETag: "aab02f2bd2d696eed817ca89ef411dda"
Expires: Sun, 19 Nov 1978 05:00:00 GMT
Last-Modified: Wed, 30 Apr 2015 14:15:49 GMT
RTSS: 1-1307-3
Server: Apache/2.2.3 (Red Hat)
Set-Cookie:
SESS2771d0952de2a1a84d322a262e0c173c=jn1u1j1etmdi5gg4lh8hakvs01;
expires=Fri, 23-May-2015 18:21:43 GMT; path=/; domain=.enterprisedb.com
Vary: Accept-Encoding
Via: 1.1 varnish
X-EDB-Backend: ec
X-EDB-Cache: HIT
X-EDB-Cache-Address: 10.31.162.212
X-EDB-Cache-Server: ip-10-31-162-212
X-EDB-Cache-TTL: 600.000
X-EDB-Cacheable: MAYBE: The user has a cookie of some sort. Maybe it's
double choc-chip!
X-EDB-Do-GZIP: false
X-Powered-By: PHP/5.2.17
X-Varnish: 484508634 484506789
transfer-encoding: chunked
Connection: keep-alive

GET_HEADER_BY_NAME¶

The GET_HEADER_BY_NAME procedure returns the header of the HTTP response according to the specified name. The signature is:

GET_HEADER_BY_NAME(<r> IN OUT UTL_HTTP.RESP, <name> VARCHAR2,
<value> OUT VARCHAR2, <n> INTEGER DEFAULT 1)

Parameters

r

r is the HTTP response record.

name

name is the name of the response header to retrieve.

value

value is the value of the response header.

n

n is the nth header of the HTTP response record to retrieve according to the values specified by name. The default is 1.

Examples

The following example retrieves the header for Content-Type.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
    v_name          VARCHAR2(30) := 'Content-Type';
    v_value         VARCHAR2(200);
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('www.enterprisedb.com');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    UTL_HTTP.GET_HEADER_BY_NAME(v_resp, v_name, v_value);
    DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_value);
    UTL_HTTP.END_RESPONSE(v_resp);
END;

Content-Type: text/html; charset=utf-8

GET_HEADER_COUNT¶

The GET_HEADER_COUNT function returns the number of HTTP response headers. The signature is:

GET_HEADER_COUNT(<r> IN OUT UTL_HTTP.RESP) RETURN INTEGER

This function returns an INTEGER value.

Parameters

r

r is the HTTP response record.

GET_RESPONSE¶

The GET_RESPONSE function sends the network request and returns any HTTP response. The signature is:

GET_RESPONSE(<r> IN OUT UTL_HTTP.REQ) RETURN UTL_HTTP.RESP

This function returns a UTL_HTTP.RESP record.

Parameters

r

r is the HTTP request record.

GET_RESPONSE_ERROR_CHECK¶

The GET_RESPONSE_ERROR_CHECK procedure returns whether or not response error check is set. The signature is:

GET_RESPONSE_ERROR_CHECK(<enable> OUT BOOLEAN)

Parameters

enable

enable returns TRUE if response error check is set, otherwise it returns FALSE.

GET_TRANSFER_TIMEOUT¶

The GET_TRANSFER_TIMEOUT procedure returns the current, default transfer timeout setting for HTTP requests. The signature is:

GET_TRANSFER_TIMEOUT(<timeout> OUT INTEGER)

Parameters

timeout

timeout is the transfer timeout setting in seconds.

READ_LINE¶

The READ_LINE procedure returns the data from the HTTP response body in text form until the end of line is reached. A CR character, a LF character, a CR LF sequence, or the end of the response body constitutes the end of line. The signature is:

READ_LINE(<r> IN OUT UTL_HTTP.RESP, <data> OUT VARCHAR2,
<remove_crlf> BOOLEAN DEFAULT FALSE)

Parameters

r

r is the HTTP response record.

data

data is the response body in text form.

remove_crlf

Set remove_crlf to TRUE to remove new line characters, otherwise set to FALSE. The default is FALSE.

Examples

The following example retrieves and displays the body of the specified website.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
    v_value         VARCHAR2(1024);
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('http://www.enterprisedb.com');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    LOOP
        UTL_HTTP.READ_LINE(v_resp, v_value, TRUE);
        DBMS_OUTPUT.PUT_LINE(v_value);
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            UTL_HTTP.END_RESPONSE(v_resp);
END;

The following is the output.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr">

  <!-- ___________________________ HEAD ___________________________ -->

  <head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />


    <title>EnterpriseDB | The Postgres Database Company</title>

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="keywords" content="postgres, postgresql, postgresql installer,
mysql migration, open source database, training, replication" />
<meta name="description" content="The leader in open source database
products, services, support, training and expertise based on PostgreSQL.
Free downloads, documentation, and tutorials." />
<meta name="abstract" content="The Enterprise PostgreSQL Company" />
<link rel="EditURI" type="application/rsd+xml" title="RSD" href="http://
www.enterprisedb.com/blogapi/rsd" />
<link rel="alternate" type="application/rss+xml" title="EnterpriseDB RSS"
href="http://www.enterprisedb.com/rss.xml" />
<link rel="shortcut icon" href="/sites/all/themes/edb_pixelcrayons/
favicon.ico" type="image/x-icon" />
    <link type="text/css" rel="stylesheet" media="all" href="/sites/default/
    files/css/css_db11adabae0aed6b79a2c3c52def4754.css" />
<!--[if IE 6]>
<link type="text/css" rel="stylesheet" media="all" href="/sites/all/themes/
oho_basic/css/ie6.css?g" />
<![endif]-->
<!--[if IE 7]>
<link type="text/css" rel="stylesheet" media="all" href="/sites/all/themes/
oho_basic/css/ie7.css?g" />
<![endif]-->
    <script type="text/javascript" src="/sites/default/files/js/
    js_74d97b1176812e2fd6e43d62503a5204.js"></script>
<script type="text/javascript">
<!--//--><![CDATA[//><!--

READ_RAW¶

The READ_RAW procedure returns the data from the HTTP response body in binary form. The number of bytes returned is specified by the len parameter. The signature is:

READ_RAW(<r> IN OUT UTL_HTTP.RESP, <data> OUT RAW, <len> INTEGER)

Parameters

r

r is the HTTP response record.

data

data is the response body in binary form.

len

Set len to the number of bytes of data to be returned.

Examples

The following example retrieves and displays the first 150 bytes in binary form.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
    v_data          RAW;
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('http://www.enterprisedb.com');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    UTL_HTTP.READ_RAW(v_resp, v_data, 150);
    DBMS_OUTPUT.PUT_LINE(v_data);
    UTL_HTTP.END_RESPONSE(v_resp);
END;

The following is the output from the example.

\x3c21444f43545950452068746d6c205055424c494320222d2f2f5733432f2f4454442058485
44d4c20312e30205374726963742f2f454e220d0a202022687474703a2f2f7777772e77332e6f
72672f54522f7868746d6c312f4454442f7868746d6c312d7374726963742e647464223e0d0a3
c68746d6c20786d6c6e733d22687474703a2f2f7777772e77332e6f72672f313939392f

READ_TEXT¶

The READ_TEXT procedure returns the data from the HTTP response body in text form. The maximum number of characters returned is specified by the len parameter. The signature is:

READ_TEXT(<r> IN OUT UTL_HTTP.RESP, <data> OUT VARCHAR2, <len> INTEGER)

Parameters

r

r is the HTTP response record.

data

data is the response body in text form.

len

Set len to the maximum number of characters to be returned.

Examples

The following example retrieves the first 150 characters.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
    v_data          VARCHAR2(150);
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('http://www.enterprisedb.com');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    UTL_HTTP.READ_TEXT(v_resp, v_data, 150);
    DBMS_OUTPUT.PUT_LINE(v_data);
    UTL_HTTP.END_RESPONSE(v_resp);
END;

The following is the output.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/

REQUEST¶

The REQUEST function returns the first 2000 bytes retrieved from a user-specified URL. The signature is:

REQUEST(<url> IN VARCHAR2) RETURN VARCHAR2

If the data found at the given URL is longer than 2000 bytes, the remainder will be discarded. If the data found at the given URL is shorter than 2000 bytes, the result will be shorter than 2000 bytes.

Parameters

url

url is the Uniform Resource Locator from which UTL_HTTP will return content.

Example

The following command returns the first 2000 bytes retrieved from the EDB website:

SELECT UTL_HTTP.REQUEST('http://www.enterprisedb.com/') FROM DUAL;

REQUEST_PIECES¶

The REQUEST_PIECES function returns a table of 2000-byte segments retrieved from an URL. The signature is:

REQUEST_PIECES(<url> IN VARCHAR2, <max_pieces> NUMBER IN
DEFAULT 32767) RETURN UTL_HTTP.HTML_PIECES

Parameters

url

url is the Uniform Resource Locator from which UTL_HTTP will return content.

max_pieces

max_pieces specifies the maximum number of 2000-byte segments that the REQUEST_PIECES function will return. If max_pieces specifies more units than are available at the specified url, the final unit will contain fewer bytes.

Example

The following example returns the first four 2000 byte segments retrieved from the EDB website:

DECLARE
    result UTL_HTTP.HTML_PIECES;
BEGIN
result := UTL_HTTP.REQUEST_PIECES('http://www.enterprisedb.com/', 4);
END

SET_BODY_CHARSET¶

The SET_BODY_CHARSET procedure sets the default character set of the body of future HTTP requests. The signature is:

SET_BODY_CHARSET(<charset> VARCHAR2 DEFAULT NULL)

Parameters

charset

charset is the character set of the body of future requests. The default is null in which case the database character set is assumed.

SET_FOLLOW_REDIRECT¶

The SET_FOLLOW_REDIRECT procedure sets the maximum number of times the HTTP redirect instruction is to be followed in the response to this request or future requests. This procedures has two signatures:

SET_FOLLOW_REDIRECT(<max_redirects> IN INTEGER DEFAULT 3)

and

SET_FOLLOW_REDIRECT(<r> IN OUT UTL_HTTP.REQ, <max_redirects>
IN INTEGER DEFAULT 3)``

Use the second form to change the maximum number of redirections for an individual request that a request inherits from the session default settings.

Parameters

r

r is the HTTP request record.

max_redirects

max_redirects is maximum number of redirections allowed. Set to 0 to disable redirections. The default is 3.

SET_HEADER¶

The SET_HEADER procedure sets the HTTP request header. The signature is:

SET_HEADER(<r> IN OUT UTL_HTTP.REQ, <name> IN VARCHAR2, <value>
IN VARCHAR2 DEFAULT NULL)

Parameters

r

r is the HTTP request record.

name

name is the name of the request header.

value

value is the value of the request header. The default is null.

SET_RESPONSE_ERROR_CHECK¶

The SET_RESPONSE_ERROR_CHECK procedure determines whether or not HTTP 4xx and 5xx status codes returned by the GET_RESPONSE function should be interpreted as errors. The signature is:

SET_RESPONSE_ERROR_CHECK(<enable> IN BOOLEAN DEFAULT FALSE)

Parameters

enable

Set enable to TRUE if HTTP 4xx and 5xx status codes are to be treated as errors, otherwise set to FALSE. The default is FALSE.

SET_TRANSFER_TIMEOUT¶

The SET_TRANSFER_TIMEOUT procedure sets the default, transfer timeout setting for waiting for a response from an HTTP request. This procedure has two signatures:

SET_TRANSFER_TIMEOUT(<timeout> IN INTEGER DEFAULT 60)

and

SET_TRANSFER_TIMEOUT(<r> IN OUT UTL_HTTP.REQ, <timeout> IN
INTEGER DEFAULT 60)

Use the second form to change the transfer timeout setting for an individual request that a request inherits from the session default settings.

Parameters

r

r is the HTTP request record.

timeout

timeout is the transfer timeout setting in seconds for HTTP requests. The default is 60 seconds.

WRITE_LINE¶

The WRITE_LINE procedure writes data to the HTTP request body in text form; the text is terminated with a CRLF character pair. The signature is:

WRITE_LINE(<r> IN OUT UTL_HTTP.REQ, <data> IN VARCHAR2)

Parameters

r

r is the HTTP request record.

data

data is the request body in TEXT form.

Example

The following example writes data (Account balance $500.00) in text form to the request body to be sent using the HTTP POST method. The data is sent to a hypothetical web application (post.php) that accepts and processes data.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('http://www.example.com/post.php',
       'POST');
    UTL_HTTP.SET_HEADER(v_req, 'Content-Length', '23');
    UTL_HTTP.WRITE_LINE(v_req, 'Account balance $500.00');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    DBMS_OUTPUT.PUT_LINE('Status Code: ' || v_resp.status_code);
    DBMS_OUTPUT.PUT_LINE('Reason Phrase: ' || v_resp.reason_phrase);
    UTL_HTTP.END_RESPONSE(v_resp);
END;

Assuming the web application successfully processed the POST method, the following output would be displayed:

Status Code: 200
Reason Phrase: OK

WRITE_RAW¶

The WRITE_RAW procedure writes data to the HTTP request body in binary form. The signature is:

WRITE_RAW(<r> IN OUT UTL_HTTP.REQ, <data> IN RAW)

Parameters

r

r is the HTTP request record.

data

data is the request body in binary form.

Example

The following example writes data in binary form to the request body to be sent using the HTTP POST method to a hypothetical web application that accepts and processes such data.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('http://www.example.com/post.php',
       'POST');
    UTL_HTTP.SET_HEADER(v_req, 'Content-Length', '23');
    UTL_HTTP.WRITE_RAW(v_req, HEXTORAW
('54657374696e6720504f5354206d6574686f6420696e20485454502072657175657374'));
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    DBMS_OUTPUT.PUT_LINE('Status Code: ' || v_resp.status_code);
    DBMS_OUTPUT.PUT_LINE('Reason Phrase: ' || v_resp.reason_phrase);
    UTL_HTTP.END_RESPONSE(v_resp);
END;

The text string shown in the HEXTORAW function is the hexadecimal translation of the text Testing POST method in HTTP request.

Assuming the web application successfully processed the POST method, the following output would be displayed:

Status Code: 200
Reason Phrase: OK

WRITE_TEXT¶

The WRITE_TEXT procedure writes data to the HTTP request body in text form. The signature is:

WRITE_TEXT(<r> IN OUT UTL_HTTP.REQ, <data> IN VARCHAR2)

Parameters

r

r is the HTTP request record.

data

data is the request body in text form.

Example

The following example writes data (Account balance $500.00) in text form to the request body to be sent using the HTTP POST method. The data is sent to a hypothetical web application (post.php) that accepts and processes data.

DECLARE
    v_req           UTL_HTTP.REQ;
    v_resp          UTL_HTTP.RESP;
BEGIN
    v_req := UTL_HTTP.BEGIN_REQUEST('http://www.example.com/post.php',
       'POST');
    UTL_HTTP.SET_HEADER(v_req, 'Content-Length', '23');
    UTL_HTTP.WRITE_TEXT(v_req, 'Account balance $500.00');
    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    DBMS_OUTPUT.PUT_LINE('Status Code: ' || v_resp.status_code);
    DBMS_OUTPUT.PUT_LINE('Reason Phrase: ' || v_resp.reason_phrase);
    UTL_HTTP.END_RESPONSE(v_resp);
END;

Assuming the web application successfully processed the POST method, the following output would be displayed:

Status Code: 200
Reason Phrase: OK

END_OF_BODY¶

The END_OF_BODY exception will be raised when it reaches the end of the HTTP response body.

Example

The following example handles the exception and writes Exception caught in text form to the request body to be sent using the HTTP POST method. The data is sent to a hypothetical web application (post.php) that accepts and processes data.

DECLARE
    req UTL_HTTP.REQ;
    resp UTL_HTTP.RESP;
    value VARCHAR2(32768);
BEGIN
    req := UTL_HTTP.BEGIN_REQUEST('https://www.google.com/');
    resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);
END LOOP;
    UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
        DBMS_OUTPUT.PUT_LINE('Exception caught');
        UTL_HTTP.END_RESPONSE(resp);
END;

Assuming the web application successfully processed the POST method, the following output will be displayed:

Output is:
Exception caught

UTL_HTTP¶

HTML_PIECES¶

REQ¶

RESP¶

BEGIN_REQUEST¶

END_REQUEST¶

END_RESPONSE¶

GET_BODY_CHARSET¶

GET_FOLLOW_REDIRECT¶

GET_HEADER¶

GET_HEADER_BY_NAME¶

GET_HEADER_COUNT¶

GET_RESPONSE¶

GET_RESPONSE_ERROR_CHECK¶

GET_TRANSFER_TIMEOUT¶

READ_LINE¶

READ_RAW¶

READ_TEXT¶

REQUEST¶

REQUEST_PIECES¶

SET_BODY_CHARSET¶

SET_FOLLOW_REDIRECT¶

SET_HEADER¶

SET_RESPONSE_ERROR_CHECK¶

SET_TRANSFER_TIMEOUT¶

WRITE_LINE¶

WRITE_RAW¶

WRITE_TEXT¶

END_OF_BODY¶

Previous topic

Next topic

Table of Contents