tkaqdoca.sql: Script to Create Users, Objects, Queue Tables, Queues & Subscribers

Rem $Header: tkaqdoca.sql 26-jan-99.17:50:37 aquser1 Exp $
Rem
Rem tkaqdoca.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      tkaqdoca.sql - TKAQ DOCumentation Admin examples file 

Rem Set up a queue admin account and individual accounts for each application
Rem
connect system/manager
set serveroutput on;
set echo on;

Rem Create a common admin account for all BooksOnLine applications
Rem
create user BOLADM identified by BOLADM;
grant connect, resource, aq_administrator_role to BOLADM;
grant execute on dbms_aq to BOLADM;
grant execute on dbms_aqadm to BOLADM;
execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','BOLADM',FALSE);
execute dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','BOLADM',FALSE);

Rem Create the application schemas and grant appropriate permission 
Rem to all schemas

Rem Create an account for Order Entry
create user OE identified by OE;
grant connect, resource to OE;
grant execute on dbms_aq to OE;
grant execute on dbms_aqadm to OE;

Rem Create an account for WR Shipping
create user WS identified by WS;
grant connect, resource to WS;
grant execute on dbms_aq to WS;
grant execute on dbms_aqadm to WS;

Rem Create an account for ER Shipping
create user ES identified by ES;
grant connect, resource to ES;
grant execute on dbms_aq to ES;
grant execute on dbms_aqadm to ES;

Rem Create an account for Overseas Shipping
create user OS identified by OS;
grant connect, resource to OS;
grant execute on dbms_aq to OS;
grant execute on dbms_aqadm to OS;

Rem Create an account for Customer Billing
Rem Customer Billing, for security reason, has an admin schema that
Rem hosts all the queue tables and an application schema from where
Rem the application runs.
create user CBADM identified by CBADM;
grant connect, resource to CBADM;
grant execute on dbms_aq to CBADM;
grant execute on dbms_aqadm to CBADM;

create user CB identified by CB;
grant connect, resource to CB;
grant execute on dbms_aq to CB;
grant execute on dbms_aqadm to CB;

Rem Create an account for Customer Service
create user CS identified by CS;
grant connect, resource to CS;
grant execute on dbms_aq to CS;
grant execute on dbms_aqadm to CS;


Rem All object types are created in the administrator schema.
Rem All application schemas that host any propagation source
Rem queues are given the ENQUEUE_ANY system level privilege
Rem allowing the application schemas to enqueue to the destination
Rem queue.
Rem 
connect BOLADM/BOLADM;

Rem Create objects

create or replace type customer_typ as object (
        custno          number,
        name            varchar2(100),
        street          varchar2(100),
        city            varchar2(30),
        state           varchar2(2),
        zip             number,
        country         varchar2(100));
/

create or replace type book_typ as object (
        title           varchar2(100),
        authors         varchar2(100),
        ISBN            number,
        price           number);
/

create or replace type orderitem_typ as object (
        quantity        number,
        item            book_typ,
        subtotal        number);
/

create or replace type orderitemlist_vartyp as varray (20) of orderitem_typ;
/

create or replace type order_typ as object (
        orderno         number,
        status          varchar2(30),
        ordertype       varchar2(30),
        orderregion     varchar2(30),
        customer        customer_typ,
        paymentmethod   varchar2(30),
        items           orderitemlist_vartyp,
        total           number);                
/

grant execute on order_typ to OE;
grant execute on orderitemlist_vartyp to OE;
grant execute on orderitem_typ to OE;
grant execute on book_typ to OE;
grant execute on customer_typ to OE;
execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','OE',FALSE);
 
grant execute on order_typ to WS;
grant execute on orderitemlist_vartyp to WS;
grant execute on orderitem_typ to WS;
grant execute on book_typ to WS;
grant execute on customer_typ to WS;
execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','WS',FALSE);
 
grant execute on order_typ to ES;
grant execute on orderitemlist_vartyp to ES;
grant execute on orderitem_typ to ES;
grant execute on book_typ to ES;
grant execute on customer_typ to ES;
execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','ES',FALSE);
 
grant execute on order_typ to OS;
grant execute on orderitemlist_vartyp to OS;
grant execute on orderitem_typ to OS;
grant execute on book_typ to OS;
grant execute on customer_typ to OS;
execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','OS',FALSE); 

grant execute on order_typ to CBADM;
grant execute on orderitemlist_vartyp to CBADM;
grant execute on orderitem_typ to CBADM;
grant execute on book_typ to CBADM;
grant execute on customer_typ to CBADM;

grant execute on order_typ to CB;
grant execute on orderitemlist_vartyp to CB;
grant execute on orderitem_typ to CB;
grant execute on book_typ to CB;
grant execute on customer_typ to CB;
 
grant execute on order_typ to CS;
grant execute on orderitemlist_vartyp to CS;
grant execute on orderitem_typ to CS;
grant execute on book_typ to CS;
grant execute on customer_typ to CS;

Rem Create queue tables, queues for OE
Rem 
connect OE/OE;
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'OE_orders_sqtab',               
        comment => 'Order Entry Single Consumer Orders queue table',    
        queue_payload_type => 'BOLADM.order_typ',                       
        message_grouping => DBMS_AQADM.TRANSACTIONAL,                   
        compatible => '8.1',                                    
        primary_instance => 1,                                  
        secondary_instance => 2);
end;
/

Rem Create a priority queue table for OE
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'OE_orders_pr_mqtab',    
        sort_list =>'priority,enq_time', 
        comment => 'Order Entry Priority MultiConsumer Orders queue table', 
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1',                                    
        primary_instance => 2,                                  
        secondary_instance => 1);
end;
/

begin
dbms_aqadm.create_queue (                                   
        queue_name              => 'OE_neworders_que',        
        queue_table             => 'OE_orders_sqtab');
end;
/

begin
dbms_aqadm.create_queue (                                
        queue_name              => 'OE_bookedorders_que',       
        queue_table             => 'OE_orders_pr_mqtab');
end;
/

Rem Orders in OE_bookedorders_que are being propagated to WS_bookedorders_que,
Rem ES_bookedorders_que and OS_bookedorders_que according to the region
Rem the books are shipped to.  At the time an order is placed, the customer
Rem can request Fed-ex shipping (priority 1), priority air shipping (priority
Rem 2) and ground shipping (priority 3).  An priority queue is created in
Rem each region, the shipping applications will dequeue from these priority
Rem queues according to the orders' shipping priorities, processes the orders
Rem and enqueue the processed orders into
Rem the shipped_orders queues or the back_orders queues.  Both the shipped_
Rem orders queues and the back_orders queues are FIFO queues. However, 
Rem orders put into the back_orders_queues are enqueued with delay time
Rem set to 1 day, so that each order in the back_order_queues is processed
Rem only once a day until the shipment is filled.
 
Rem Create queue tables, queues for WS Shipping
connect WS/WS;

Rem Create a priority queue table for WS shipping
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'WS_orders_pr_mqtab',            
        sort_list =>'priority,enq_time', 
        comment => 'West Shipping Priority MultiConsumer Orders queue table', 
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');
end;
/

Rem Create a FIFO queue tables for WS shipping
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'WS_orders_mqtab',               
        comment => 'West Shipping Multi Consumer Orders queue table',   
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');
end;
/

Rem Booked orders are stored in the priority queue table
begin
dbms_aqadm.create_queue (                                   
        queue_name              => 'WS_bookedorders_que',        
        queue_table             => 'WS_orders_pr_mqtab');
end;
/

Rem Shipped orders and back orders are stored in the FIFO queue table
begin
dbms_aqadm.create_queue (                                
        queue_name              => 'WS_shippedorders_que',       
        queue_table             => 'WS_orders_mqtab');
end;
/


begin
dbms_aqadm.create_queue (                                 
        queue_name              => 'WS_backorders_que',           
        queue_table             => 'WS_orders_mqtab');
end;
/

Rem
Rem  In order to test history, set retention to 1 DAY for the queues
Rem  in WS

begin
dbms_aqadm.alter_queue(    
         queue_name => 'WS_bookedorders_que', 
         retention_time => 86400);
end;
/

begin
dbms_aqadm.alter_queue(    
         queue_name => 'WS_shippedorders_que', 
         retention_time => 86400);
end;
/

begin
dbms_aqadm.alter_queue(    
         queue_name => 'WS_backorders_que', 
         retention_time => 86400);
end;
/


Rem Create queue tables, queues for ES Shipping
connect ES/ES;

Rem Create a priority queue table for ES shipping
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'ES_orders_mqtab',               
        comment => 'East Shipping Multi Consumer Orders queue table',   
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');
end;
/

Rem Create a FIFO queue tables for ES shipping
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'ES_orders_pr_mqtab',            
        sort_list =>'priority,enq_time', 
        comment => 'East Shipping Priority Multi Consumer Orders queue table',
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');
end;
/

Rem Booked orders are stored in the priority queue table
begin
dbms_aqadm.create_queue (                                   
        queue_name              => 'ES_bookedorders_que',        
        queue_table             => 'ES_orders_pr_mqtab');
end;
/

Rem Shipped orders and back orders are stored in the FIFO queue table
begin
dbms_aqadm.create_queue (                                
        queue_name              => 'ES_shippedorders_que',       
        queue_table             => 'ES_orders_mqtab');
end;
/

begin
dbms_aqadm.create_queue (                                 
        queue_name              => 'ES_backorders_que',           
        queue_table             => 'ES_orders_mqtab');
end;
/

 
Rem Create queue tables, queues for Overseas Shipping
connect OS/OS;

Rem Create a priority queue table for OS shipping
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'OS_orders_pr_mqtab',            
        sort_list =>'priority,enq_time', 
        comment => 'Overseas Shipping Priority MultiConsumer Orders queue 
table', 
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');
end;
/

Rem Create a FIFO queue tables for OS shipping
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'OS_orders_mqtab',               
        comment => 'Overseas Shipping Multi Consumer Orders queue table', 
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');
end;
/

Rem Booked orders are stored in the priority queue table
begin
dbms_aqadm.create_queue (                                   
        queue_name              => 'OS_bookedorders_que',        
        queue_table             => 'OS_orders_pr_mqtab');
end;
/

Rem Shipped orders and back orders are stored in the FIFO queue table
begin
dbms_aqadm.create_queue (                                
        queue_name              => 'OS_shippedorders_que',       
        queue_table             => 'OS_orders_mqtab');
end;
/

begin
dbms_aqadm.create_queue (                                 
        queue_name              => 'OS_backorders_que',           
        queue_table             => 'OS_orders_mqtab');
end;
/


Rem Create queue tables, queues for Customer Billing
connect CBADM/CBADM;
begin
dbms_aqadm.create_queue_table(                          
        queue_table => 'CBADM_orders_sqtab',            
        comment => 'Customer Billing Single Consumer Orders queue table',
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');

dbms_aqadm.create_queue_table(                          
        queue_table => 'CBADM_orders_mqtab',            
        comment => 'Customer Billing Multi Consumer Service queue table', 
        multiple_consumers => TRUE,                             
        queue_payload_type => 'BOLADM.order_typ',                       
        compatible => '8.1');

dbms_aqadm.create_queue (                                   
        queue_name              => 'CBADM_shippedorders_que',        
        queue_table             => 'CBADM_orders_sqtab');

end;
/

Rem Grant dequeue privilege on the shopped orders queue to the Customer Billing
Rem application.  The CB application retrieves shipped orders (not billed yet)
Rem from the shopped orders queue.
execute dbms_aqadm.grant_queue_privilege('DEQUEUE', 'CBADM_shippedorders_que', 
'CB', FALSE);

begin
dbms_aqadm.create_queue (                                
        queue_name              => 'CBADM_billedorders_que',       
        queue_table             => 'CBADM_orders_mqtab');
end;
/

Rem Grant enqueue privilege on the billed orders queue to Customer Billing
Rem application.  The CB application is allowed to put billed orders into
Rem this queue.
execute dbms_aqadm.grant_queue_privilege('ENQUEUE', 'CBADM_billedorders_que', 
'CB', FALSE);


Rem Customer support tracks the state of the customer request in the system 
Rem  
Rem At any point, customer request can be in one of the following states
Rem  A. BOOKED  B. SHIPPED  C. BACKED   D. BILLED
Rem  Given the order number the customer support will return the state
Rem  the order is in. This state is maintained in the order_status_table

connect CS/CS;

CREATE TABLE Order_Status_Table(customer_order       boladm.order_typ, 
                                status               varchar2(30));

Rem Create queue tables, queues for Customer Service

begin
dbms_aqadm.create_queue_table(                            
        queue_table => 'CS_order_status_qt',                      
        comment => 'Customer Status multi consumer queue table',  
        multiple_consumers => TRUE,                               
        queue_payload_type => 'BOLADM.order_typ',                 
        compatible => '8.1');

dbms_aqadm.create_queue (                                   
        queue_name              => 'CS_bookedorders_que',           
        queue_table             => 'CS_order_status_qt');

dbms_aqadm.create_queue (                                   
        queue_name              => 'CS_backorders_que',           
        queue_table             => 'CS_order_status_qt');

dbms_aqadm.create_queue (                                   
        queue_name              => 'CS_shippedorders_que',          
        queue_table             => 'CS_order_status_qt');

dbms_aqadm.create_queue (                                   
        queue_name              => 'CS_billedorders_que',           
        queue_table             => 'CS_order_status_qt');

end;
/

Rem Create the Subscribers for OE queues
Rem Add the Subscribers for the OE booked_orders queue

connect OE/OE;

Rem Add a rule-based subscriber for West Shipping
Rem West Shipping handles Western region US orders
Rem Rush Western region orders are handled by East Shipping
declare
  subscriber     aq$_agent;
begin
  subscriber := aq$_agent('West_Shipping', 'WS.WS_bookedorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'OE.OE_bookedorders_que',
                            subscriber => subscriber,
                            rule       => 'tab.user_data.orderregion = 
''WESTERN'' AND tab.user_data.ordertype != ''RUSH''');
end;
/

Rem Add a rule-based subscriber for East Shipping
Rem East shipping handles all Eastern region orders
Rem East shipping also handles all US rush orders
declare
  subscriber     aq$_agent;
begin
  subscriber := aq$_agent('East_Shipping', 'ES.ES_bookedorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'OE.OE_bookedorders_que',
                            subscriber => subscriber,
                            rule       => 'tab.user_data.orderregion = 
''EASTERN'' OR (tab.user_data.ordertype = ''RUSH'' AND tab.user_
data.customer.country = ''USA'') ');
end;
/

Rem Add a rule-based subscriber for Overseas Shipping
Rem Intl Shipping handles all non-US orders
declare
  subscriber     aq$_agent;
begin
  subscriber := aq$_agent('Overseas_Shipping', 'OS.OS_bookedorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'OE.OE_bookedorders_que',
                            subscriber => subscriber,
                            rule       => 'tab.user_data.orderregion = 
''INTERNATIONAL''');
end;
/

Rem Add the Customer Service order queues as a subscribers to the
Rem corresponding queues  in OrderEntry, Shipping and Billing

declare
  subscriber     aq$_agent;
begin
  /* Subscribe to the booked orders queue */
  subscriber := aq$_agent('BOOKED_ORDER', 'CS.CS_bookedorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'OE.OE_bookedorders_que',
                            subscriber => subscriber);
end;
/


connect WS/WS;

declare
  subscriber     aq$_agent;
begin
  /* Subscribe to the WS back orders queue */
  subscriber := aq$_agent('BACK_ORDER', 'CS.CS_backorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'WS.WS_backorders_que',
                            subscriber => subscriber);
end;
/

declare
  subscriber     aq$_agent;
begin
  /* Subscribe to the WS shipped orders queue */
  subscriber := aq$_agent('SHIPPED_ORDER', 'CS.CS_shippedorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'WS.WS_shippedorders_que',
                            subscriber => subscriber);
end;
/

connect CBADM/CBADM;
declare
  subscriber     aq$_agent;
begin
  /* Subscribe to the BILLING billed orders queue */
  subscriber := aq$_agent('BILLED_ORDER', 'CS.CS_billedorders_que', null);
  dbms_aqadm.add_subscriber(queue_name => 'CBADM.CBADM_billedorders_que',
                            subscriber => subscriber);

end;
/


Rem
Rem BOLADM will Start all the queues
Rem
connect BOLADM/BOLADM
execute dbms_aqadm.start_queue(queue_name => 'OE.OE_neworders_que');        
execute dbms_aqadm.start_queue(queue_name => 'OE.OE_bookedorders_que');       
execute dbms_aqadm.start_queue(queue_name => 'WS.WS_bookedorders_que');        
execute dbms_aqadm.start_queue(queue_name => 'WS.WS_shippedorders_que');       
execute dbms_aqadm.start_queue(queue_name => 'WS.WS_backorders_que');
execute dbms_aqadm.start_queue(queue_name => 'ES.ES_bookedorders_que');        
execute dbms_aqadm.start_queue(queue_name => 'ES.ES_shippedorders_que');       
execute dbms_aqadm.start_queue(queue_name => 'ES.ES_backorders_que');  
execute dbms_aqadm.start_queue(queue_name => 'OS.OS_bookedorders_que');        
execute dbms_aqadm.start_queue(queue_name => 'OS.OS_shippedorders_que');       
execute dbms_aqadm.start_queue(queue_name => 'OS.OS_backorders_que');  
execute dbms_aqadm.start_queue(queue_name => 'CBADM.CBADM_shippedorders_que');
execute dbms_aqadm.start_queue(queue_name => 'CBADM.CBADM_billedorders_que'); 
execute dbms_aqadm.start_queue(queue_name => 'CS.CS_bookedorders_que');
execute dbms_aqadm.start_queue(queue_name => 'CS.CS_backorders_que');
execute dbms_aqadm.start_queue(queue_name => 'CS.CS_shippedorders_que');
execute dbms_aqadm.start_queue(queue_name => 'CS.CS_billedorders_que');

connect system/manager

Rem
Rem Start job_queue_processes to handle AQ propagation
Rem

alter system set job_queue_processes=4;