Rem
Rem $Header: template.sql 06-feb-96.13:23:14 kosinski Exp $
Rem
Rem aqdemo01.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      aqdemo01.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rbhyrava    07/10/00 - Bug - 1319922
Rem    rbhyrava    07/10/00 - Created
Rem
Rem
Rem aqdemo00.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      aqdemo00.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem
rem     NAME
rem     aqdemo00.sql
rem
rem     DESCRIPTION
rem     This set of scripts serve as an example for building an
rem     application, using Oracle Advanced Queues, to perform
rem     asynchronous database operations.
rem
rem     The scripts do the following:
rem
rem     aqdemo00
rem     1) Create aquser as an user of AQ
rem     2) Create tables prog1_processed_data, prog2_processed_data and
rem        prog3_processed_data
rem
rem     aqdemo01
rem     1) Create two queue tables - input_queue_table, prop_queue_table
rem     2) Create two queues - input_queue belonging to input_queue_table,
rem        prop_queue belonging to prop_queue_table
rem     3) Create two subscribers to input_queue - prog1, prog2
rem     4) Create one subscribers to input_queue - prog3 at prop_queue
rem     5) Schedule propagation between input_queue and other queues in
rem        the database
rem
rem     aqdemo02
rem     1) Enqueue 100 messages into the input_queue
rem
rem     aqdemo03
rem     1) Installs the dequeue procedures
rem
rem     aqdemo04
rem     1) prog3 performs a blocking dequeue from prop_queue. Messages
rem        in prop_table were propagated from input_queue.
rem
rem     aqdemo05
rem     1) A listener program listens on input_table and calls
rem        prog1 or prog2 to dequeue from input_queue based on the
rem        kind of message received.
rem
rem     aqdemo06
rem     1) This script cleans up all the tables, types, queues,
rem        queue_tables, users etc. created by
rem        aqdemo00 - aqdemo05
rem
rem    NOTES
rem      This file contains the sql script that drives the demo.
rem      Before running the demo, add the following lines to
rem      your init.ora file:
rem        compatible = 8.1.0
rem        aq_tm_processes = 1
rem        job_queue_processes = 2
rem        job_queue_interval = 1
rem      shutdown and restart the database.
rem
rem      Run this demo as SYS using SQLPLUS.  Just login as SYS
rem      in SQLPLUS and type '@aqdemo00'.
rem      aqdemo00 calls aqdemo01 and aqdemo03.
rem
rem      Log into another SQLPUS session and type '@aqdemo02'.
rem      This enqueus 100 messages into input_queue
rem
rem      Log into another SQLPLUS session and type '@aqdemo04'.
rem      This program blocks on prop_queue for approxmately 2 minutes and
rem      dequeues messages.
rem
rem      Log into another SQLPLUS session and type '@aqdemo05'.
rem      This program listens for approxmately 2 minutes on input_queue
rem      and calls the dequeue  for prog1 and prog2 appropriately.
rem
rem      aqdemo02(enqueue), aqdemo04(blocking dequeue) and
rem      aqdemo05(listen) can be run concurrently
rem
rem      aqdemo06(cleanup script) has to be run by SYS.
rem      Login as SYS in SQLPLUS and type '@aqdemo06'.
rem

set serveroutput on
set echo on
spool aqdemo00.log
rem ====================================================================
rem create a queue user
rem ====================================================================

grant connect, resource to aquser identified by aquser
/
grant execute on dbms_aq to aquser
/
grant execute on dbms_aqadm to aquser
/
grant execute on dbms_lock to aquser
/


connect aquser/aquser
set serveroutput on
set echo on

rem ====================================================================
rem
rem Create a type
rem
rem ====================================================================

create type message as object (
    id NUMBER,
    city VARCHAR2(30),
    priority NUMBER)
/

rem ====================================================================
rem
rem  Create the table to store the dequeued data
rem
rem ====================================================================
drop table prog1_processed_data
/
drop table prog2_processed_data
/
drop table prog3_processed_data
/

create table prog1_processed_data
(
    id NUMBER,
    city VARCHAR2(30),
    priority NUMBER
)
/

create table prog2_processed_data
(
    id NUMBER,
    city VARCHAR2(30),
    priority NUMBER
)
/

create table prog3_processed_data
(
    id NUMBER,
    city VARCHAR2(30),
    priority NUMBER
)
/

rem ====================================================================
rem   Setup complete
rem ====================================================================


rem Set up queue tables, queues, subscribers etc.
@@aqdemo01.sql
rem Load dequeue procedures
@@aqdemo03.sql



rem ====================================================================
rem   Setup complete
rem ====================================================================

spool off
