UTL_HTTP v15

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

Function/procedureReturn typeDescription
BEGIN_REQUEST(url, method, http_version)UTL_HTTP.REQInitiates a new HTTP request.
END_REQUEST(r IN OUT)n/aEnds an HTTP request before allowing it to complete.
END_RESPONSE(r IN OUT)n/aEnds the HTTP response.
END_OF_BODY(r IN OUT)n/aEnds package body.
GET_BODY_CHARSETVARCHAR2Returns the default character set of the body of future HTTP requests.
GET_BODY_CHARSET(charset OUT)n/aReturns the default character set of the body of future HTTP requests.
GET_FOLLOW_REDIRECT(max_redirects OUT)n/aCurrent setting for the maximum number of redirections allowed.
GET_HEADER(r IN OUT, n, name OUT, value OUT)n/aReturns the nth header of the HTTP response.
GET_HEADER_BY_NAME(r IN OUT, name, value OUT, n)n/aReturns the HTTP response header for the specified name.
GET_HEADER_COUNT(r IN OUT)INTEGERReturns the number of HTTP response headers.
GET_RESPONSE(r IN OUT)UTL_HTTP.RESPReturns the HTTP response.
GET_RESPONSE_ERROR_CHECK(enable OUT)n/aReturns whether or not response error check is set.
GET_TRANSFER_TIMEOUT(timeout OUT)n/aReturns the transfer timeout setting for HTTP requests.
READ_LINE(r IN OUT, data OUT, remove_crlf)n/aReturns the HTTP response body in text form until the end of line.
READ_RAW(r IN OUT, data OUT, len)n/aReturns the HTTP response body in binary form for a specified number of bytes.
READ_TEXT(r IN OUT, data OUT, len)n/aReturns the HTTP response body in text form for a specified number of characters.
REQUEST(url)VARCHAR2Returns the content of a web page.
REQUEST_PIECES(url, max_pieces)UTL_HTTP. HTML_PIECESReturns a table of 2000-byte segments retrieved from an URL.
SET_BODY_CHARSET(charset)n/aSets the default character set of the body of future HTTP requests.
SET_FOLLOW_REDIRECT(max_redirects)n/aSets the maximum number of times to follow the redirect instruction.
SET_FOLLOW_REDIRECT(r IN OUT, max_redirects)n/aSets the maximum number of times to follow the redirect instruction for an individual request.
SET_HEADER(r IN OUT, name, value)n/aSets the HTTP request header.
SET_RESPONSE_ERROR_CHECK(enable)n/aDetermines whether to treat HTTP 4xx and 5xx status codes as errors.
SET_TRANSFER_TIMEOUT(timeout)n/aSets the default transfer timeout value for HTTP requests.
SET_TRANSFER_TIMEOUT(r IN OUT, timeout)n/aSets the transfer timeout value for an individual HTTP request.
WRITE_LINE(r IN OUT, data)n/aWrites CRLF-terminated data to the HTTP request body in TEXT form.
WRITE_RAW(r IN OUT, data)n/aWrites data to the HTTP request body in BINARY form.
WRITE_TEXT(r IN OUT, data)n/aWrites data to the HTTP request body in TEXT form.

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

Note

In EDB Postgres Advanced Server, an HTTP 4xx or HTTP 5xx response produces a database error. In Oracle, you can configure this option. It is FALSE by default.

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

EDB Postgres Advanced Server ignores all cookies it receives.

The UTL_HTTP exceptions that can be raised in Oracle aren't recognized by EDB Postgres Advanced Server. In addition, the error codes returned by EDB Postgres Advanced Server aren't 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 codeCondition nameDescriptionRaised where
-29266END_OF_BODYThe end of HTTP response body is reachedREAD_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.

Various public constants are 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_0CONSTANT VARCHAR2(64) := 'HTTP/1.0';
HTTP_VERSION_1_1CONSTANT VARCHAR2(64) := 'HTTP/1.1';
Standard port assignments
DEFAULT_HTTP_PORTCONSTANT INTEGER := 80;
DEFAULT_HTTPS_PORTCONSTANT INTEGER := 443;

The following table contains UTL_HTTP public status code constants.

1XX Informational
HTTP_CONTINUECONSTANT INTEGER := 100;
HTTP_SWITCHING_PROTOCOLSCONSTANT INTEGER := 101;
HTTP_PROCESSINGCONSTANT INTEGER := 102;
2XX success
HTTP_OKCONSTANT INTEGER := 200;
HTTP_CREATEDCONSTANT INTEGER := 201;
HTTP_ACCEPTEDCONSTANT INTEGER := 202;
HTTP_NON_AUTHORITATIVE_INFOCONSTANT INTEGER := 203;
HTTP_NO_CONTENTCONSTANT INTEGER := 204;
HTTP_RESET_CONTENTCONSTANT INTEGER := 205;
HTTP_PARTIAL_CONTENTCONSTANT INTEGER := 206;
HTTP_MULTI_STATUSCONSTANT INTEGER := 207;
HTTP_ALREADY_REPORTEDCONSTANT INTEGER := 208;
HTTP_IM_USEDCONSTANT INTEGER := 226;
3XX redirection
HTTP_MULTIPLE_CHOICESCONSTANT INTEGER := 300;
HTTP_MOVED_PERMANENTLYCONSTANT INTEGER := 301;
HTTP_FOUNDCONSTANT INTEGER := 302;
HTTP_SEE_OTHERCONSTANT INTEGER := 303;
HTTP_NOT_MODIFIEDCONSTANT INTEGER := 304;
HTTP_USE_PROXYCONSTANT INTEGER := 305;
HTTP_SWITCH_PROXYCONSTANT INTEGER := 306;
HTTP_TEMPORARY_REDIRECTCONSTANT INTEGER := 307;
HTTP_PERMANENT_REDIRECTCONSTANT INTEGER := 308;
4XX client error
HTTP_BAD_REQUESTCONSTANT INTEGER := 400;
HTTP_UNAUTHORIZEDCONSTANT INTEGER := 401;
HTTP_PAYMENT_REQUIREDCONSTANT INTEGER := 402;
HTTP_FORBIDDENCONSTANT INTEGER := 403;
HTTP_NOT_FOUNDCONSTANT INTEGER := 404;
HTTP_METHOD_NOT_ALLOWEDCONSTANT INTEGER := 405;
HTTP_NOT_ACCEPTABLECONSTANT INTEGER := 406;
HTTP_PROXY_AUTH_REQUIREDCONSTANT INTEGER := 407;
HTTP_REQUEST_TIME_OUTCONSTANT INTEGER := 408;
HTTP_CONFLICTCONSTANT INTEGER := 409;
HTTP_GONECONSTANT INTEGER := 410;
HTTP_LENGTH_REQUIREDCONSTANT INTEGER := 411;
HTTP_PRECONDITION_FAILEDCONSTANT INTEGER := 412;
HTTP_REQUEST_ENTITY_TOO_LARGECONSTANT INTEGER := 413;
HTTP_REQUEST_URI_TOO_LARGECONSTANT INTEGER := 414;
HTTP_UNSUPPORTED_MEDIA_TYPECONSTANT INTEGER := 415;
HTTP_REQ_RANGE_NOT_SATISFIABLECONSTANT INTEGER := 416;
HTTP_EXPECTATION_FAILEDCONSTANT INTEGER := 417;
HTTP_I_AM_A_TEAPOTCONSTANT INTEGER := 418;
HTTP_AUTHENTICATION_TIME_OUTCONSTANT INTEGER := 419;
HTTP_ENHANCE_YOUR_CALMCONSTANT INTEGER := 420;
HTTP_UNPROCESSABLE_ENTITYCONSTANT INTEGER := 422;
HTTP_LOCKEDCONSTANT INTEGER := 423;
HTTP_FAILED_DEPENDENCYCONSTANT INTEGER := 424;
HTTP_UNORDERED_COLLECTIONCONSTANT INTEGER := 425;
HTTP_UPGRADE_REQUIREDCONSTANT INTEGER := 426;
HTTP_PRECONDITION_REQUIREDCONSTANT INTEGER := 428;
HTTP_TOO_MANY_REQUESTSCONSTANT INTEGER := 429;
HTTP_REQUEST_HEADER_FIELDS_TOO_LARGECONSTANT INTEGER := 431;
HTTP_NO_RESPONSECONSTANT INTEGER := 444;
HTTP_RETRY_WITHCONSTANT INTEGER := 449;
HTTP_BLOCKED_BY_WINDOWS_PARENTAL_CONTROLSCONSTANT INTEGER := 450;
HTTP_REDIRECTCONSTANT INTEGER := 451;
HTTP_REQUEST_HEADER_TOO_LARGECONSTANT INTEGER := 494;
HTTP_CERT_ERRORCONSTANT INTEGER := 495;
HTTP_NO_CERTCONSTANT INTEGER := 496;
HTTP_HTTP_TO_HTTPSCONSTANT INTEGER := 497;
HTTP_CLIENT_CLOSED_REQUESTCONSTANT INTEGER := 499;
5XX server error
HTTP_INTERNAL_SERVER_ERRORCONSTANT INTEGER := 500;
HTTP_NOT_IMPLEMENTEDCONSTANT INTEGER := 501;
HTTP_BAD_GATEWAYCONSTANT INTEGER := 502;
HTTP_SERVICE_UNAVAILABLECONSTANT INTEGER := 503;
HTTP_GATEWAY_TIME_OUTCONSTANT INTEGER := 504;
HTTP_VERSION_NOT_SUPPORTEDCONSTANT INTEGER := 505;
HTTP_VARIANT_ALSO_NEGOTIATESCONSTANT INTEGER := 506;
HTTP_INSUFFICIENT_STORAGECONSTANT INTEGER := 507;
HTTP_LOOP_DETECTEDCONSTANT INTEGER := 508;
HTTP_BANDWIDTH_LIMIT_EXCEEDEDCONSTANT INTEGER := 509;
HTTP_NOT_EXTENDEDCONSTANT INTEGER := 510;
HTTP_NETWORK_AUTHENTICATION_REQUIREDCONSTANT INTEGER := 511;
HTTP_NETWORK_READ_TIME_OUT_ERRORCONSTANT INTEGER := 598;
HTTP_NETWORK_CONNECT_TIME_OUT_ERRORCONSTANT 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 returns content.

method

method is the HTTP method to use. The default is GET.

http_version

http_version is the HTTP protocol version sending the request. Specify either HTTP/1.0 or HTTP/1.1. The default is null. In that 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, and 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

This example shows the use of the GET_BODY_CHARSET function.

edb=# SELECT UTL_HTTP.GET_BODY_CHARSET() FROM DUAL;
Output
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 the 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

This example retrieves the header count and 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 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. The default is FALSE.

Examples

This 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.

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 return.

Examples

This 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.

Output
\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 return.

Examples

This 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:

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 URL. The signature is:

REQUEST(<url> IN VARCHAR2) RETURN VARCHAR2

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

Parameters

url

url is the Uniform Resource Locator from which UTL_HTTP returns content.

Example

This 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 a 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 returns content.

max_pieces

max_pieces specifies the maximum number of 2000-byte segments that the REQUEST_PIECES function returns. If max_pieces specifies more units than are available at the specified URL, the final unit contains fewer bytes.

Example

This 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, meaning the database character set is assumed.

SET_FOLLOW_REDIRECT

The SET_FOLLOW_REDIRECT procedure sets the maximum number of times to follow the HTTP redirect instruction 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 the 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 to interpret HTTP 4xx and 5xx status codes returned by the GET_RESPONSE function as errors. The signature is:

SET_RESPONSE_ERROR_CHECK(<enable> IN BOOLEAN DEFAULT FALSE)

Parameters

enable

Set enable to TRUE if you want to treat HTTP 4xx and 5xx status codes as errors. 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

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

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 is displayed:

Output
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

This example writes data in binary form to the request body to send using the HTTP POST method to a 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.

When the web application successfully processes the POST method, the following output is displayed:

Output
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

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

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;

When the web application successfully processes the POST method, the following output is displayed:

Output
Status Code: 200
Reason Phrase: OK

END_OF_BODY

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

Example

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

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;

When the web application successfully processes the POST method, the following output is displayed:

Output
Output is:
Exception caught