HTTP Communication from Within the Oracle Database

Listen Print Discuss

HTTP Communication from Within the Oracle Database
Pages: 1, 2, 3, 4

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.

Participant	Request	Reply
customer #1 (to many vendors):	This is customer #1 Our ref #nnnnn Can you supply me with item X? What is your price for N units? When can you deliver?
vendor #1		This is vendor #1 Your ref #nnnnn We don't stock item X
vendor #2		This is vendor #2 Your ref #nnnnn N units of item X will cost you Y We can deliver by D1 This offer good until D2 Our offer ref #mmmm
customer #1 to vendor #2	This is customer #1 Our ref #ppppp Your offer ref #mmmm Supply me with N units of item X
vendor #2 to customer #1		This is vendor #2 Your ref #ppppp Order for N units of item X confirmed Our order ref #qqqqq

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

Pages: 1, 2, 3, 4