Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle UTL_HTTP Package

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

UTL_HTTP contains two functions; specifically, REQUEST and REQUEST_LINES. REQUEST is a function that returns a string variable (4000 character maximum). REQUEST will return a string that contains up to the first 4000 bytes of the HTML result returned from the HTTP request to a specific URL. In other words, if more than 4000 characters are in the resulting HTML received, characters 4001 and beyond will be truncated.

According to the package specification: Package UTL_HTTP contains function REQUEST for making HTTP callouts from PL/SQL programs. Function UTL_HTTP.REQUEST may not be called directly from an SQL statement (because its return type is not supported in a query); however, it may be called from the body of another function that is called directly from an SQL statement (and that returns a type that is legal for a function called from SQL; that is, CHAR).

The input parameters to the REQUEST function are URL and PROXY. URL is a varchar2 string that will contain the URL to be called. PROXY contains the IP address or domain name for the proxy server; the default is NULL (meaning there is no proxy server).

The REQUEST_PIECES function also calls a URL and can accept a PROXY server value. However, REQUEST_PIECES returns a PL/SQL table (array structure) of varchar2 strings containing the entire site's HTML output (or at least up to 32,767 times 4000 characters of the page).

According to the UTL_HTTP specification, the function REQUEST_LINES takes a URL as its argument and returns a PL/SQL-table of strings, which are the successive pieces of the HTML response obtained from the HTTP request to that URL.

REQUEST_LINES accepts three parameters: URL, MAX_PIECES, and PROXY. URL and PROXY are described above. MAX_PIECES represents the maximum number of "pieces" to return. The default value is 32,767.

The UTL_HTTP package also contains a data-type of HTML_PIECES, which is a varchar2 PL/SQL table. This datatype should be used in conjunction with the REQUEST_LINES function.

REQUEST and REQUEST_LINES are typically just called with a URL (unless you have a proxy server) as follows:

      REQUEST
      declare html_results
      varchar2(4000); -- max is 2000 for v7
      begin
         html_results := utl_http.request
         ('http://www.tusc.com');
         .... -- process html_results
      end;
 
      REQUEST_LINES
      declare pieces
      utl_http.html_pieces;
         begin
            pieces := utl_http.request_
            lines('http://www.tusc.com');
            for i in 1 .. pieces.count
      loop
         ... -- process each piece
         ... -- refer to each piece as pieces(i)
         end loop;

      end;

Some things worth noting include:

1. Although it seems repetitious, http:// (the protocol) is specified in the URL.

2. Note the use of the PL/SQL-table method COUNT to discover the number of pieces returned, which may be zero or more.

3. When using REQUEST_LINES, MAX_PIECES is optional. It is the maximum number of pieces (each 4000 characters in length except for the last, which may be shorter) that REQUEST should return. If provided, that argument should be a positive integer.

*******************************************************

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.

 

spool get_page.html

DECLARE

 req   UTL_HTTP.REQ;

 resp  UTL_HTTP.RESP;

 value VARCHAR2(1024);

BEGIN

  req := UTL_HTTP.BEGIN_REQUEST('http://dba-oracle.com');

  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

  resp := UTL_HTTP.GET_RESPONSE(req);

  LOOP

    UTL_HTTP.READ_LINE(resp, value, TRUE);

    dbms_output.put_line(value);

  END LOOP;

  UTL_HTTP.END_RESPONSE(resp);

EXCEPTION

  WHEN UTL_HTTP.END_OF_BODY THEN

    UTL_HTTP.END_RESPONSE(resp);

END;

/

spool off

 

Here is the top part of the captured HTML source.

 

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">

<html>

<head>

<META name="verify-v1" content="03pBD3fe1Hr9cZGVzdKBKWKwK7myXtC2l7tXPLFZzbI=" />

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<meta name="GENERATOR" content="Microsoft FrontPage 6.0">

<title>Oracle Consulting, Oracle Support and Oracle Training by BC</title>

<meta name="keywords" content="Oracle Consulting, Oracle support, Oracle Consultants,Oracle Education,Oracle

contracting,Oracle consulting,Oracle consultant,Oracle Training,Oracle dba support,Oracle architecture,Oracle

classes,Oracle,Oracle architecture,Oracle tuning, Oracle data warehousing">

<meta name="description" content="Burleson Oracle consulting, Oracle training and Oracle support">

</head>

 

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:

 

DECLARE

   x   UTL_HTTP.HTML_PIECES;

   len PLS_INTEGER;

BEGIN

   x := UTL_HTTP.REQUEST_PIECES('http://dba-oracle.com/', 100);

   DBMS_OUTPUT.PUT_LINE(x.count || ' pieces were retrieved.');

   DBMS_OUTPUT.PUT_LINE('with total length ');

   IF x.count < 1 THEN

      DBMS_OUTPUT.PUT_LINE('0');

  ELSE

   len := 0;

   FOR i in 1..x.count LOOP

      len := len + length(x(i));

   END LOOP;

   DBMS_OUTPUT.PUT_LINE(len);

  END IF;

END;

/

 

23 pieces were retrieved.

with total length

44356

 

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,

 

TYPE req IS RECORD (

   url           VARCHAR2(32767),

   method        VARCHAR2(64),

   http_version  VARCHAR2(64));

 

         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.

 

% Note: All the above parameters are set only after the request is created using the BEGIN_REQUEST function.

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.BEGIN_REQUEST (

   url           IN VARCHAR2,

   method        IN VARCHAR2 DEFAULT 'GET',

   http_version  IN VARCHAR2 DEFAULT NULL)

RETURN req;

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.

 

TYPE resp IS RECORD (

   status_code    PLS_INTEGER,

   reason_phrase  VARCHAR2(256),

   http_version   VARCHAR2(64));

 

         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.

 

% Note: All these parameters are set after the response is processed by the GET_RESPONSE type.

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,

 

UTL_HTTP.GET_RESPONSE (

   r IN OUT NOCOPY req)

RETURN resp;

 

         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.

 

% Note: If the request is made using the HTTP 1.1 version, the header is automatically set.

The prototype for defining the SET_HEADER procedure is shown below,

 

UTL_HTTP.SET_HEADER (

   r IN OUT NOCOPY req,

   name IN VARCHAR2,

   value IN VARCHAR2);

 

         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.

 

DECLARE

  l_http_req UTL_HTTP.REQ;

  l_http_resp UTL_HTTP.RESP;

  l_vc_header_name varchar2(256);

  l_vc_header_value varchar2(1024);                   

  l_vc_html VARCHAR2(32767);

BEGIN

  l_http_req := UTL_HTTP.BEGIN_REQUEST('http://www.google.com');

  dbms_output.put_line('Request URL: '||l_http_req.url);

  dbms_output.put_line('Request Method: '||l_http_req.method);

  dbms_output.put_line('Request Version: '||l_http_req.http_version);

  UTL_HTTP.SET_HEADER(l_http_req, 'Header #1', 'Chrome V.52.X');

  l_http_resp := UTL_HTTP.GET_RESPONSE(l_http_req);

  dbms_output.put_line('Response Status Code: '||l_http_resp.status_code);

  dbms_output.put_line('Response Reason: '||l_http_resp.reason_phrase);

  dbms_output.put_line('Response Version: '||l_http_resp.http_version);

  dbms_output.put_line('---Header Count Starts---');

  FOR loop_hc IN 1..UTL_HTTP.GET_HEADER_COUNT(l_http_resp)

  LOOP

    UTL_HTTP.GET_HEADER(l_http_resp, loop_hc, l_vc_header_name, l_vc_header_value);

    DBMS_OUTPUT.PUT_LINE(l_vc_header_name || ': ' || l_vc_header_value);

  END LOOP loop_hc;

  dbms_output.put_line('---Header Count Ends---');

  LOOP

    UTL_HTTP.READ_LINE(l_http_resp, l_vc_html, TRUE);

    DBMS_OUTPUT.PUT_LINE(l_vc_html);

  END LOOP;

  UTL_HTTP.END_RESPONSE(l_http_resp);

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

  UTL_HTTP.END_RESPONSE(l_http_resp);

END;

/

 

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,

 

TYPE cookie IS RECORD (

   name  VARCHAR2(256),

   value  VARCHAR2(1024),

   domain  VARCHAR2(256),

   expire  TIMESTAMP WITH TIME ZONE,

   path  VARCHAR2(1024),

   secure  BOOLEAN,

   version  PLS_INTEGER,

   comment  VARCHAR2(1024));

 

TYPE cookie_table IS TABLE OF cookie INDEX BY binary_integer;

 

         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,

 

UTL_HTTP.GET_COOKIES (cookies IN OUT NOCOPY cookie_table);

 

         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,

 

UTL_HTTP.ADD_COOKIES (cookies IN cookie_table);

 

         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.

 

CREATE

  TABLE my_cookie_table

  (

    name    VARCHAR2(256),

    value   VARCHAR2(1024),

    domain  VARCHAR2(256),

    expire  DATE,

    path    VARCHAR2(1024),

    secure  VARCHAR2(1),

    version INTEGER

  );

 

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.

 

DECLARE

  l_ct_cookies UTL_HTTP.COOKIE_TABLE;

  l_vc_secure VARCHAR2(1);

BEGIN

  UTL_HTTP.GET_COOKIES(l_ct_cookies);

  FOR loop_ct IN 1..l_ct_cookies.count

  LOOP

    IF (l_ct_cookies(loop_ct).secure) THEN

      l_vc_secure := 'Y';

    ELSE

      l_vc_secure := 'N';

    END IF;

    INSERT

    INTO

      my_cookie_table VALUES

      (

        l_ct_cookies(loop_ct).name,

        l_ct_cookies(loop_ct).value,

        l_ct_cookies(loop_ct).domain,

        l_ct_cookies(loop_ct).expire,

        l_ct_cookies(loop_ct).path,

        l_vc_secure,

        l_ct_cookies(loop_ct).version

      );

  END LOOP loop_ct;

  COMMIT;

END;

/

 

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.

 

SELECT * FROM my_cookie_table;

 

Query Result:

NAME

VALUE

DOMAIN

EXPIRE

PATH

SECURE

VERSION

GUEST_LANGUAGE_ID

en_US

www.peteranswers.com

05-SEP-2017

/

N

 

JSESSIONID

324DF1EE44890ED6

3ADB92FE98E1FF85

www.peteranswers.com

 

/

Y

 

COOKIE_SUPPORT

True

www.peteranswers.com

05-SEP-2017

/

N

 

NID

85=D6HVRmZc3nuMGyzRQZ8

MCZw5iLNht16AfAnmeRSu

8jOR73q4DVlnMlMAMBkji0kjL

BZ36jITf8ZVKm0WLW7JGd1-9LFX9nOFVEOZxos1x4

BTA04pONU_wONMWajuYXAl

.google.co.in

07-MAR-2017

/

N

 

JSESSIONID

2515B7F1D123CD7C9

5597CF5A953EC00

100pulse.com

 

/

N

 

 

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.

 

DECLARE

  l_ct_cookies UTL_HTTP.COOKIE_TABLE;

  l_c_cookie UTL_HTTP.COOKIE;

  l_pi_counter PLS_INTEGER := 0;

  CURSOR cur

  IS

    SELECT

      *

    FROM

      my_cookie_table;

BEGIN

  FOR loop_cur IN cur

  LOOP

    l_pi_counter        :=l_pi_counter+1;

    l_c_cookie.name     := loop_cur.name;

    l_c_cookie.value    := loop_cur.value;

    l_c_cookie.domain   := loop_cur.domain;

    l_c_cookie.expire   := loop_cur.expire;

    l_c_cookie.path     := loop_cur.path;

    IF (loop_cur.secure  = 'Y') THEN

      l_c_cookie.secure := TRUE;

    ELSE

      l_c_cookie.secure := FALSE;

    END IF;

    l_c_cookie.version         := loop_cur.version;

    l_ct_cookies(l_pi_counter) := l_c_cookie;

  END LOOP loop_cur;

  UTL_HTTP.CLEAR_COOKIES;

  UTL_HTTP.ADD_COOKIES(l_ct_cookies);

  dbms_output.put_line('Total Cookies Count: '||UTL_HTTP.GET_COOKIE_COUNT);

END;

/

 

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,

 

SUBTYPE request_context_key IS PLS_INTEGER;

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,

 

UTL_HTTP.CREATE_REQUEST_CONTEXT (

         wallet_path IN VARCHAR2 DEFAULT NULL,

         wallet_password IN VARCHAR2 DEFAULT NULL,

         enable_cookies  IN BOOLEAN DEFAULT TRUE,

         max_cookies IN PLS_INTEGER DEFAULT 300,

         max_cookies_per_site IN PLS_INTEGER DEFAULT 20)

         RETURN request_context_key;

 

         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,

 

UTL_HTTP.DESTROY_REQUEST_CONTEXT (request_context request_context_key);

 

         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.

 

DECLARE

  l_vc_url VARCHAR2(100):='www.anexampleurl.com';

  l_rc_key UTL_HTTP.REQUEST_CONTEXT_KEY;

  l_http_req UTL_HTTP.REQ;

  l_http_resp UTL_HTTP.RESP;

  l_vc_html VARCHAR2(1024);

BEGIN

  l_rc_key := UTL_HTTP.CREATE_REQUEST_CONTEXT( wallet_path =>

  'File:C:\app\BoobalGanesan\admin\oracle\wallet', wallet_password => 'oracle',

  enable_cookies => TRUE, max_cookies => 100, max_cookies_per_site => 10);

  dbms_output.put_line('Request Context Key: '||l_rc_key);

  l_http_req  := UTL_HTTP.BEGIN_REQUEST(l_vc_url,l_rc_key);

  l_http_resp := UTL_HTTP.GET_RESPONSE(l_http_req);

  BEGIN

    LOOP

      UTL_HTTP.READ_LINE(l_http_resp, l_vc_html,true);

      DBMS_OUTPUT.PUT_LINE(l_vc_html);

    END LOOP;

  EXCEPTION

  WHEN UTL_HTTP.END_OF_BODY THEN

    UTL_HTTP.END_RESPONSE(l_http_resp);

  END;

  UTL_HTTP.DESTROY_REQUEST_CONTEXT(l_rc_key);

END;

/

 

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,

 

UTL_HTTP.REQUEST (

         url IN VARCHAR2,

         proxy IN VARCHAR2 DEFAULT NULL,

         wallet_path IN VARCHAR2 DEFAULT NULL,

         wallet_password IN VARCHAR2 DEFAULT NULL)

         RETURN VARCHAR2;

 

         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.

 

SELECT utl_http.request('http://www.dba-oracle.com/') FROM dual;

 

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,

 

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

 

UTL_HTTP.REQUEST_PIECES(

         url IN VARCHAR2,

         max_pieces IN NATURAL DEFAULT 32767,

         proxy IN VARCHAR2 DEFAULT NULL,

         wallet_path IN VARCHAR2 DEFAULT NULL,

         wallet_password IN VARCHAR2 DEFAULT NULL)

         RETURN html_pieces;

 

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

 

DECLARE

  l_html_pieces utl_http.html_pieces;

  LEN PLS_INTEGER;

BEGIN

  l_html_pieces:=UTL_HTTP.REQUEST_PIECES('http://www.google.com', 100);

  dbms_output.put_line('Total chunks: '||l_html_pieces.count);

  FOR loop_chunks IN 1..l_html_pieces.count

  LOOP

    dbms_output.put_line('Chunk #'||loop_chunks||': '||l_html_pieces(loop_chunks));

  END LOOP loop_chunks;

END;

/

 

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>


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.