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.