They recognise that occasionally the attempt to send a message to a particular site will fail (e.g. because that site is down) and require than an administrator at the sending site be notified of the failure by automatically generated email.
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 customer database values accordingly. If an error is detected then the Utl_Smtp API will be used to alert the administrator by email.
One customer communicates with one vendor. The sample will be more convincing if the customer site and vendor sites are implemented in different databases on different machines.
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;But it of course will work with both pieces in a single database.
The trigger computes the number of items to be ordered and inserts a row into a table which represents an orders queue. The order number is generated from a sequence.
This queue will be consumed periodically by a procedure. This could be automatically scheduled using the Dbms_Job API, but this code sample requires it to be executed manually.
The procedure calls a child procedure for each item to be ordered. The child procedure assembles the message, getting the appropriate XML tags as package constants and getting the vendor URL and password data from a table.
Be sure to edit this to correctly specify the node where you have created the vendor user
Note: A simple version of the child procedure is provided for comparison. It uses only Utl.Http.Request, which was available in Oracle8i. The full version of the child procedure relies on features introduced in Utl.Http in Oracle9i. All calls to the Oracle9i Utl.Http API are bundled in a grandchild procedure
The return message is parsed and the resulting information is used to update the orders queue.
On error, an email is sent automatically. This procedure relies on the Demo_Mail package code sample.
Be sure to edit the Customizable Section for the smtp host and domain for your environment.
Create the customer site schema objects in the correct dependency order.
The vendor implements the URL as a PL/SQL procedure. via mod_plsql and Htp.Print.
Make sure that the basic mechanism is properly configured by compling and testing a simple Hello mod_plsql URL.
This procedure parses the incoming message and updates the vendor orders table. accordingly. It composes a return message getting the appropriate XML tags as package constants.
For the most dramatic effect
Create the vendor site schema objects in the correct dependency order.
Note: The customer message is sent in this code sample as the value in a name-and-value parameter pair using the "GET" method. This works fine for the concrete data provided. A realistic implementation should cater for 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 which implements the URL would need to be correspondingly more elaborate.
connect customer/customer@customer_site Execute Send_Error_Mail ( 12345, 'This is a test' );The complete end-to-end test is to update the curr_stock_level in for a row in the customer's stock_levels table so that it falls below threshold_stock_level and triggers the message exchange. Then check the customer_orders and vendor_orders tables.
Use this test script. If you run it immediately after running the scripts to create the customer site and to create the vendor site then you should see [something like] this...
O V SCU Q D S MSG ------- ---- ---- ---- ------------------------ ---------- -------- 1234567 1 1 41 12:51:23::08-Nov-2001 submitted...in the customer_orders table and [something like] this...
ORDER_REF CUSTOMER_ID SCU QUANTITY ORDER_DAT STATUS --------- ----------- ---------- ---------- --------- ------ 1234567 1 1 41 08-NOV-01 new...in the vendor_orders table.
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 table of vendor data using a URL like...
http://bllewell-sun.us.oracle.com:7777/pls/vendor/Nonexistent...and then to run the scripts to create the customer site, create the vendor site and run the test. You should now see [something like] this...
O V SCU Q D S MSG ------- ---- ---- ---- ------------------------ ---------- -------------------------------------------- 1234567 1 1 41 01:45:11::08-Nov-2001 failed ORA-29268: HTTP client error 404 - Not Found...in the customer_orders table and you should receive a corresponding email.
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 24-June-2002