UTL_HTTP v11

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:

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.

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.

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.

The following table contains UTL_HTTP public status code constants.

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 EnterpriseDB 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 EnterpriseDB 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

• On this page
• 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