HTTP Communication from Within the Oracle Database by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn 03/04/2003

Editor's note: In the last article in their continuing series on new Oracle 9i features, Steven Feuerstein and Bryn Llewellyn showed you how to work with multi-level collections. In this article, the pair take a look at Utl_Http and show how you can use it in an Oracle 9i database to implement a requestor in a B2B implementation.

The B2B (business-to-business) component of e-Business (which is still going strong, even if there is much less IPO hype about it) depends on automated communication between business sites across the public Internet. In other words, distributed components need to be able to communicate with each other without any need for manual, human intervention.

HTTP (the Hypertext Transfer Protocol) offers a standard set of rules for exchanging files (such as text and multimedia files) on the Web. Web servers contain an HTTP daemon, which waits for requests and responds to them when they arrive at a site served by that Web server. An HTTP client, such as a Web browser, can submit requests for files or for actions to be taken.

In a B2B implementation, the requestor (sometimes called the consumer) is a mechanical version of the familiar Web browser. This article explains how to implement the requestor in an Oracle 9i database using Utl_Http. The other partner in the dialogue, the provider, is implemented using the same technology as any Web site, and is not our focus in this article. The requestor/provider relationship is just another example of the familiar remote procedure call (RPC) paradigm. The requestor is the invoker of the remote procedure, and the provider is its implementor. The exciting thing is that now we're doing remote procedure calls across the public Internet.

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

To fully automate this process, however, you need standardized-or at least agreed- upon-semantics for communicating requests and understanding responses. Though partners in a particular B2B relationship could define standards for their protocols from scratch, the de facto standard that has emerged is to rely on XML documents (eXtended Markup Language) for both request and reply.

Oracle offers technology to allow both the requestor and the provider to straightforwardly implement their services backed by an Oracle 9i database, and using only PL/SQL on top of fully elaborated APIs. The simplest way to code the provider is to use mod_plsql, either directly via the HTTP listener component of the Oracle 9i database or via Oracle9iAS, and to write a PL/SQL stored procedure that's exposed as the URL representing the request. The XML document payload expressing the request is decoded; the database is accessed to supply the reply information and is updated appropriately; and the reply is encoded and sent using Htp.Print or a similar mechanism. A detailed discussion of this end of the dialogue is beyond the scope of this article, and, of course, the provider could be implemented using entirely non-Oracle technology.

We provide and explain a complete working example at the end of this article. If you're lucky enough to have Oracle 9i database installations on machines at two distinct locations, then you'll be able to see the communication between requestor and provider take place across the public Internet. (The code will work fine with both requestor and provider in the same database, of course, but you'll have to use your imagination a little to supply the realism!)

The request is typically sent (or more likely queued and then sent later) in the body of a database trigger, which fires on an event like a stock level falling below the defined threshold for reordering. The XML document expressing the request is encoded by accessing current database values and sent, typically using the "POST" method to ensure that an arbitrarily large XML request can be sent piecewise. Authentication information (for instance, username and password) is likely to be required as part of the request. And possibly the request header will need to be explicitly set to reflect an agreed-upon protocol. Then the response is (started to be) fetched, and its status code is checked for errors, and its header is checked for protocol compliance. Then the arbitrarily large XML document expressing the response itself is fetched piecewise, decoded, and the information is used to update the database. A robust implementation is likely to have a component that automatically sends a generated email to a system administrator in the event of an error. Oracle has features for encoding and decoding XML, and for sending email from the database, but, again, these are beyond the scope of this article.

Depending on the design of the workflow, state may need to be represented. For example, a customer may request a price and delivery date for a given quantity of items from several vendors. Each vendor would reply with price and delivery date and with an "offer good to" date. When the customer site sends a request to the selected vendor to place a definite order, it will need to refer to the specific offer. If such a scheme is used within a single organization-for example, to communicate between databases at local offices in different countries-then the communication protocol can be designed from scratch, and most likely an offer reference number will be exchanged as part of the XML encoding. However, if the partners in the B2B relationship are completely independent, and especially if the relationship is casual, then the requestor will have to follow whatever protocol the provider has defined. It may be that the provider has implemented the state that represents an ongoing dialogue using cookies. In this case, the requestor will need to handle these programmatically.

The Utl_Http Package

The Utl_Http package pre-Oracle 9i allowed a basic implementation of the requestor site. It allowed an arbitrarily large response to be handled piecewise in a PL/SQL VARCHAR2. But it supported only the "GET" method-that is, it didn't support sending arbitrarily large messages in the body of the request. And it didn't support authentication, setting the header of the request, inspecting the status code and header of the response, or dealing with cookies. Oracle 9i adds support for all these (including optionally fetching the response "as is" into a PL/SQL RAW), and beyond that provides full support for the semantics that can be expressed via HTTP. For example, persistent HTTP connections are now supported. Use of these gives dramatic speed and scalability improvement for applications that repeatedly and frequently make HTTP requests to the same site. And users now have full control over the encoding of character data.

HTTP relies on an underlying transport layer. Thus the Utl_Http package (written in PL/SQL) is implemented on top of the Utl_Tcp package. (The Utl_Smtp package for sending email from the database is implemented in the same fashion.) Pre-Oracle9i, Utl_Tcp was implemented in Java. At Oracle9i, it has been re-implemented natively-that is, in C directly on top of the socket layer-to improve its performance.

Later in the article, we'll provide a code sample that shows how to model the requestor at SQL*Plus, and that can be used to inspect the return status and content of an arbitrary password-protected URL.

Encoding of Character Data

In the classical client/server architecture, the database and the client may use different encoding schemes to represent character data. For example, in a Japanese application, the database might use (a variety of) the EUC character set, and the client might use (a variety of) the SJIS character set. Thus character-encoding conversion is required. The solution is well known and long established: Oracle Net transparently handles the conversion (as specified by the database character set and the NLS_LANG client environment variable). A corresponding issue exists for Utl_Http. When a request is sent, it may need to be encoded differently than the database character set (because the requestor knows that the target URL requires this). And when a response is received, it may again be encoded differently than the database character set (because that's the non-negotiable behavior of the target URL).

There are two areas of concern when sending a request: the URL and the request body. When sending by the "GET" method, all request parameterization is via the URL itself, typically after the ? delimiter. Search terms, for example, are normally handled this way. HTTP defines no convention for specifying different character sets for the URL and expects that everything is 7-bit ASCII. Other character-encoding schemes should be represented as the hex codes of their bytes using the %nn notation. (The sender of the request must know from documentation which character set the URL expects to decode from the hex representation.) Oracle 9i introduces the Utl_Url package, which has functions to convert from the database character set to a hex-coded representation of a specified character set, and vice versa. In addition, these functions handle the conversion of the reserved symbols: percent (%), semi-colon (;), slash (/), question mark (?), colon (:), at sign (@), ampersand (&), equals sign (=), plus sign (+), dollar sign ($), and comma (,).

When sending by the "PUT" method, the character set of the request body should be set via the charset attribute of the Content-Type in the request header, using the new Utl_Http.Set_Header procedure. If this is done, it gives Oracle sufficient information to transform appropriately when sending a character request body (by using Utl_Http.Write_Text). If the charset attribute isn't set in the request header, then no character set conversion takes place unless the user has catered for it via the overloaded procedure Utl_Http.Set_Body_Charset. The variant Set_Body_Charset(charset varchar2)-a.k.a. the global variant-allows the user to set a fallback character set, to be assumed, if no other information is provided, for both requests and responses for the session. The variant Set_Body_Charset(r Utl_Http.Req, charset varchar2)-a.k.a. the request variant-allows the user to insist on a character set for the body for this request. (A record of PL/SQL type Utl_Http.Req is returned when the HTTP request is begun with Utl_Http.Begin_Request.) The choice made via the request variant won't only override that made via the global variant but will also override that made via the charset attribute of the request header. For this reason, the recommended way to specify the character set conversion for the request body is via the charset attribute of the header. Only if the user has a special reason for leaving this unspecified in the request header would he use the request variant of Set_Body_Charset.

There's just one area of concern when receiving the response: the response body. If the implementation of the URL is well-mannered, then the character set of the response body will be specified correctly in the charset attribute of the Content-Type in the response header, accessible to the user via the procedure Utl_Http.Get_Header. Oracle will implicitly perform the appropriate conversion in connection with calling Utl_Http.Read_Text. However, this is often not set. In this case, the user can use the global variant of Set_Body_Charset to determine the character set conversion. However, the charset attribute of the response header is sometimes set wrong. (This is likely when pages in different character sets are served up as files from the file system seen by the Web server, since the Content-Type header information will often be set globally for the server with no mechanism to make it file-specific.) For this reason, a third overloaded variant Set_Body_Charset(r Utl_Http.Resp,charset varchar2) is provided-a.k.a. the response variant. (A record of PL/SQL type Utl_Http.Resp is returned when the HTTP response is received with Utl_Http.Get_Response.) The choice made via the response variant will override that made via the global variant and that expressed via the charset attribute of the response header.

Note: From Oracle 8i v8.1.6 and pre-Oracle 9i, Oracle detected the charset of the response body (if this was specified) and used the information to do the character set conversion. And if the charset attribute of the response body wasn't specified, then no conversion took place, and no overriding or fallback mechanism was provided. Under special circumstances (for example, fetching a SJIS Japanese response where the charset attribute isn't specified into an EUC database), problems arose pre- Oracle 9i.

Thus the user now has full control over all character set conversion issues. In an extreme case, where the response body is Content-Type text/HTML and where the HTML <meta> tag is used to specify the character set, the user can retrieve the response body into a PL/SQL RAW with Utl_Http.Read_Raw and then write custom code to parse the HTML and to convert to the database character set in a PL/SQL VARCHAR2 once the response character set is discovered.

A Closer Look at Utl_Http Calls

Example 1 shows: how to send an HTTP request-setting the proxy information, setting the method to "GET," providing username/password authentication information, and setting the request header-and how to get the response-retrieving the status code, the header information, and the response body. The "GET" method is suitable for non-parameterized URLs or for URLs with a manageable volume of parameter name-value pairs. The maximum length of the URL string is limited by the capacity of the PL/SQL VARCHAR2 variable used to pass it. The "POST" method is suitable for parameterizing the request with an arbitrarily large volume of data, especially, for example, as might be the case when the request is expressed as an XML document.

Example 1. A simple demonstration of Utl_Http's basic features.

DECLARE
   req     Utl_Http.req;
   resp    Utl_Http.resp;
   NAME    VARCHAR2 (255);
   VALUE   VARCHAR2 (1023);
   v_msg   VARCHAR2 (80);
   v_url   VARCHAR2 (32767) := 'http://otn.oracle.com/';
BEGIN
   /* request that exceptions are raised for error Status Codes */
   Utl_Http.set_response_error_check (ENABLE => TRUE );

   /* allow testing for exceptions like Utl_Http.Http_Server_Error */
   Utl_Http.set_detailed_excp_support (ENABLE => TRUE );
   
   Utl_Http.set_proxy (
      proxy                 => 'www-proxy.us.oracle.com',
      no_proxy_domains      => 'us.oracle.com'
   );
   req := Utl_Http.begin_request (url => v_url, method => 'GET');

   /*
    Alternatively use method => 'POST' and Utl_Http.Write_Text to
    build an arbitrarily long message
  */
   Utl_Http.set_authentication (
      r              => req,
      username       => 'SomeUser',
      PASSWORD       => 'SomePassword',
      scheme         => 'Basic',
      for_proxy      => FALSE /* this info is for the target Web server */
   );
   Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');
   resp := Utl_Http.get_response (r => req);

   DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);
   DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);

   FOR i IN 1 .. Utl_Http.get_header_count (r => resp)
   LOOP
      Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
      DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
   END LOOP;

   BEGIN
      LOOP
         Utl_Http.read_text (r => resp, DATA => v_msg);
         DBMS_OUTPUT.put_line (v_msg);
      END LOOP;
   EXCEPTION
      WHEN Utl_Http.end_of_body
      THEN
         NULL;
   END;

   Utl_Http.end_response (r => resp);
EXCEPTION
   /*
    The exception handling illustrates the use of "pragma-ed" exceptions
    like Utl_Http.Http_Client_Error. In a realistic example, the program
    would use these when it coded explicit recovery actions.

    Request_Failed is raised for all exceptions after calling
    Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )
    And it is NEVER raised after calling with ENABLE=>TRUE
  */
   WHEN Utl_Http.request_failed
   THEN
      DBMS_OUTPUT.put_line (
         'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm
      );
   /* raised by URL http://xxx.oracle.com/ */
   WHEN Utl_Http.http_server_error
   THEN
      DBMS_OUTPUT.put_line (
         'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm
      );
   /* raised by URL http://otn.oracle.com/xxx */
   WHEN Utl_Http.http_client_error
   THEN
      DBMS_OUTPUT.put_line (
         'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm
      );
   /* code for all the other defined exceptions you can recover from */
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

A B2B example

Picture a community of customers and vendors. Each customer buys items from one or many vendors, and each vendor sells items to one or many customers. Customers and vendors maintain inventory data in Oracle databases. The community has come to appreciate the benefits of e-Business and has agreed to standardize on a way to express the variety of requests and replies that need to be sent, as shown in Table 1.

Table 1. B2B participants and their requests and replies.

They agree to adopt XML via HTTP as the communication format and transport mechanisms since this supports all current messaging requirements and is readily extensible, without requiring changes to the basic message exchange and parsing mechanisms.

They recognize, furthermore, that occasionally the attempt to send a message to a particular site will fail (for instance, because that site is down). They require, as a result, that an administrator at the sending site be notified of the failure by automatically generated email.

Implementation Concept

A customer database event will trigger the sending of a message. The message will be constructed from current database values. It will be sent to one or several password- protected URLs using the Utl_Http API-URL and password data to be retrieved from the database.

The vendor URL will be implemented as a PL/SQL procedure via mod_plsql and Htp.Print, etc. This procedure will parse the message and access vendor database values to compose the reply and will record data about the reply in the database.

The customer will parse the return message and update the customer database values accordingly. If an error is detected, then the Utl_Smtp API will be used to alert the administrator by email.

Simplified Scenario

One customer communicates with one vendor. The sample will be more convincing if the customer and vendor sites are implemented in different databases on different machines, as shown in the following script:

connect system/manager@customer_site
create user customer identified by customer;
grant resource, connect to customer;

connect system/manager@vendor_site
create user vendor identified by vendor;
grant resource, connect to vendor;

This approach will, of course, work with both pieces in a single database.

Setting Up the Customer Site

The customer inventory is represented in a single stock_levels table (stock_levels.sql). A trigger on this table fires when the stock level of an item falls below a threshold.

The trigger computes the number of items to be ordered and inserts a row into the customer_orders tables, which represents an orders queue (customer_orders.sql). The order number is generated from the order_ref_seq sequence number (order_ref_seq.sql). The customer orders queue will be consumed periodically by the scan_customer_orders procedure (scan_customer_orders.sql). This could be automatically scheduled using the Dbms_Job API, but the code sample requires it to be executed manually.

The procedure calls the submit_order procedure for each item to be ordered (submit_order.sql). Submit_order assembles the message, getting the appropriate XML tags as package constants from the tags package. It also retrieves the vendor URL and password data from the vendors table (vendors.sql).

Note: You will need to edit the INSERT statement for the vendors table (shown in Example 2) to correctly specify the node where you've created the vendor user.

Example 2. Setting up the vendors table.

INSERT INTO vendors
            (vendor_id, url, the_user, PASSWORD)
     VALUES (1, 
      --
        -- modify this data for your site
        --
        'http://bllewell-sun.us.oracle.com/pls/vendor/receive_order',
        --
        --  Uncomment this line to fabricate an error and cause an error 
        --  email to be sent
        --  'http://bllewell-sun.us.oracle.com/pls/vendor/Nonexistent',
        'my_vendor', 
        'my_password');

Note: A simple version of the submit_order procedure is provided for comparison in submit_order_simple_version.sql. It uses only Utl.Http.Request, which was available in Oracle 8i. The full version of the submit_order relies on features introduced in Utl.Http in Oracle 9i. All calls to the Oracle 9i Utl.Http API are bundled in a grandchild procedure named get_http_request (get_http_request.sql).

Let's take a look at some of the steps in get_http_request that demonstrate the extended power of Utl_Http in Oracle 9i.

At the very beginning of the executable section, we set up the HTTP session: First, set the error-check response level so that if a call to Utl_Http.Get_Response results in a Web server returning a state indicating failure, Oracle will raise an exception. We also call Utl_Http to set the proxy Web server (you'll want to change the settings you see in the following code block):

BEGIN /* executable section */
   Utl_Http.set_response_error_check (
     ENABLE => TRUE );

   Utl_Http.set_proxy (
      proxy                 
         => 'www-proxy.us.oracle.com',
      no_proxy_domains      
         => 'us.oracle.com'
   );

Now it's time to start a new HTTP request:

   v_req := Utl_Http.begin_request (
     url => p_url, method => 'GET');

   Set authentication for this session based on the provided username and password:

   Utl_Http.set_authentication (
      r              => v_req,
      username       => p_user,
      PASSWORD       => p_password,
      scheme         => 'Basic'
   );

Set the HTTP request header and then obtain the response from the Web server. The v_resp variable is a record of type Utl_Http.resp, with fields named status_code, reason_phrase, and http_version.

   Utl_Http.set_header (
      r          => v_req,
      NAME       => 'User-Agent',
      VALUE      => 'Mozilla/4.0'
   );
   v_resp := Utl_Http.get_response (r => v_req);

At this point, you may prefer to test v_resp.status_code explicitly. The constant Utl.Http.Http_Ok (which has the value 200) means "OK." You may also want to program explicit action for various other error values of v_resp.status_code. Since no action is taken here, we simply rely on the fact that if there's an error, Oracle will raise an exception, ORA-29268, with a message such as "HTTP client error nnn - <some explanation>."

Assuming that the response came back without error, we then loop through all the different headers in the response and make sure that they're all of type XML, as shown in Example 3.

Example 3. Confirm response headers for XML document types.

   FOR i IN 1 .. Utl_Http.get_header_count (r => v_resp)
   LOOP
      Utl_Http.get_header (
         r          => v_resp,
         n          => i,
         NAME       => v_name,
         VALUE      => v_value
      );

      IF LOWER (v_name) = 'content-type'
      THEN
         IF INSTR (LOWER (v_value), 'text/xml') < 1
         THEN
            RAISE_APPLICATION_ERROR (
               -20997,
               'Get_Http_Request: 
               unexpected Content-Type: ' || v_value
            );
         END IF;
      END IF;
   END LOOP;

Once we make sure that the content in the response conforms to what's expected (and what can be handled), it's time to obtain the XML document itself. We deliberately declared a small buffer variable VARCHAR2(80) to illustrate piecewise fetch logic. In a more typical implementation, you'd use a large buffer (maximum size is 32,767) so that you can handle arbitrarily long messages.

BEGIN
  LOOP
     Utl_Http.read_text (
        r => v_resp, 
        data => v_buffer);
     v_msg := 
       v_msg || v_buffer;
  END LOOP;
EXCEPTION
  WHEN Utl_Http.end_of_body
  THEN
     NULL;
END;

When we're finished retrieving the HTTP response text, we end the response and return the value:

Utl_Http.end_response (r => v_resp);
RETURN v_msg;

The return message is then parsed using the parse_message procedure (parse_message.sql), and the resulting information is used to update the orders queue.

On error, an email is sent automatically using the send_error_email procedure (see send_error_email.sql). This procedure relies on the Demo_Mail package code sample, available on the Oracle Technology Network.

Be sure to edit the Customizable Section in send_error_mail.sql for the SMTP host and domain for your environment.

Vendor Site

The vendor implements the URL in the receive_order procedure (receive_order.sql) via mod_plsql and Htp.Print.

Make sure that this basic mechanism is properly configured by compiling and testing a simple mod_plsql URL, such as that shown in Example 4 (hello.sql).

Example 4. Simple "hello" procedure to test mod_plsql mechanism.

CREATE OR REPLACE PROCEDURE hello
IS
-- http://bllewell-sun.us.oracle.com/pls/vendor/hello
BEGIN
   Htp.Print (
         '<head><title>hello</title></head><body>'
      || 'Hello. This is vendor #1'
      || '</body></html>'
   );
END hello;

The receive_order procedure parses an incoming message using the parse_message package (parse_message.sql) and updates the vendor orders table (vendor_orders.sql) accordingly. It composes a return message, using the appropriate XML tags as directed by the named constants in the tags package.

Note: The customer message is sent in this code sample as the value in a name- value parameter pair using the "GET" method. This works fine for the concrete data provided. A realistic implementation should cater to the possibility that the message to be sent is arbitrarily long, and so would use the "POST" method to send the message in the body of the HTTP request. The Utl_Http API supports this. However, the programming of the procedure that implements the URL would need to be correspondingly more elaborate.

Test the System

First, test the sending of email from the database, which you can do by calling send_error_email as shown here:

connect customer/customer@customer_site
BEGIN
   Send_Error_Mail ( 
     12345, 'This is a test' );
END;
/

The complete end-to-end test involves the following steps:

1. Update the curr_stock_level for a row in the customer's stock_levels table so that it falls below threshold_stock_level.

2. Trigger the message exchange.

3. Check the customer_orders and vendor_orders tables.

The script found in b2b_test.sql and shown in Example 5 will run the preceding test.

Example 5. End-to-end test script.

CONNECT customer/customer@customer_site

UPDATE stock_levels
   SET curr_stock_level = 50
 WHERE scu = 1;
COMMIT ;

UPDATE stock_levels
   SET curr_stock_level = 9
 WHERE scu = 1;
COMMIT ;

SET Serveroutput On
EXECUTE Scan_Customer_Orders

COLUMN o   format 9999999999
COLUMN v   format 999
COLUMN scu format 999
COLUMN q   format 999
COLUMN d   format a24
COLUMN s   format a10
COLUMN msg format a60
SET Wrap On
SET LineSize 140

SELECT order_ref o, vendor_id v, scu, quantity q,
       TO_CHAR (order_date, 'hh:mi:ss::DD-Mon-YYYY')
             d, status s, err_msg msg
  FROM customer_orders;

COLUMN m format a140
SELECT err_msg m FROM customer_orders;

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

CONNECT vendor/vendor@vendor_site
SELECT * FROM vendor_orders;

If you execute b2b_test.sql immediately after running customer_install.sql and vendor_install.sql, you should see something like the content shown in Example 6 and Example 7 in the customer_orders and vendor_orders table, respectively.

Example 6. Content in the customer_orders table after script execution.

      O    V  SCU    Q D                        S          MSG
------- ---- ---- ---- ------------------------ ---------- --------
1234567    1    1   41 12:51:23::08-Nov-2001    submitted

Example 7. Content in the vendor_orders table after script execution.

ORDER_REF CUSTOMER_ID        SCU   QUANTITY ORDER_DAT STATUS
--------- ----------- ---------- ---------- --------- ------
  1234567           1          1         41 08-NOV-01 new

To test the exception reporting and the automatic sending of email, fabricate an error condition. A simple way to do this is to update the vendors table using a URL such as that shown in Example 8.

Example 8. A URL that will fabricate an error condition.

http://bllewell-sun.us.oracle.com:7777/pls/vendor/Nonexistent

Once you've finished, run customer_install.sql, vendor_install.sql, and finally b2b_test.sql. You should then see data such as that shown in Example 9 in the customer_orders table. You should also receive a corresponding email.

Example 9. Resulting data in the customer_orders table.

D                        S      MSG
------------------------ ------ --------------------------------------------
01:45:11::08-Nov-2001    failed ORA-29268: HTTP client error 404 - Not Found

PL/SQL at the Heart of Internet Applications

The Utl_Http package further enhances the ability of a PL/SQL-based application to integrate with and operate at the very heart of an Internet-centric application. With Utl_Http, you can implement the requestor site in a B2B transaction.

Pre Oracle 9i, the Utl_Http package supported enough functionality to implement the sending of a basic B2B request and the receipt of the response. Oracle 9i adds:

• The "POST" method to handle arbitrarily long requests
• Authentication
• Access to return status code
• RAW reply
• Cookie support

In other words, it provides full support for the semantics that can be expressed via HTTP. It also adds full functionality for character-set conversion for request and reply.

With the addition of these new features, Utl_Http can now support arbitrarily complex requirements for the requestor site.

This article was originally published in the July 2002 issue of Oracle Professional. The material in Feuerstein's articles--and those he cowrote with Bryn Llewellyn--are based on Oracle Corporation white papers (originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002), and Feuerstein's book, Oracle PL/SQL Programming, 3rd Edition.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language.

Bryn Llewellyn is PL/SQL Product Manager, Database and Application Server Technologies Development Group, at Oracle Corporation Headquarters.

O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.

• Sample Chapter 10, Dates and Timestamps, is available free online.

• You can also look at the Table of Contents, the Index, and the Full Description of the book.

• For more information, or to order the book, click here.

Return to the O'Reilly Network.

Copyright © 2007 O'Reilly Media, Inc.