HTTP Communication from Within the Oracle Database
Pages: 1, 2, 3, 4
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:
Update the
curr_stock_levelfor a row in the customer's stock_levels table so that it falls belowthreshold_stock_level.Trigger the message exchange.
Check the
customer_ordersandvendor_orderstables.
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 submittedExample 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 newTo 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 FoundPL/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, 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.
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.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 9 of 9.
-
Implementing Vendor's Site
2005-01-18 03:32:05 Scorp [Reply | View]
Can any body tell me how to set vendor's site....
or can anybody suggest me a tutorial on implementing vendor's site
-
Good article, but no POST examples
2003-10-12 22:30:02 anonymous2 [Reply | View]
This article mentions that 9i supports the POST method, and that this is the method that would typically be used, but does not give any examples. I cannot find any examples of using the POST method to send an XML document using utl_http - every article I have read uses the same code examples using the GET method, and then goes on to mention that you could alternatively use the POST method, but not HOW to parameterise the data when using POST.
Does anyone know where there is some example code for this?
-
Good article, but no POST examples
2003-11-05 12:10:29 anonymous2 [Reply | View]
It's actually quite simple:
PROCEDURE POST_Test (o_replytext out VARCHAR2
,o_return_code out INTEGER
,i_messagetext in VARCHAR2
,i_urltext in VARCHAR2
,i_timeout in INTEGER DEFAULT 60)
IS
sslreq UTL_HTTP.req; /* Secure connection request */
rsp UTL_HTTP.resp; /* Secure response */
replymessage VARCHAR2(32767); /* reply message (local) */
replyline VARCHAR2(32767); /* piece of reply message read from secure site. */
BEGIN
/* Set up wallet information for secure connection capability */
/* remove this line if you don't have wallet installed and are not using https */
-- UTL_HTTP.SET_WALLET('file:DirectoryPath','put password here');
/* Initiate request to secure site, set parameters */
sslreq := UTL_HTTP.BEGIN_REQUEST(i_urltext,'POST','HTTP/1.0');
UTL_HTTP.SET_HEADER(sslreq,'Content-Type','text/xml');
UTL_HTTP.SET_HEADER(sslreq,'Content-Length',to_char(length(i_messagetext)));
UTL_HTTP.SET_TRANSFER_TIMEOUT(i_timeout);
/* Write information over secure connection */
UTL_HTTP.WRITE_TEXT(sslreq,i_messagetext);
/* Initialize Response */
rsp:=UTL_HTTP.GET_RESPONSE(sslreq);
replymessage := '';
/* Retrieve response */
BEGIN
LOOP
UTL_HTTP.READ_LINE(rsp,replyline,TRUE);
replymessage := replymessage || replyline;
END LOOP;
UTL_HTTP.END_RESPONSE(rsp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(rsp);
END;
/* Set output information */
o_replytext := replymessage;
o_return_code := 0;
EXCEPTION
WHEN OTHERS THEN
o_return_code := SQLCODE;
o_replytext := SQLERRM;
END POST_Test;
Post for HTTP 1.1 is suppose to be supported, but it doesn't work. There is a bug in utl_http that Oracle knows about, and has yet to fix.
An example would be helpful.