Creating Queue Tables and Queues

Note: You may need to set up the following data structures for certain examples to work: CONNECT system/manager; DROP USER aqadm CASCADE; GRANT CONNECT, RESOURCE TO aqadm; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; GRANT Aq_administrator_role TO aqadm; DROP USER aq CASCADE; CREATE USER aq IDENTIFIED BY aq; GRANT CONNECT, RESOURCE TO aq; GRANT EXECUTE ON dbms_aq TO aq;

Creating a Queue Table and Queue of Object Type

/* Creating a message type: */
CREATE type aq.Message_typ as object (
subject     VARCHAR2(30),
text        VARCHAR2(80));   

/* Creating a object type queue table and queue: */
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.objmsgs80_qtab',
queue_payload_type => 'aq.Message_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => 'msg_queue',
queue_table        => 'aq.objmsgs80_qtab');

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => 'msg_queue');

Creating a Queue Table and Queue of Raw Type

/* Creating a RAW type queue table and queue: */
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( 
queue_table          => 'aq.RawMsgs_qtab', 
queue_payload_type   => 'RAW'); 
  
EXECUTE DBMS_AQADM.CREATE_QUEUE ( 
queue_name          => 'raw_msg_queue', 
queue_table         => 'aq.RawMsgs_qtab'); 
  
EXECUTE DBMS_AQADM.START_QUEUE ( 
queue_name          => 'raw_msg_queue'); 

Creating a Prioritized Message Queue Table and Queue

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.priority_msg', 
sort_list          => 'PRIORITY,ENQ_TIME', 
queue_payload_type => 'aq.Message_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => 'priority_msg_queue', 
queue_table        => 'aq.priority_msg');

EXECUTE DBMS_AQADM.START_QUEUE ( 
queue_name         => 'priority_msg_queue');

Creating a Multiple-Consumer Queue Table and Queue

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.MultiConsumerMsgs_qtab',
multiple_consumers => TRUE, 
queue_payload_type => 'aq.Message_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => 'msg_queue_multiple',
queue_table        => 'aq.MultiConsumerMsgs_qtab');

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => 'msg_queue_multiple');

Creating a Queue to Demonstrate Propagation

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name        => 'another_msg_queue',
queue_table       => 'aq.MultiConsumerMsgs_qtab');

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => 'another_msg_queue');

Setting Up Java AQ Examples

CONNECT system/manager

DROP USER aqjava CASCADE;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE TO aqjava IDENTIFIED BY aqjava;
GRANT EXECUTE ON DBMS_AQADM TO aqjava;
GRANT EXECUTE ON DBMS_AQ TO aqjava;
CONNECT aqjava/aqjava

/* Set up main class from which we will call subsequent examples and handle 
   exceptions: */
import java.sql.*;
import oracle.AQ.*;

public class test_aqjava
{
   public static void main(String args[]) 
   {
      AQSession  aq_sess = null;
      try 
      {
         aq_sess = createSession(args);

        /* now run the test: */
        runTest(aq_sess);     
      }
      catch (Exception ex)
      {
         System.out.println("Exception-1: " + ex); 
         ex.printStackTrace();      
      }  
   }
}

Creating an Java AQ Session

/* Creating an Java AQ Session for the 'aqjava' user as shown in the 
   AQDriverManager section above: */
 public static AQSession createSession(String args[]) 
   {
      Connection db_conn;
      AQSession  aq_sess = null;

      try 
      {
    
         Class.forName("oracle.jdbc.driver.OracleDriver");
         /* your actual hostname, port number, and SID will 
         vary from what follows. Here we use 'dlsun736,' '5521,'
         and 'test,' respectively: */

         db_conn =
                  DriverManager.getConnection(
                  "jdbc:oracle:thin:@dlsun736:5521:test", 
                  "aqjava", "aqjava");

         System.out.println("JDBC Connection opened "); 
         db_conn.setAutoCommit(false);
                 
         /* Load the Oracle8i AQ driver: */
         Class.forName("oracle.AQ.AQOracleDriver");

         /* Creating an AQ Session: */
         aq_sess = AQDriverManager.createAQSession(db_conn);
         System.out.println("Successfully created AQSession ");  
      }
      catch (Exception ex)
      {
         System.out.println("Exception: " + ex); 
         ex.printStackTrace();      
      }  
      return aq_sess;
   }

Creating a Queue Table and Queue Using Java

public static void runTest(AQSession aq_sess) throws AQException
{
    AQQueueTableProperty     qtable_prop;
    AQQueueProperty          queue_prop;
    AQQueueTable             q_table;
    AQQueue                  queue;
   
    /* Creating a AQQueueTableProperty object (payload type - RAW): */
    qtable_prop = new AQQueueTableProperty("RAW"); 
   
    /* Creating a queue table called aq_table1 in aqjava schema: */
    q_table = aq_sess.createQueueTable ("aqjava", "aq_table1", qtable_prop);
    System.out.println("Successfully created aq_table1 in aqjava schema");  

    /* Creating a new AQQueueProperty object */
    queue_prop = new AQQueueProperty();
   
    /* Creating a queue called aq_queue1 in aq_table1: */
    queue = aq_sess.createQueue (q_table, "aq_queue1", queue_prop);
    System.out.println("Successfully created aq_queue1 in aq_table1");  
}

/* Get a handle to an existing queue table and queue: */
public static void runTest(AQSession aq_sess) throws AQException
{
    AQQueueTable            q_table;
    AQQueue                 queue;
    
    /* Get a handle to queue table - aq_table1 in aqjava schema: */
    q_table = aq_sess.getQueueTable ("aqjava", "aq_table1");
    System.out.println("Successful getQueueTable");  
    
    /* Get a handle to a queue - aq_queue1 in aqjava schema: */
    queue = aq_sess.getQueue ("aqjava", "aq_queue1");
    System.out.println("Successful getQueue");  
}

Creating a Queue and Start Enqueue/Dequeue Using Java

{
     AQQueueTableProperty    qtable_prop;
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;

     /* Creating a AQQueueTable property object (payload type - RAW): */
     qtable_prop = new AQQueueTableProperty("RAW"); 
    qtable_prop.setCompatible("8.1");

     /* Creating a queue table called aq_table3 in aqjava schema: */
     q_table = aq_sess.createQueueTable ("aqjava", "aq_table3", qtable_prop);
     System.out.println("Successful createQueueTable");  

     /* Creating a new AQQueueProperty object: */
     queue_prop = new AQQueueProperty();

     /* Creating a queue called aq_queue3 in aq_table3: */
     queue = aq_sess.createQueue (q_table, "aq_queue3", queue_prop);
     System.out.println("Successful createQueue");  

     /* Enable enqueue/dequeue on this queue: */
     queue.start();
     System.out.println("Successful start queue");  
   
     /* Grant enqueue_any privilege on this queue to user scott: */
     queue.grantQueuePrivilege("ENQUEUE", "scott"); 
     System.out.println("Successful grantQueuePrivilege");  
}

Creating a Multi-Consumer Queue and Add Subscribers Using Java

public static void runTest(AQSession aq_sess) throws AQException
{
     AQQueueTableProperty    qtable_prop;
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;
     AQAgent                  subs1, subs2;

     /* Creating a AQQueueTable property object (payload type - RAW): */
     qtable_prop = new AQQueueTableProperty("RAW"); 
     System.out.println("Successful setCompatible");  

     /* Set multiconsumer flag to true: */
     qtable_prop.setMultiConsumer(true);

     /* Creating a queue table called aq_table4 in aqjava schema: */
     q_table = aq_sess.createQueueTable ("aqjava", "aq_table4", qtable_prop);
     System.out.println("Successful createQueueTable");  

     /* Creating a new AQQueueProperty object: */
     queue_prop = new AQQueueProperty();
     /* Creating a queue called aq_queue4 in aq_table4 */
     queue = aq_sess.createQueue (q_table, "aq_queue4", queue_prop);
     System.out.println("Successful createQueue");  

     /* Enable enqueue/dequeue on this queue: */
     queue.start();
     System.out.println("Successful start queue");  

     /* Add subscribers to this queue: */
     subs1 = new AQAgent("GREEN", null, 0);
     subs2 = new AQAgent("BLUE", null, 0);

     queue.addSubscriber(subs1, null); /* no rule   */
     System.out.println("Successful addSubscriber 1");  

     queue.addSubscriber(subs2, "priority < 2"); /* with rule */
     System.out.println("Successful addSubscriber 2");  
}