Administrative Interface, 3 of 25

Create a Queue Table

Figure 9-2 Use Case Diagram: Create a Queue Table

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose

Create a queue table for messages of a pre-defined type.

Usage Notes

The sort keys for dequeue ordering, if any, need to be defined at table creation time. The following objects are created at this time:
- The default exception queue associated with the queue table called aq$_<queue_table_name>_e.
- A read-only view which is used by AQ applications for querying queue data called aq$<queue_table_name>.
- An index for the queue monitor operations called aq$_<queue_table_name>_t.
- An index or an index organized table (IOT) in the case of multiple consumer queues for dequeue operations called aq$_<queue_table_name>_i.
For 8.1 compatible multiconsumer queue tables the following additional objects are created:
- A table called aq$_<queue_table_name>_s. This table stores information about the subscribers.
- A table called aq$_<queue_table_name>_r. This table stores information about rules on subscriptions.
- An index organized table (IOT) called aq$_<queue_table_name>_h.This table stores the dequeue history data.
CLOB, BLOB or BFILE objects are valid in an AQ message. You cant propagate these object types using AQ propagation with the Oracle8i release 8.1.x. In order to enqueue an object type that has a LOB, you must first set the LOB_attribute to EMPTY_BLOB() and perform the enqueue. You can then select the LOB locator that was generated from the queue table's view and use the standard LOB operations. Please see the Oracle8i Application Developer's Guide - Large Objects (LOBs) for more information.
You can specify and modify the primary_instance and secondary_instance only in 8.1 compatible mode.
You cannot specify a secondary instance unless there is a primary instance.

Syntax

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

PL/SQL (DBMS_AQADM Package): Oracle8i Supplied PL/SQL Packages Reference DBMS_AQADM, CREATE_QUEUE_TABLE
Visual Basic (OO4O): There is no applicable syntax reference for this use case
Java (JDBC): Oracle8i Supplied Java Packages Reference oracle.AQ createQueueTable

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. :

PL/SQL (DBMS_AQADM Package): Create a Queue Table

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;

Create queue table for queues containing messages of object type

CREATE type aq.Message_typ as object (
   Subject                VARCHAR2(30),
   Text                   VARCHAR2(80));   

/* Note: if you do not stipulate a schema, you default to the user's schema. */
EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.ObjMsgs_qtab',
   Queue_payload_type     => 'aq.Message_typ');

Create queue table for queues containing messages of RAW type

EXECUTE dbms_aqadm.create_queue_table ( 
   Queue_table            => 'aq.RawMsgs_qtab', 
   Queue_payload_type     => 'RAW');

Create a queue table for prioritized messages

EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.PriorityMsgs_qtab', 
   Sort_list              => 'PRIORITY,ENQ_TIME', 
   Queue_payload_type     => 'aq.Message_typ');

Create a queue table for multiple consumers

EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.MultiConsumerMsgs_qtab',
   Multiple_consumers     => TRUE, 
   Queue_payload_type     => 'aq.Message_typ');

Create a queue table for multiple consumers compatible with 8.1

EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.Multiconsumermsgs8_1qtab',
   Multiple_consumers     =>  TRUE,   
   Compatible             => '8.1', 
   Queue_payload_type     => 'aq.Message_typ');

Create a queue table in a specified tablespace

EXECUTE dbms_aqadm.create_queue_table( 
        queue_table        => 'aq.aq_tbsMsg_qtab',  
        queue_payload_type => 'aq.Message_typ',  
        storage_clause     => 'tablespace aq_tbs');

VB (OO4O): Create a Queue Table

OOO4O uses database functionality for this operation.

Java (JDBC): Create a Queue Table

Three examples follow of how to create a queue table using Java.

Note:
You may need to set up the following data structures for certain examples to work:
CONNECT system/manager; DROP USER aqadm CASCADE; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT CONNECT, RESOURCE TO 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; CREATE type aq.Message_typ as object ( Subject VARCHAR2(30), Text VARCHAR2(80));

Create queue table for queues containing messages of object type

public static void example(AQSession aq_sess) throws AQException
{
    AQQueueTableProperty     qtable_prop;
    AQQueueProperty          queue_prop;
    AQQueueTable             q_table;
    AQQueue                  queue;
   
    /* Create a AQQueueTableProperty object (payload type Message_typ): */
    qtable_prop = new AQQueueTableProperty("AQ.MESSAGE_TYP"); 
   
    /* Create a queue table in aq schema */
    q_table = aq_sess.createQueueTable ("aq", "ObjMsgs_qtab", qtable_prop);

    System.out.println("Successfully created ObjMsgs_qtab in aq schema");  
}

Create queue table for queues containing messages of RAW type

public static void example(AQSession aq_sess) throws AQException
{
    AQQueueTableProperty     qtable_prop;
    AQQueueProperty          queue_prop;
    AQQueueTable             q_table;
    AQQueue                  queue;
   
    /* Create a AQQueueTableProperty object (payload type RAW): */
    qtable_prop = new AQQueueTableProperty("RAW"); 
   
    /* Create a queue table in aq schema */
    q_table = aq_sess.createQueueTable ("aq", "RawMsgs_qtab", qtable_prop);

    System.out.println("Successfully created RawMsgs_qtab in aq schema");  
}


3. Create a queue table for multiple consumers and prioritized messages

public static void example(AQSession aq_sess) throws AQException
{
    AQQueueTableProperty     qtable_prop;
    AQQueueProperty          queue_prop;
    AQQueueTable             q_table;
    AQQueue                  queue;
   
    qtable_prop = new AQQueueTableProperty("RAW"); 

    /* Enable multiple consumers */
    qtable_prop.setMultiConsumer(true);
    qtable_prop.setCompatible("8.1");	
       
    /* Specify sort order as priority,enqueue_time */
    qtable_prop.setSortOrder("PRIORITY,ENQ_TIME");

    /* Create a queue table in aq schema */
    q_table = aq_sess.createQueueTable ("aq", "PriorityMsgs_qtab", 
					 qtable_prop);

    System.out.println("Successfully created PriorityMsgs_qtab in aq schema"); 
}

Create queue table in specified tablespace

public static void example(AQSession aq_sess) throws AQException
{
    AQQueueTableProperty     qtable_prop;
    AQQueueProperty          queue_prop;
    AQQueueTable             q_table;
    AQQueue                  queue;
   
    /* Create a AQQueueTableProperty object (payload type Message_typ): */
    qtable_prop = new AQQueueTableProperty("AQ.MESSAGE_TYP");

    /* Specify tablespace for queue table */ 
    qtable_prop.setStorageClause("tablespace aq_tbs");   

    /* Create a queue table in aq schema */
    q_table = aq_sess.createQueueTable ("aq", "aq_tbsMsg_qtab", qtable_prop);  
}