Create the AQ Environment and Queue Tables

Run the AQ schema scripts as follows:

• SQL Example 3: Set Up the Environment for AQ -- mkAQUser.sql

• SQL Example 4: Call the AQ Queue Creation Scripts -- mkQ.sql. This calls:
  • SQL (PL/SQL) Example 5: Create Table, AppOne_QTab -- mkQueueTableApp1.sql

  • SQL (PL/SQL) Example 6: Create Table, AppTwo_QTab -- mkQueueTableApp2.sql

  • SQL (PL/SQL) Example 7: Create Table, AppThree_QTab -- mkQueueTableApp3.sql

  • SQL (PL/SQL) Example 8: Create Table, AppFour_QTab -- mkQueueTableApp4.sql

SQL Example 3: Set Up the Environment for AQ -- mkAQUser.sql

The following SQL script sets up the environment for using AQ, creates user aqMessBrok, creates default and temporary tablespace, grants execute privileges on the AQ PL/SQL packages dbms_aqadm and dbms_aq to aqMessBrok.

set ver off
set scan on
prompt Creating environment for Advanced Queuing
accept mgrPsw prompt 'Please enter the SYSTEM password                                 
> ' hide
accept cStr   prompt 'Please enter the the DB Alias if any, WITH the @ sign (ie 
@Ora8i)> '
connect system/&mgrPsw&cStr

col tablespace_name head "Available Tablespaces"
select tablespace_name from dba_tablespaces
/
Prompt
accept userTbsp prompt 'What is the DEFAULT Tablespace name ?   > '
accept tempTbsp prompt 'What is the TEMPORARY Tablespace name ? > '
prompt

prompt Creating aqMessBrok
create user aqMessBrok identified by aqMessBrok
default tablespace &userTbsp
temporary tablespace &tempTbsp
quota unlimited on &userTbsp
/
grant connect, resource, aq_administrator_role, create any directory to 
aqMessBrok
/
grant execute on dbms_aqadm to aqMessBrok
/
grant execute on dbms_aq to aqMessBrok
/

SQL Example 4: Call the AQ Queue Creation Scripts -- mkQ.sql

This script calls four scripts to create the AQ queue tables.

@mkQueueTableApp1
@mkQueueTableApp2
@mkQueueTableApp3
@mkQueueTableApp4

SQL (PL/SQL) Example 5: Create Table, AppOne_QTab -- mkQueueTableApp1.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 1, AppOne_QTab.

execute dbms_aqadm.create_queue_table (queue_table => 'AppOne_QTab', queue_
payload_type => 'RAW');

SQL (PL/SQL) Example 6: Create Table, AppTwo_QTab -- mkQueueTableApp2.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 2, AppTwo_QTab.

execute dbms_aqadm.create_queue_table (queue_table => 'AppTwo_QTab', queue_
payload_type => 'RAW');

SQL (PL/SQL) Example 7: Create Table, AppThree_QTab -- mkQueueTableApp3.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 3, AppThree_QTab.

execute dbms_aqadm.create_queue_table (queue_table => 'AppThree_QTab', queue_
payload_type => 'RAW');

SQL (PL/SQL) Example 8: Create Table, AppFour_QTab -- mkQueueTableApp4.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 4, AppFour_QTab.

execute dbms_aqadm.create_queue_table (queue_table => 'AppFour_QTab', queue_
payload_type => 'RAW');