Release 2 (8.1.6)
A76938-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Advanced Queuing Release 2 (8.1.6) A76938-01 |
|
Scripts for Implementing 'BooksOnLine', 5 of 6
Rem Rem $Header: tkaqdocp.sql 26-jan-99.17:50:54 aquser1 Exp $ Rem Rem tkaqdocp.sql Rem Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved. Rem Rem NAME Rem tkaqdocp.sql - <one-line expansion of the name> Rem set echo on; Rem ================================================================== Rem Illustrating Support for OPS Rem ================================================================== Rem Login into OE account connect OE/OE; set serveroutput on; Rem check instance affinity of OE queue tables from AQ administrative view select queue_table, primary_instance, secondary_instance, owner_instance from user_queue_tables; Rem alter instance affinity of OE queue tables begin dbms_aqadm.alter_queue_table( queue_table => 'OE.OE_orders_sqtab', primary_instance => 2, secondary_instance => 1); end; / begin dbms_aqadm.alter_queue_table( queue_table => 'OE.OE_orders_pr_mqtab', primary_instance => 1, secondary_instance => 2); end; / Rem check instance affinity of OE queue tables from AQ administrative view select queue_table, primary_instance, secondary_instance, owner_instance from user_queue_tables; Rem ================================================================== Rem Illustrating Propagation Scheduling Rem ================================================================== Rem Login into OE account set echo on; connect OE/OE; set serveroutput on; Rem Rem Schedule Propagation from bookedorders_que to shipping Rem execute dbms_aqadm.schedule_propagation(queue_name => 'OE.OE_bookedorders_que'); Rem Login into boladm account set echo on; connect boladm/boladm; set serveroutput on; Rem create a procedure to enqueue an order create or replace procedure order_enq(book_title in varchar2, book_qty in number, order_num in number, shipping_priority in number, cust_state in varchar2, cust_country in varchar2, cust_region in varchar2, cust_ord_typ in varchar2) as OE_enq_order_data BOLADM.order_typ; OE_enq_cust_data BOLADM.customer_typ; OE_enq_book_data BOLADM.book_typ; OE_enq_item_data BOLADM.orderitem_typ; OE_enq_item_list BOLADM.orderitemlist_vartyp; enqopt dbms_aq.enqueue_options_t; msgprop dbms_aq.message_properties_t; enq_msgid raw(16); begin msgprop.correlation := cust_ord_typ; OE_enq_cust_data := BOLADM.customer_typ(NULL, NULL, NULL, NULL, cust_state, NULL, cust_country); OE_enq_book_data := BOLADM.book_typ(book_title, NULL, NULL, NULL); OE_enq_item_data := BOLADM.orderitem_typ(book_qty, OE_enq_book_data, NULL); OE_enq_item_list := BOLADM.orderitemlist_vartyp( BOLADM.orderitem_typ(book_qty, OE_enq_book_data, NULL)); OE_enq_order_data := BOLADM.order_typ(order_num, NULL, cust_ord_typ, cust_region, OE_enq_cust_data, NULL, OE_enq_item_list, NULL); -- Put the shipping priority into message property before -- enqueueing the message msgprop.priority := shipping_priority; dbms_aq.enqueue('OE.OE_bookedorders_que', enqopt, msgprop, OE_enq_order_data, enq_msgid); end; / show errors; grant execute on order_enq to OE; Rem now create a procedure to dequeue booked orders for shipment processing create or replace procedure shipping_bookedorder_deq( consumer in varchar2, deqmode in binary_integer) as deq_cust_data BOLADM.customer_typ; deq_book_data BOLADM.book_typ; deq_item_data BOLADM.orderitem_typ; deq_msgid RAW(16); dopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; deq_order_data BOLADM.order_typ; qname varchar2(30); no_messages exception; pragma exception_init (no_messages, -25228); new_orders BOOLEAN := TRUE; begin dopt.consumer_name := consumer; dopt.wait := DBMS_AQ.NO_WAIT; dopt.dequeue_mode := deqmode; dopt.navigation := dbms_aq.FIRST_MESSAGE; IF (consumer = 'West_Shipping') THEN qname := 'WS.WS_bookedorders_que'; ELSIF (consumer = 'East_Shipping') THEN qname := 'ES.ES_bookedorders_que'; ELSE qname := 'OS.OS_bookedorders_que'; END IF; WHILE (new_orders) LOOP BEGIN dbms_aq.dequeue( queue_name => qname, dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); deq_item_data := deq_order_data.items(1); deq_book_data := deq_item_data.item; deq_cust_data := deq_order_data.customer; dbms_output.put_line(' **** next booked order **** '); dbms_output.put_line('order_num: ' || deq_order_data.orderno || ' book_title: ' || deq_book_data.title || ' quantity: ' || deq_item_data.quantity); dbms_output.put_line('ship_state: ' || deq_cust_data.state || ' ship_country: ' || deq_cust_data.country || ' ship_order_type: ' || deq_order_data.ordertype); dopt.navigation := dbms_aq.NEXT_MESSAGE; EXCEPTION WHEN no_messages THEN dbms_output.put_line (' ---- NO MORE BOOKED ORDERS ---- '); new_orders := FALSE; END; END LOOP; end; / show errors; Rem now create a procedure to dequeue rush orders for shipment create or replace procedure get_rushtitles(consumer in varchar2) as deq_cust_data BOLADM.customer_typ; deq_book_data BOLADM.book_typ; deq_item_data BOLADM.orderitem_typ; deq_msgid RAW(16); dopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; deq_order_data BOLADM.order_typ; qname varchar2(30); no_messages exception; pragma exception_init (no_messages, -25228); new_orders BOOLEAN := TRUE; begin dopt.consumer_name := consumer; dopt.wait := 1; dopt.correlation := 'RUSH'; IF (consumer = 'West_Shipping') THEN qname := 'WS.WS_bookedorders_que'; ELSIF (consumer = 'East_Shipping') THEN qname := 'ES.ES_bookedorders_que'; ELSE qname := 'OS.OS_bookedorders_que'; END IF; WHILE (new_orders) LOOP BEGIN dbms_aq.dequeue( queue_name => qname, dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); deq_item_data := deq_order_data.items(1); deq_book_data := deq_item_data.item; dbms_output.put_line(' rushorder book_title: ' || deq_book_data.title || ' quantity: ' || deq_item_data.quantity); EXCEPTION WHEN no_messages THEN dbms_output.put_line (' ---- NO MORE RUSH TITLES ---- '); new_orders := FALSE; END; END LOOP; end; / show errors; Rem now create a procedure to dequeue orders for handling North American Rem orders create or replace procedure get_northamerican_orders as deq_cust_data BOLADM.customer_typ; deq_book_data BOLADM.book_typ; deq_item_data BOLADM.orderitem_typ; deq_msgid RAW(16); dopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; deq_order_data BOLADM.order_typ; deq_order_nodata BOLADM.order_typ; qname varchar2(30); no_messages exception; pragma exception_init (no_messages, -25228); new_orders BOOLEAN := TRUE; begin dopt.consumer_name := 'Overseas_Shipping'; dopt.wait := DBMS_AQ.NO_WAIT; dopt.navigation := dbms_aq.FIRST_MESSAGE; dopt.dequeue_mode := DBMS_AQ.LOCKED; qname := 'OS.OS_bookedorders_que'; WHILE (new_orders) LOOP BEGIN dbms_aq.dequeue( queue_name => qname, dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); deq_item_data := deq_order_data.items(1); deq_book_data := deq_item_data.item; deq_cust_data := deq_order_data.customer; IF (deq_cust_data.country = 'Canada' OR deq_cust_data.country = 'Mexico' ) THEN dopt.dequeue_mode := dbms_aq.REMOVE_NODATA; dopt.msgid := deq_msgid; dbms_aq.dequeue( queue_name => qname, dequeue_options => dopt, message_properties => mprop, payload => deq_order_nodata, msgid => deq_msgid); dbms_output.put_line(' **** next booked order **** '); dbms_output.put_line('order_no: ' || deq_order_data.orderno || ' book_title: ' || deq_book_data.title || ' quantity: ' || deq_item_data.quantity); dbms_output.put_line('ship_state: ' || deq_cust_data.state || ' ship_country: ' || deq_cust_data.country || ' ship_order_type: ' || deq_order_data.ordertype); END IF; commit; dopt.dequeue_mode := DBMS_AQ.LOCKED; dopt.msgid := NULL; dopt.navigation := dbms_aq.NEXT_MESSAGE; EXCEPTION WHEN no_messages THEN dbms_output.put_line (' ---- NO MORE BOOKED ORDERS ---- '); new_orders := FALSE; END; END LOOP; end; / show errors; grant execute on shipping_bookedorder_deq to WS; grant execute on shipping_bookedorder_deq to ES; grant execute on shipping_bookedorder_deq to OS; grant execute on shipping_bookedorder_deq to CS; grant execute on get_rushtitles to ES; grant execute on get_northamerican_orders to OS; Rem Login into OE account connect OE/OE; set serveroutput on; Rem Rem Enqueue some orders into OE_bookedorders_que Rem execute BOLADM.order_enq('My First Book', 1, 1001, 3,'CA', 'USA', 'WESTERN', 'NORMAL'); execute BOLADM.order_enq('My Second Book', 2, 1002, 3,'NY', 'USA', 'EASTERN', 'NORMAL'); execute BOLADM.order_enq('My Third Book', 3, 1003, 3, '', 'Canada', 'INTERNATIONAL', 'NORMAL'); execute BOLADM.order_enq('My Fourth Book', 4, 1004, 2, 'NV', 'USA', 'WESTERN', 'RUSH'); execute BOLADM.order_enq('My Fifth Book', 5, 1005, 2, 'MA', 'USA', 'EASTERN', 'RUSH'); execute BOLADM.order_enq('My Sixth Book', 6, 1006, 3,'' , 'UK', 'INTERNATIONAL', 'NORMAL'); execute BOLADM.order_enq('My Seventh Book', 7, 1007, 1,'', 'Canada', 'INTERNATIONAL', 'RUSH'); execute BOLADM.order_enq('My Eighth Book', 8, 1008, 3,'', 'Mexico', 'INTERNATIONAL', 'NORMAL'); execute BOLADM.order_enq('My Ninth Book', 9, 1009, 1, 'CA', 'USA', 'WESTERN', 'RUSH'); execute BOLADM.order_enq('My Tenth Book', 8, 1010, 3, '' , 'UK', 'INTERNATIONAL', 'NORMAL'); execute BOLADM.order_enq('My Last Book', 7, 1011, 3, '' , 'Mexico', 'INTERNATIONAL', 'NORMAL'); commit; / Rem Rem Wait for Propagation to Complete Rem execute dbms_lock.sleep(100); Rem ================================================================== Rem Illustrating Dequeue Modes/Methods Rem ================================================================== connect WS/WS; set serveroutput on; Rem Dequeue all booked orders for West_Shipping execute BOLADM.shipping_bookedorder_deq('West_Shipping', DBMS_AQ.REMOVE); commit; / connect ES/ES; set serveroutput on; Rem Browse all booked orders for East_Shipping execute BOLADM.shipping_bookedorder_deq('East_Shipping', DBMS_AQ.BROWSE); Rem Dequeue all rush order titles for East_Shipping execute BOLADM.get_rushtitles('East_Shipping'); commit; / Rem Dequeue all remaining booked orders (normal order) for East_Shipping execute BOLADM.shipping_bookedorder_deq('East_Shipping', DBMS_AQ.REMOVE); commit; / connect OS/OS; set serveroutput on; Rem Dequeue all international North American orders for Overseas_Shipping execute BOLADM.get_northamerican_orders; commit; / Rem Dequeue rest of the booked orders for Overseas_Shipping execute BOLADM.shipping_bookedorder_deq('Overseas_Shipping', DBMS_AQ.REMOVE); commit; / Rem ================================================================== Rem Illustrating Enhanced Propagation Capabilities Rem ================================================================== connect OE/OE; set serveroutput on; Rem Rem Get propagation schedule information & statistics Rem Rem get averages select avg_time, avg_number, avg_size from user_queue_schedules; Rem get totals select total_time, total_number, total_bytes from user_queue_schedules; Rem get status information of schedule (present only when active) select process_name, session_id, instance, schedule_disabled from user_queue_schedules; Rem get information about last and next execution select last_run_date, last_run_time, next_run_date, next_run_time from user_queue_schedules; Rem get last error information if any select failures, last_error_msg, last_error_date, last_error_time from user_queue_schedules; Rem disable propagation schedule for booked orders execute dbms_aqadm.disable_propagation_schedule(queue_name => 'OE_bookedorders_ que'); execute dbms_lock.sleep(30); select schedule_disabled from user_queue_schedules; Rem alter propagation schedule for booked orders to execute every Rem 15 mins (900 seconds) for a window duration of 300 seconds begin dbms_aqadm.alter_propagation_schedule( queue_name => 'OE_bookedorders_que', duration => 300, next_time => 'SYSDATE + 900/86400', latency => 25); end; / execute dbms_lock.sleep(30); select next_time, latency, propagation_window from user_queue_schedules; Rem enable propagation schedule for booked orders execute dbms_aqadm.enable_propagation_schedule(queue_name => 'OE_bookedorders_ que'); execute dbms_lock.sleep(30); select schedule_disabled from user_queue_schedules; Rem unschedule propagation for booked orders execute dbms_aqadm.unschedule_propagation(queue_name => 'OE.OE_bookedorders_ que'); set echo on; Rem ================================================================== Rem Illustrating Message Grouping Rem ================================================================== Rem Login into boladm account set echo on; connect boladm/boladm; set serveroutput on; Rem now create a procedure to handle order entry create or replace procedure new_order_enq(book_title in varchar2, book_qty in number, order_num in number, cust_state in varchar2) as OE_enq_order_data BOLADM.order_typ; OE_enq_cust_data BOLADM.customer_typ; OE_enq_book_data BOLADM.book_typ; OE_enq_item_data BOLADM.orderitem_typ; OE_enq_item_list BOLADM.orderitemlist_vartyp; enqopt dbms_aq.enqueue_options_t; msgprop dbms_aq.message_properties_t; enq_msgid raw(16); begin OE_enq_cust_data := BOLADM.customer_typ(NULL, NULL, NULL, NULL, cust_state, NULL, NULL); OE_enq_book_data := BOLADM.book_typ(book_title, NULL, NULL, NULL); OE_enq_item_data := BOLADM.orderitem_typ(book_qty, OE_enq_book_data, NULL); OE_enq_item_list := BOLADM.orderitemlist_vartyp( BOLADM.orderitem_typ(book_qty, OE_enq_book_data, NULL)); OE_enq_order_data := BOLADM.order_typ(order_num, NULL, NULL, NULL, OE_enq_cust_data, NULL, OE_enq_item_list, NULL); dbms_aq.enqueue('OE.OE_neworders_que', enqopt, msgprop, OE_enq_order_data, enq_msgid); end; / show errors; Rem now create a procedure to handle order enqueue create or replace procedure same_order_enq(book_title in varchar2, book_qty in number) as OE_enq_order_data BOLADM.order_typ; OE_enq_book_data BOLADM.book_typ; OE_enq_item_data BOLADM.orderitem_typ; OE_enq_item_list BOLADM.orderitemlist_vartyp; enqopt dbms_aq.enqueue_options_t; msgprop dbms_aq.message_properties_t; enq_msgid raw(16); begin OE_enq_book_data := BOLADM.book_typ(book_title, NULL, NULL, NULL); OE_enq_item_data := BOLADM.orderitem_typ(book_qty, OE_enq_book_data, NULL); OE_enq_item_list := BOLADM.orderitemlist_vartyp( BOLADM.orderitem_typ(book_qty, OE_enq_book_data, NULL)); OE_enq_order_data := BOLADM.order_typ(NULL, NULL, NULL, NULL, NULL, NULL, OE_enq_item_list, NULL); dbms_aq.enqueue('OE.OE_neworders_que', enqopt, msgprop, OE_enq_order_data, enq_msgid); end; / show errors; grant execute on new_order_enq to OE; grant execute on same_order_enq to OE; Rem now create a procedure to get new orders by dequeuing create or replace procedure get_new_orders as deq_cust_data BOLADM.customer_typ; deq_book_data BOLADM.book_typ; deq_item_data BOLADM.orderitem_typ; deq_msgid RAW(16); dopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; deq_order_data BOLADM.order_typ; qname varchar2(30); no_messages exception; end_of_group exception; pragma exception_init (no_messages, -25228); pragma exception_init (end_of_group, -25235); new_orders BOOLEAN := TRUE; begin dopt.wait := 1; dopt.navigation := DBMS_AQ.FIRST_MESSAGE; qname := 'OE.OE_neworders_que'; WHILE (new_orders) LOOP BEGIN LOOP BEGIN dbms_aq.dequeue( queue_name => qname, dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); deq_item_data := deq_order_data.items(1); deq_book_data := deq_item_data.item; deq_cust_data := deq_order_data.customer; IF (deq_cust_data IS NOT NULL) THEN dbms_output.put_line(' **** NEXT ORDER **** '); dbms_output.put_line('order_num: ' || deq_order_data.orderno); dbms_output.put_line('ship_state: ' || deq_cust_data.state); END IF; dbms_output.put_line(' ---- next book ---- '); dbms_output.put_line(' book_title: ' || deq_book_data.title || ' quantity: ' || deq_item_data.quantity); EXCEPTION WHEN end_of_group THEN dbms_output.put_line ('*** END OF ORDER ***'); commit; dopt.navigation := DBMS_AQ.NEXT_TRANSACTION; END; END LOOP; EXCEPTION WHEN no_messages THEN dbms_output.put_line (' ---- NO MORE NEW ORDERS ---- '); new_orders := FALSE; END; END LOOP; end; / show errors; grant execute on get_new_orders to OE; Rem Login into OE account connect OE/OE; set serveroutput on; Rem Rem Enqueue some orders using message grouping into OE_neworders_que Rem Rem First Order execute BOLADM.new_order_enq('My First Book', 1, 1001, 'CA'); execute BOLADM.same_order_enq('My Second Book', 2); commit; / Rem Second Order execute BOLADM.new_order_enq('My Third Book', 1, 1002, 'WA'); commit; / Rem Third Order execute BOLADM.new_order_enq('My Fourth Book', 1, 1003, 'NV'); execute BOLADM.same_order_enq('My Fifth Book', 3); execute BOLADM.same_order_enq('My Sixth Book', 2); commit; / Rem Fourth Order execute BOLADM.new_order_enq('My Seventh Book', 1, 1004, 'MA'); execute BOLADM.same_order_enq('My Eighth Book', 3); execute BOLADM.same_order_enq('My Ninth Book', 2); commit; / Rem Rem Dequeue the neworders Rem execute BOLADM.get_new_orders;
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|