Cleaning Up Your Environment and Preparing to Rerun Application

Run reset.sql to clean up your environment and rerun this application.

• SQL Example 11: Stops and Drops Queue Applications. Starts Queue Applications -- reset.sql

  This calls the following 16 PL/SQL scripts:

  • stopQueueApp1.sql

  • stopQueueApp2.sql

  • stopQueueApp3.sql

  • stopQueueApp4.sql

  • dropQueueApp1.sql

  • dropQueueApp2.sql

  • dropQueueApp3.sql

  • dropQueueApp4.sql

  • createQueueApp1.sql

  • createQueueApp2.sql

  • createQueueApp3.sql

  • createQueueApp4.sql

  • startQueueApp1.sql

  • startQueueApp2.sql

  • startQueueApp3.sql

  • startQueueApp4.sql

SQL Example 11: Stops and Drops Queue Applications. Starts Queue Applications -- reset.sql

reset.sql script first stops all four queue applications by calling the stopQueueApp1 through 4, then drops them by calling dropQueueApp1 through 4, and restarts them by calling startQueueApp1 through 4.

The script also prompts you to Hit Return to Exit.

connect aqMessBrok/aqMessBrok
start stopQueueApp1
start stopQueueApp2
start stopQueueApp3
start stopQueueApp4
start dropQueueApp1
start dropQueueApp2
start dropQueueApp3
start dropQueueApp4
start createQueueApp1
start createQueueApp2
start createQueueApp3
start createQueueApp4
start startQueueApp1
start startQueueApp2
start startQueueApp3
start startQueueApp4
prompt Press [Return] to exit !
pause
exit

Stop Queue SQL Scripts

These four scripts are called from reset.sql.They use PL/SQL procedure dbms_aqadm.stop_queue to stop the queues.

stopQueueApp1.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppOneMsgQueue');

stopQueueApp2.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppTwoMsgQueue');

stopQueueApp3.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppThreeMsgQueue');

stopQueueApp4.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppFourMsgQueue');

Drop Queue SQL Scripts

These four scripts are called from reset.sql. They use PL/SQL procedure dbms_aqadm.drop_queue to drop the queues.

dropQueueApp1.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppOneMsgQueue');

dropQueueApp2.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppTwoMsgQueue');

dropQueueApp3.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppThreeMsgQueue');

dropQueueApp4.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppFourMsgQueue');

Create Queue SQL Scripts

These four scripts are called from reset.sql. They use PL/SQL procedure, dbms_aqadm.create_queue to create the queues.

createQueueApp1.sql

execute dbms_aqadm.create_queue (queue_name=>'AppOneMsgQueue', queue_
table=>'AppOne_QTab');

createQueueApp2.sql

execute dbms_aqadm.create_queue (queue_name=>'AppTwoMsgQueue', queue_
table=>'AppTwo_QTab');

createQueueApp3.sql

execute dbms_aqadm.create_queue (queue_name=>'AppThreeMsgQueue', queue_
table=>'AppThree_QTab');

createQueueApp4.sql

execute dbms_aqadm.create_queue (queue_name=>'AppFourMsgQueue', queue_
table=>'AppFour_QTab');

Start Queue SQL Scripts

These four scripts are called from reset.sql. They use PL/SQL procedure, dbms_aqadm.start_queue to start the queues.

startQueueApp1.sql

execute dbms_aqadm.start_queue(queue_name=>'AppOneMsgQueue');

startQueueApp2.sql

execute dbms_aqadm.start_queue (queue_name=>'AppTwoMsgQueue');

startQueueApp3.sql

execute dbms_aqadm.start_queue (queue_name=>'AppThreeMsgQueue');

startQueueApp4.sql

execute dbms_aqadm.start_queue (queue_name=>'AppFourMsgQueue');

dropOrder.sql

This SQL script deletes orders from the Retailer-Supplier database Customers table according to the customer's ID.

set ver off
accept CustName prompt 'Drop all for customer named > ' 

Delete LINE_ITEM I
Where I.ORD_ID in
(Select O.ID
 From ORD O
 Where O.CUSTOMER_ID in
 (Select C.ID
  From CUSTOMER C
  Where Upper(C.NAME) = Upper('&CustName')))
/
Delete ORD O
Where O.CUSTOMER_ID in
(Select C.ID
 From CUSTOMER C
 Where Upper(C.NAME) = Upper('&CustName'))
/