Oracle UTL_HTTP Package

In terms of scale, the documentation behind the UTL_HTTP package is monstrous compared to many other packages. Given the huge amount of data under the covers of a Web page, it is not surprising that the number of subprograms for this package would be quite numerous.

What is a quick and dirty use of UTL_HTTP? The simplest and fastest example is one's ability to capture the source of a Web page by using PL/SQL. Coupled with spooling the output, one now has a simple means of capturing the source of virtually any Web page.

If the developer can spool to a file, she can load the file into the database. If there is a BLOB in the database, write it out to the file system. This workflow should bring to mind the idea of creating a rudimentary source control system. Not only can the developer store and generate HTML files based on a combination of UTL_FILE and UTL_HTTP, but they can be edited as well.

There are applications where the HTML for Web pages served in a framework is stored in the database. How those pages render is slightly different than creating HTML files on the file system, but the concept is the same - the database serves up the source for a page. So look at a quick example of capturing the source for a page. Use the home page of dba-oracle.com. This small body of code will generate almost 900 lines of HTML code and uses eight subprograms in the UTL_HTTP package.

Here is the top part of the captured HTML source.

Any Web page that can be accessed via the browser, minus those requiring authentication, can be captured. With this ability, one can sample pages and check to see if there are any differences. But what about HTTPS pages? For these pages, one needs to configure Wallet Manager.

On Windows, this can be found under Start > Programs > the Oracle home > Integrated Management Tools > Wallet Manager, and in UNIX, use one found in $ORACLE_HOME/bin. Use of Wallet Manager enables the developer to capture secure Web pages, secure in the sense that this is what one is allowed to access via the wallet information.

If the returned page is large, use the REQUEST_PIECES function. This function returns a PL/SQL table of 2000 bytes. In the prior example, how many pieces were returned and what was the length? Using the example in Oracle's documentation, and correcting the error for the length output (use "len," not "i" in the last DBMS_OUTPUT), the following occurs:

The output using this against www.dba-oracle.com is 22 pieces with length 43086, which is quite different than what is shown in Oracle's documentation (4 pieces and length 7687).

-------------------------------------------------------------------------

This UTL_HTTP (Hyper Text Transfer Protocol) package is available from the Oracle version 9.2 for accessing data on the internet using HTTP. This package also fetches the internet data using HTTP over Secured Socket Layer (SSL), also known as HTTPS. For the HTTPS fetches, the SSL client authentication has to be performed by sending the client certificate in a wallet to the remote website.

The different objects available in this package are described below.

REQ Type

This type is used for representing an HTTP request. This type accepts the information returned by the BEGIN_REQUEST function in READ ONLY mode. Thus, changing the type value does not have any impact on the request.

The prototype of the REQ type is shown below,

·         URL parameter holds the URL of the HTTP request.

·         METHOD parameter holds the method to be used on the resource identified by the HTTP request.

·         HTTP_VERSION parameter holds the HTTP version used during the HTTP request.

BEGIN_REQUEST Function

This function begins a new HTTP request by establishing a network connection between the PL/SQL program and the target website meanwhile sending the request line. This function’s return value is set to the REQ type for continuing the request.

The prototype of the BEGIN_REQUEST function is shown below,

UTL_HTTP RESP Type

This type is used for representing an HTTP response. This type accepts the information returned by the GET_REQUEST function in READ ONLY mode. Thus, changing the type value does not have any impact on the response.

·         STATUS_CODE parameter accepts a three-digit status code indicating the results of the HTTP request.

·         REASON_PHRASE parameter accepts the short message describing the status code returned by the HTTP request.

UTL_HTTP GET_RESPONSE Function

This function reads the HTTP response accepting the request as its input. The status line and the response headers are read right before the function’s return into the RESP type, completing the header section.

The prototype of the GET_RESPONSE function is shown below,

·         R parameter denotes the HTTP response.

UTL_HTTP SET_HEADER Procedure

This procedure is used for setting the header section for an HTTP request. We can also set multiple headers of the same name for a request as the duplicate header name will not replace the existing headers of the same name.

The prototype for defining the SET_HEADER procedure is shown below,

·         R parameter accepts the HTTP request made.

·         NAME parameter accepts a user-defined name in the request header.

·         VALUE parameter accepts a user-defined value for the request header.

In the below example, the BEGIN_REQUEST function accepts the URL of GOOGLE website and begins the HTTP request which is then assigned to the REQ type. This REQ type variable is then passed to the SET_HEADER procedure for setting a user-defined header. After the header is added, the response for the request is received and stored in the RESP type using the GET_RESPONSE function. The GET_HEADER_COUNT function returns the number of HTTP response headers returned with the response. It is then looped for the number of times to the GET_HEADER function to retrieve the header name and its value.

After retrieving the response, it is then looped using the READ_LINE procedure and buffers it using the PUT_LINE procedure. The READ_LINE procedure accepts the response type variable and the output variable which holds the HTTP response body in the text format. The third Boolean parameter omits the newline character when setting to true or is included in case of false.

Once the response body reaches its end, the END_OF_BODY exception is thrown, which is handled separately to avoid sudden termination of the program. After the response body is buffered out, the request and the response is completed by using the END_RESPONSE procedure.

Result:

Request URL: http://www.google.com

Request Method: GET

Request Version:

Response Status Code: 200

Response Reason: OK

Response Version: HTTP/1.1

---Header Count Starts---

Date: Mon, 05 Sep 2016 17:39:14 GMT

Expires: -1

Cache-Control: private, max-age=0

Content-Type: text/html; charset=ISO-8859-1

Server: gws

X-XSS-Protection: 1; mode=block

---Header Count Ends---

<HTML File for the GOOGLE webpage>

X-Frame-Options: SAMEORIGIN

Accept-Ranges: none

Vary: Accept-Encoding

Connection: close

HTTP Cookies

HTTP cookies are a small amount of data sent by the website and stored in the user’s browser while the user is browsing. These are originally designed for remembering the basic state of the user’s browsing activity such as pages visited, items added to the cart while e-shopping, form fields fill-up, etc. The term is coined from the fortune cookies, a cookie with an embedded message.

The COOKIE record-type represents an HTTP cookie and the COOKIE_TABLE index by table represents a set of HTTP cookies.

The prototype of the COOKIE and the COOKIE_TABLE type is shown below,

·         NAME parameter holds the name of the cookie.

·         VALUE parameter holds the value of the cookie.

·         DOMAIN parameter holds the domain for which the cookie is valid.

·         EXPIRE parameter holds the expiry time for the cookie.

·         PATH parameter holds the subset of URLs for which the cookie is available.

·         SECURE parameter holds the Boolean for whether the cookie should be returned securely to the web server or not.

·         VERSION parameter holds the version of the HTTP cookie specification.

·         COMMENT parameter holds the description about the HTTP cookie.

These cookies are maintained by the UTL_HTTP package transparently lasting for the duration of the session and are not usually changed by the PL/SQL programs. When we want to maintain the cookies beyond their lifetime, we must download them into the database tables using the GET_COOKIES procedure and use them for the next database session using the ADD_COOKIES procedure. We must capture all the information on the cookie (Except its comment) mandatorily for the cookie to function properly. We must also ensure that we do not change the cookie information as it may result in application failure.

GET_COOKIES Procedure

This procedure returns all the cookies currently maintained by the UTL_HTTP package set by all the web servers.

The prototype for defining the GET_COOKIES procedure is shown below,

·         COOKIES parameter returns all the cookies available.

ADD_COOKIES Procedure

This procedure adds all the cookies that are currently maintained by the UTL_HTTP package.

The prototype for defining the ADD_COOKIES procedure is shown below,

·         COOKIES parameter adds all the cookies available.

In the below example, cookies from one session have been preserved and used for another session. Firstly, the MY_COOKIE_TABLE with the attributes of the type UTL_HTTP.COOKIE_TABLE is created as shown below. Note that the COMMENT attribute is not included as it is not mandatory.

Then, the cookies from one session are copied into this table using the below anonymous block. In the below example, the cookies transparently stored in the UTL_HTTP package are retrieved using the GET_COOKIES procedure and is assigned to the COOKIE_TABLE type variable. It is then looped for every cookie value and then inserted into our MY_COOKIE_TABLE permanently. Note that the attribute SECURE is converted to Y for true and N for false values of the cookie information.

When we query the MY_COOKIE_TABLE after executing the above anonymous block, we are able to see around 5 cookies being downloaded and readily available for adding to any other session.

After copying the cookies, we can add them to any session by using the below anonymous block. The MY_COOKIE_TABLE with all the cookies is copied into a COOKIE_TABLE type variable and is called as input for the ADD_COOKIES procedure. Before that, we can either delete all the existing cookies using the CLEAR_COOKIES procedure or add them up to the existing cookies. The total cookies available in the UTL_HTTP package state can be retrieved using the GET_COOKIE_COUNT function.

Result:

Total Cookie Count: 5

REQUEST_CONTEXT_KEY Subtype

This subtype creates a unique PLS_INTEGER key for representing a request context. A request context is a context that holds wallet information and a cookie table for private HTTP request and response. This wallet and the cookie table will not be shared with any other application in the same database session.

The prototype of this type is shown below,

CREATE_REQUEST_CONTEXT Function

This function is used for creating a request context. A request context holds a wallet information and a cookie table for making a private HTTP request without sharing it with any other application in the same database session.

The prototype for defining this function is shown below,

·         WALLET_PATH parameter accepts the directory path containing the wallet.

·         WALLET_PASSWORD parameter accepts the password needed for opening the wallet.

·         ENABLE_COOKIES parameter is used for enabling (Set True) and disabling (Set False) the cookie support for the HTTP request.

·         MAX_COOKIES parameter sets the maximum cookie limit to be maintained during this HTTP request context.

·         MAX_COOKIES_PER_SITE parameter sets the maximum cookie limit to be maintained for each site during this HTTP request context.

DESTROY_REQUEST_CONTEXT Procedure

This procedure is used for destroying a request context. Note that a request context cannot be destroyed when it is in use by an HTTP request or a response.

The prototype for defining this procedure is shown below,

·         Request_context parameter accepts a request context that has to be destroyed.

In the below example, a request context is created with wallet and cookie information. After the context creation, an HTTP request and its corresponding response are triggered for the user passed web URL which we have discussed in our previous sections. After the HTML file is retrieved, the request context is destroyed.

To know more about the wallet creation and its functioning, please refer to the Transparent Data Encryption section in Chapter 8: The Advanced Security Methods in PL/SQL.

Result:

Request Context Key: 1471220475

<HTML File for the webpage>

Simple HTTP fetches

The functions REQUEST and REQUEST_PIECES accept a URL as its parameter and contacts the website for returning the data obtained in HTML format.

REQUEST Function

This function returns the information up to the first 2000 bytes obtained from the input URL. This function can be called in a SELECT statement for a quick call out of small websites.

The prototype for defining this function is shown below,

·         URL parameter accepts an input URL for the data fetch.

·         PROXY parameter accepts the proxy server to use during an HTTP request.

·         WALLET_PATH parameter accepts the directory path for the wallet.

·         WALLET_PASSWORD parameter accepts the password for opening the wallet.

In the below example, a URL is passed as input to the REQUEST function returning the first 2000 bytes of information obtained from the website.

Result:

<2000 bytes of the HTML file are obtained from the input URL>

REQUEST_PIECES Function

This function returns the complete information of the input URL in the form of PL/SQL associative array having a maximum of 2000 byte. This column has an extra attribute compared to the REQUEST function for passing the maximum pieces allowed during the HTML file split up.

This function cannot be used in a SELECT statement as it returns an associative array. Also, the TABLE function cannot be used upon this function’s associative array return statement w.r.t. the 12c enhancement as the MAX_PIECES parameter uses a non-SQL datatype.

The prototype of the HTML_PIECES type and the REQUEST_PIECES function is shown below,

·         MAX_PIECES parameter accepts a positive integer value for determining the maximum number of chunks the obtained HTML must be split into.

In the below example, the REQUEST_PIECES function requests for a maximum of 100 chunks of 2000-bytes information from the input URL.

This function’s return statement is then looped to print all the available chunks of 2000 bytes (The last chunk may contain <=2000 bytes).

Result:

Total chunks: 24

Chunk #1: <HTML file of 2000 bytes>

Chunk #2: <HTML file of 2000 bytes>

Chunk #3: <HTML file of 2000 bytes>

...

...

Chunk #24: <HTML file of <=2000 bytes>