Administrative Interface, 3 of 36

Creating a Queue Table

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

Text description of adq09qad28.gif follows

Text description of the illustration adq09qad28.gif

Text description of adq81045.gif follows

Text description of the illustration adq81045.gif

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 predefined type.

Usage Notes

Queue names and queue table names are converted to upper case. Mixed case (upper and lower case together) is not supported.
The default value of the compatible parameter depends on the database compatibility mode in the init.ora
- If the database is in 8.1 or higher compatible mode, the default value is '8.1'
- If the database is in 8.0 compatible mode, the default value is '8.0'
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 can propagate these object types using AQ propagation with Oracle since release 8.1.x. To enqueue an object type that has an 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. See the Oracle9i 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.
When a queue, queue table, or subscriber is created, modified, or dropped, and if GLOBAL_TOPIC_ENABLED = TRUE, a corresponding LDAP entry is also created.

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): Oracle9i Supplied PL/SQL Packages and Types Reference DBMS_AQADM, CREATE_QUEUE_TABLE
Visual Basic (OO4O): There is no applicable syntax reference for this use case
Java (JDBC): Oracle9i 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): Creating 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 queues containing messages of object type with 
XMLType attributes
CREATE OR REPLACE TYPE order_xml_typ as OBJECT (
   orderno  NUMBER,
   details  XMLTYPE);

execute dbms_aqadm.create_queue_table(
        queue_table     => 'OS_orders_pr_mqtab', 
        comment         => 'Overseas Shipping MultiConsumer Orders queue table', 
        multiple_consumers  => TRUE, 
        queue_payload_type  => 'OS.order_xml_typ',
        compatible          => '8.1');

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');

Create a queue table with freelists or freelist groups

BEGIN                                                                          
dbms_aqadm.create_queue_table ( 
queue_table=> 'AQ_ADMIN.TEST', 
queue_payload_type=> 'RAW', 
storage_clause=> 'STORAGE (FREELISTS 4 FREELIST GROUPS 2)',
compatible => '8.1');
COMMIT;
END;

VB (OO4O): Creating a Queue Table

OOO4O uses database functionality for this operation.

Java (JDBC): Creating 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);  
}