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.
The vendor implements the URL in the receive_order procedure (receive_order.sql)
Make sure that this basic mechanism is properly configured by compiling and testing a
mod_plsql URL, such as that shown in Example 4 (hello.sql).
Example 4. Simple "hello" procedure to test
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
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:
curr_stock_levelfor a row in the customer's stock_levels table so that it falls below
Trigger the message exchange.
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
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.
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
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
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
- 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, having written ten books on the subject. Steven is a Senior Technology Advisor with Quest Software and has been developing software since 1980.
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.
For more information, or to order the book, click here.
Return to the O'Reilly Network.