9
Administrative Interface

This chapter describes the administrative interface to Oracle Advanced Queuing. We discuss each operation (such as "Creating a Queue Table") in terms of a use case by that name. Each use case is laid out as follows:

• Use case figure. A figure that depicts the use case.
• Purpose. The purpose of this use case.
• Usage Notes. Guidelines to assist implementation.
• Syntax. The main syntax used to perform this activity.
• Examples. Examples in each programmatic environment which illustrate the use case.

Use Case Model: Administrative Interface -- Basic Operations

Table 9-1, "Use Case Model: Administrative Interface -- Basic Operations" indicates with a + where examples are provided for specific use cases and in which programmatic environment.

The table refers to programmatic environments with the following abbreviations:

• P -- PL/SQL using the DBMS_AQADM and DBMS_AQ packages
• V -- Visual Basic using OO4O (Oracle Objects for OLE)
• J -- Java (native AQ) using JDBC (Java Database Connectivity)
• JMS -- Java (JMS standard) using JDBC (Java Database Connectivity)

  Table 9-1 Use Case Model: Administrative Interface -- Basic Operations

  Use Case	P	V	J	JMS
  Creating a Queue Table	+	+	+	-
  Creating a Queue Table [Set Storage Clause]	+	-	+	-
  Altering a Queue Table	+	-	+	-
  Dropping a Queue Table	+	-	+	-
  Creating a Queue	+	-	+	-
  Creating a Nonpersistent Queue	+	-	-	-
  Altering a Queue	+	-	+	-
  Dropping a Queue	+	-	+	-
  Creating a Transformation	+	-	-	-
  Modifying a Transformation	+	-	-	-
  Applying a Transformation	+	-	-	-
  Dropping a Transformation	+	-	-	-
  Starting a Queue	+	-	+	-
  Stopping a Queue	+	-	+	-
  Granting System Privilege	+	-	+	-
  Revoking System Privilege	+	-	-	-
  Granting Queue Privilege	+	-	+	-
  Revoking Queue Privilege	+	-	+	-
  Adding a Subscriber	+	-	+	-
  Altering a Subscriber	+	-	+	-
  Removing a Subscriber	+	-	+	-
  Scheduling a Queue Propagation	+	-	+	-
  Unscheduling a Queue Propagation	+	-	+	-
  Verifying a Queue Type	+	-	-	-
  Altering a Propagation Schedule	+	-	+	-
  Enabling a Propagation Schedule	+	-	+	-
  Disabling a Propagation Schedule	+	-	+	-
  Creating an AQ Agent	+	-	-	-
  Altering an AQ Agent	+	-	-	-
  Dropping an AQ Agent	+	-	-	-
  Enabling Database Access	+	-	-	-
  Disabling Database Access	+	-	-	-
  Adding an Alias to the LDAP Server	+	-	-	-
  Removing an Alias from the LDAP Server	+	-	-	-

Creating a Queue Table

Figure 9-1 Creating a Queue Table

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 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
• VB (OO4O): Creating a Queue Table
• Java (JDBC): Creating a Queue Table

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

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

Creating a queue table for queues containing messages of RAW type

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

Creating a queue table for queues containing messages of 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  => 'SYS.XMLType',
        compatible          => '8.1');

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

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

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

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

Creating 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

Examples depicting how to create a queue table using Java follow.

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

Creating a 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");  
}

Creating a 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"); 
}

Creating a 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);  
}

Creating a Queue Table [Set Storage Clause]

Figure 9-2 Creating a Queue Table [Set Storage Clause]

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

Altering a Queue Table

Figure 9-3 Altering a Queue Table

Purpose

Alter the existing properties of a queue table.

Usage Notes

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.ALTER_QUEUE_TABLE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ,alterQueue

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments.

• PL/SQL (DBMS_AQADM Package): Altering a Queue Table
• VB (OO4O): Example not provided.
• Java (JDBC): Altering a Queue Table

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

/* Altering the table to change the primary, secondary instances for queue owner    
(only applies to Real Application Clusters environments). The primary instance 
is the instance number of the primary owner of the queue table. The secondary 
instance is the instance number of the secondary owner of the queue table. */
 EXECUTE dbms_aqadm.alter_queue_table ( 
   Queue_table          => 'aq.ObjMsgs_qtab', 
   Primary_instance     => 3, 
   Secondary_instance   => 2); 
 
/* Altering the table to change the comment for a queue table: */ 
EXECUTE dbms_aqadm.alter_queue_table ( 
   Queue_table          => 'aq.ObjMsgs_qtab', 
   Comment              => 'revised usage for queue table'); 

/* Altering the table to change the comment for a queue table and use 
nonrepudiation: */ 
EXECUTE dbms_aqadm.alter_queue_table ( 
   Queue_table          => 'aq.ObjMsgs_qtab', 
   Comment              => 'revised usage for queue table', 

Java (JDBC): Altering a Queue Table

/* Alter a queue table */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueueTableProperty    qtable_prop;
     AQQueueTable            q_table;
     
     q_table = aq_sess.getQueueTable ("aq", "ObjMsgs_qtab");

     /* Get queue table properties: */
     qtable_prop = q_table.getProperty();

     /* Alter the queue table comment and instance affinity */
     q_table.alter("altered queue table", 3, 2);
} 

Dropping a Queue Table

Figure 9-4 Dropping a Queue Table

Purpose

Drop an existing queue table. Note that you must stop and drop all the queues in a queue tables before the queue table can be dropped. You must do this explicitly unless the force option is used in which case this done automatically.

Usage Notes

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 or dropped.

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.DROP_QUEUE_TABLE procedure.
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ AQQueueTable.drop

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments.

• PL/SQL (DBMS_AQADM Package): Dropping a Queue Table
• VB (OO4O): Example not provided.
• Java (JDBC): Dropping a Queue Table

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

/* Drop the queue table (for which all queues have been previously dropped by
 the user) */
EXECUTE dbms_aqadm.drop_queue_table ( 
   queue_table        => 'aq.Objmsgs_qtab');

/* Drop the queue table and force all queues to be stopped and dropped by the
 system */ 
EXECUTE dbms_aqadm.drop_queue_table ( 
   queue_table        => 'aq.Objmsgs_qtab', 
   force              => TRUE); 

Java (JDBC): Dropping a Queue Table

/* Drop a queue table - for which all queues have already been dropped by 
   the user */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueueTable            q_table;
     
     q_table = aq_sess.getQueueTable ("aq", "ObjMsgs_qtab");
     
     /* Drop the queue table*/
     q_table.drop(false);
     System.out.println("Successful drop"); 
} 


/* Drop the queue table (and force all queues to be stopped and dropped by 
   the user */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueueTable            q_table;
     
     q_table = aq_sess.getQueueTable ("aq", "ObjMsgs_qtab");
     
     /* Drop the queue table (and automatically drop all queues inside it */
     q_table.drop(true);
     System.out.println("Successful drop"); 
} 

Creating a Queue

Figure 9-5 Creating a Queue

Purpose

Create a queue in the specified queue table.

Usage Notes

• Queue names and queue table names are converted to upper case. Mixed case (upper and lower case together) is not supported.
• All queue names must be unique within a schema. Once a queue is created with CREATE_QUEUE, it can be enabled by calling START_QUEUE. By default, the queue is created with both enqueue and dequeue disabled.
• To view retained messages, you can either dequeue by message ID or use SQL.
• When a queue, queue table, or subscriber is created 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 procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ, CreateQueue

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments.

• PL/SQL (DBMS_AQADM Package): Dropping a Queue Table
• VB (OO4O): Example not provided.
• Java (JDBC): Dropping a Queue Table

PL/SQL (DBMS_AQADM): Creating a Queue

Creating a queue within a queue table for messages of object type

/* Create a message type: */
CREATE type aq.Message_typ as object (
   Subject     VARCHAR2(30),
   Text        VARCHAR2(80));   

/* Create a object type queue table and queue: */
EXECUTE dbms_aqadm.create_queue_table (
   Queue_table        => 'aq.ObjMsgs_qtab',
   Queue_payload_type => 'aq.Message_typ');

EXECUTE dbms_aqadm.create_queue (
   Queue_name         =>  'msg_queue',
   Queue_table        => 'aq.ObjMsgs_qtab');

Creating a queue within a queue table for messages of RAW type

/* Create a RAW type queue table and queue: */
EXECUTE dbms_aqadm.create_queue_table ( 
   Queue_table          => 'aq.RawMsgs_qtab', 
   Queue_payload_type   => 'RAW'); 
  
/* Create queue: */
EXECUTE dbms_aqadm.create_queue ( 
   Queue_name          => 'raw_msg_queue', 
   Queue_table         => 'aq.RawMsgs_qtab'); 
  Create a prioritized message queue table and queue

/* Create a queue table for priortized messages: */
EXECUTE dbms_aqadm.create_queue_table (
   Queue_table        => 'aq.PriorityMsgs_qtab', 
   Sort_list          => 'PRIORITY,ENQ_TIME', 
   Queue_payload_type => 'aq.Message_typ');
/* Create queue: */
EXECUTE dbms_aqadm.create_queue (
   Queue_name         => 'priority_msg_queue', 
   Queue_table        => 'aq.PriorityMsgs_qtab');

Creating a queue table and queue for multiple consumers

/* Create a queue table for multi-consumers: */
EXECUTE dbms_aqadm.create_queue_table (
   queue_table        => 'aq.MultiConsumerMsgs_qtab',
   Multiple_consumers => TRUE, 
   Queue_payload_type => 'aq.Message_typ');

/* Create queue: */
EXECUTE dbms_aqadm.create_queue (
   Queue_name         => 'MultiConsumerMsg_queue',
   Queue_table        => 'aq.MultiConsumerMsgs_qtab');

Creating a queue table and queue to demonstrate propagation

/* Create queue: */
EXECUTE dbms_aqadm.create_queue (
   Queue_name        => 'AnotherMsg_queue',
   queue_table       => 'aq.MultiConsumerMsgs_qtab');

Creating a queue table and queue for multiple consumers compatible with 8.1

/* Create a queue table for multi-consumers compatible with Release 8.1: */
EXECUTE dbms_aqadm.create_queue_table ( 
   Queue_table        => 'aq.MultiConsumerMsgs81_qtab', 
   Multiple_consumers => TRUE, 
   Compatible         => '8.1', 
   Queue_payload_type => 'aq.Message_typ'); 
 
EXECUTE dbms_aqadm.create_queue ( 
   Queue_name         => 'MultiConsumerMsg81_queue', 
   Queue_table        => 'aq.MultiConsumerMsgs81_qtab'); 

Java (JDBC): Creating a Queue

Creating a queue within a queue table for messages of object type

public static void example(AQSession aq_sess) throws AQException
{
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;

     q_table = aq_sess.getQueueTable ("aq", "ObjMsgs_qtab");

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

     queue = aq_sess.createQueue (q_table, "msg_queue", queue_prop);
     System.out.println("Successful createQueue");  

}

Creating a queue within a queue table for messages of raw type

public static void example(AQSession aq_sess) throws AQException
{
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;

     q_table = aq_sess.getQueueTable ("aq", "RawMsgs_qtab");

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

     queue = aq_sess.createQueue (q_table, "msg_queue", queue_prop);
     System.out.println("Successful createQueue");  

}

Creating a multiconsumer queue with prioritized messages

public static void example(AQSession aq_sess) throws AQException
{
     AQQueueTableProperty    qtable_prop;
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;
     AQAgent                 agent;

     qtable_prop = new AQQueueTableProperty("RAW"); 
     qtable_prop.setMultiConsumer(true);

     qtable_prop.setSortOrder("priority,enq_time");
     q_table = aq_sess.createQueueTable ("aq", "PriorityMsgs_qtab", 
                     qtable_prop);

     queue_prop = new AQQueueProperty();
     queue = aq_sess.createQueue (q_table, "priority_msg_queue", queue_prop);

}

Creating a Nonpersistent Queue

Figure 9-6 Creating a Nonpersistent Queue

Purpose

Create a nonpersistent queue.

Usage Notes

The queue may be either single-consumer or multiconsumer queue. All queue names must be unique within a schema. The queues are created in a 8.1 compatible system-created queue table (AQ$_MEM_SC or AQ$_MEM_MC) in the same schema as that specified by the queue name. If the queue name does not specify a schema name, the queue is created in the login user's schema. Once a queue is created with CREATE_NP_QUEUE, it can be enabled by calling START_QUEUE. By default, the queue is created with both enqueue and dequeue disabled.

You can enqueue RAW and Object Type (ADT) messages into a nonpersistent queue. You cannot dequeue from a nonpersistent queue. The only way to retrieve a message from a nonpersistent queue is by using the OCI notification mechanism (see Registering for Notification).

You cannot invoke the listen call on a nonpersistent queue (see "Listening to One or More Single-Consumer Queues").

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_NP_QUEUE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): There is no applicable syntax reference for this use case

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments.

• PL/SQL (DBMS_AQADM Package): Dropping a Queue Table
• VB (OO4O): Example not provided.
• Java (JDBC): Dropping a Queue Table

PL/SQL (DBMS_AQADM): Creating a Nonpersistent Queue

/* Create a nonpersistent single-consumer queue (Note: this is not preceded by
 creation of a queue table) */ 
EXECUTE dbms_aqadm.create_np_queue( 
   Queue_name           => 'Singleconsumersmsg_npque',  
   Multiple_consumers   => FALSE);  
 
/* Create a nonpersistent multi-consumer queue (Note: this is not preceded by
 creation of a queue table) */ 
EXECUTE dbms_aqadm.create_np_queue( 
   Queue_name           => 'Multiconsumersmsg_npque',  
   Multiple_consumers   => TRUE); 

Java (JDBC): Creating a Nonpersistent Queue

Feature not available through Java API.

Altering a Queue

Figure 9-7 Altering a Queue

Purpose

Alter existing properties of a queue. Only max_retries, comment, retry_delay, and retention_time can be altered.

Usage Notes

To view retained messages, you can either dequeue by message ID or use SQL.

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.ALTER_QUEUE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ, alter

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Altering a Queue
• VB (OO4O): Example not provided.
• Java (JDBC): Altering a Queue

PL/SQL (DBMS_AQADM): Altering a Queue

/* Alter queue to change retention time, saving messages for 1 day after
 dequeueing:  */
EXECUTE dbms_aqadm.alter_queue ( 
   queue_name        => 'aq.Anothermsg_queue', 
   retention_time    => 86400);

Java (JDBC): Altering a Queue

/* Alter a queue to change retention time, saving messages for 1 day
   after dequeuing */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueueProperty         queue_prop;
     AQQueue                 queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("AQ", "Anothermsg_queue");

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

     /* Change retention time to 1 day */
     queue_prop.setRetentionTime(new Double(86400));

     /* Alter the queue */
     queue.alterQueue(queue_prop);

}

Dropping a Queue

Figure 9-8 Dropping a Queue

Purpose

Drops an existing queue. DROP_QUEUE is not allowed unless STOP_QUEUE has been called to disable the queue for both enqueuing and dequeuing. All the queue data is deleted as part of the drop operation.

Usage Notes

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.DROP_QUEUE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ, dropQueue

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Dropping a Queue
• VB (OO4O): Example not provided.
• Java (JDBC): Dropping a Queue

PL/SQL (DBMS_AQADM): Dropping a Queue

Dropping a Standard Queue

/* Stop the queue preparatory to dropping it (a queue may be dropped only after
 it has been succesfully stopped for enqueing and dequeing): */ 
EXECUTE dbms_aqadm.stop_queue (  
   Queue_name        => 'aq.Msg_queue');   
 
/* Drop queue: */  
EXECUTE dbms_aqadm.drop_queue (  
   Queue_name         => 'aq.Msg_queue');   

Dropping a Nonpersistent Queue

EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => 'Nonpersistent_singleconsumerq1');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => 'Nonpersistent_multiconsumerq1');

Java (JDBC): Dropping a Queue

/* Drop a queue */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue                queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("AQ", "Msg_queue");

     /* Stop the queue first */
     queue.stop(true);

     /* Drop the queue */
     queue.drop();
}

Creating a Transformation

Figure 9-9 Creating a Transformation

Purpose

Creates a message format transformation. The transformation must be a SQL function with input type from_type, returning an object of type to_type. It can also be a SQL expression of type to_type, referring to from_type. All references to from_type must be of the form source.user_data.

Usage Notes

To use this feature, you must be granted execute privileges on dbms_transform. You must also have execute privileges on the user-defined types that are the source and destination types of the transformation, and have execute privileges on any PL/SQL function being used in the transformation function. The transformation cannot write the database state (that is, perform DML) or commit or rollback the current transaction.

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_TRANSFORM.CREATE_TRANSFORMATION procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Creating a Transformation
• VB (OO4O): Example not provided.
• Java (JDBC): none

PL/SQL (DBMS_AQADM): Creating a Transformation

dbms_transform.create_transformation(schema => 'scott',
    name           => 'test_transf', from_schema => 'scott',
    from_type      => 'type1', to_schema => 'scott',
    to_type        => 'type2',
    transformation => 'scott.trans_func(source.user_data)');

Or you can do the following:

dbms_transform.create_transformation(schema => 'scott',
    name           => 'test_transf',
    from_schema    => 'scott', 
    from_type      => 'type1,
    to_schema      => 'scott',
    to_type        => 'type2',
    transformation => 'scott.type2(source.user_data.attr2, 
        source.user_data.attr1)');

Java (JDBC)

No example is provided with this release.

Modifying a Transformation

Figure 9-10 Modifying a Transformation

Purpose

This feature is used to change the transformation function and to specify transformations for each attribute of the target type. If the attribute number 0 is specified, then the transformation expression singularly defines the transformation from the source to target types. All references to from_type must be of the form source.user_data. All references to the attributes of the source type must be prefixed by source.user_data.

Usage Notes

To use this feature, you must be granted execute privileges on dbms_transform. You must also have execute privileges on the user-defined types that are the source and destination types of the transformation, and have execute privileges on any PL/SQL function being used in the transformation function.

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_TRANSFORM.MODIFY procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference

Applying a Transformation

Figure 9-11 Applying a Transformation

Dropping a Transformation

Figure 9-12 Dropping a Transformation

Purpose

To drop a transformation.

Usage Notes

To use this feature, you must be granted execute privileges on dbms_transform. You must also have execute privileges on the user-defined types that are the source and destination types of the transformation, and have execute privileges on any PL/SQL function being used in the transformation function.

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_TRANSFORM.DROP procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference

Starting a Queue

Figure 9-13 Starting a Queue

Purpose

Enables the specified queue for enqueuing or dequeueing.

Usage Notes

After creating a queue the administrator must use START_QUEUE to enable the queue. The default is to enable it for both ENQUEUE and DEQUEUE. Only dequeue operations are allowed on an exception queue. This operation takes effect when the call completes and does not have any transactional characteristics.

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.START_QUEUE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ, AQQueueAdmin.start

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM Package): Starting a Queue
• VB (OO4O): Example not provided.
• Java (JDBC): Starting a Queue

PL/SQL (DBMS_AQADM Package): Starting a Queue

/* Start a queue and enable both enqueue and dequeue: */
EXECUTE dbms_aqadm.start_queue (
   queue_name         => 'Msg_queue');

/* Start a previously stopped queue for dequeue only */ 
EXECUTE dbms_aqadm.start_queue ( 
   queue_name         => 'aq.msg_queue', 
   dequeue            => TRUE, 
   enqueue            => FALSE); 

Java (JDBC): Starting a Queue

/* Start a queue - enable both enqueue and dequeue */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue                 queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("AQ", "Msg_queue");

     /* Enable enqueue and dequeue */
     queue.start();
}


/* Start a previously stopped queue for dequeue only */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue                 queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("AQ", "Msg_queue");

     /* Enable enqueue and dequeue */
     queue.start(false, true);
}

Stopping a Queue

Figure 9-14 Stopping a Queue

Purpose

Disables enqueuing or dequeuing on the specified queue.

Usage Notes

By default, this call disables both ENQUEUEs or DEQUEUEs. A queue cannot be stopped if there are outstanding transactions against the queue. This operation takes effect when the call completes and does not have any transactional characteristics.

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.STOP_QUEUE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ) AQQueueAdmin.stop

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Stopping a Queue
• VB (OO4O): Example not provided.
• Java (JDBC): Stopping a Queue

PL/SQL (DBMS_AQADM): Stopping a Queue

/* Stop the queue: */ 
EXECUTE dbms_aqadm.stop_queue (  
   queue_name        => 'aq.Msg_queue');   

Java (JDBC): Stopping a Queue

/* Stop a queue - wait for oustanding transactions */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue                 queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("AQ", "Msg_queue");

     /* Enable enqueue and dequeue */
     queue.stop(true);
}

Granting System Privilege

Figure 9-15 Granting System Privilege

Purpose

To grant AQ system privileges to users and roles. The privileges are ENQUEUE_ANY, DEQUEUE_ANY, MANAGE_ANY. Initially, only SYS and SYSTEM can use this procedure successfully.

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.GRANT_SYSTEM_PRIVILEGE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): There is no applicable syntax reference for this use case

Usage Notes

Not applicable.

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Granting System Privilege
• VB (OO4O): Example not provided.
• Java (JDBC): Granting System Privilege

PL/SQL (DBMS_AQADM): Granting System Privilege

/* User AQADM grants the rights to enqueue and dequeue to ANY queues: */  

CONNECT system/manager;
CREATE USER aqadm IDENTIFIED BY aqadm;
GRANT CONNECT, RESOURCE TO aqadm; 
GRANT EXECUTE ON DBMS_AQADM TO aqadm;
GRANT Aq_administrator_role TO aqadm;

CONNECT aqadm/aqadm; 
EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege          =>    'ENQUEUE_ANY', 
   grantee            =>    'Jones', 
   admin_option       =>     FALSE);
EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege          =>     'DEQUEUE_ANY', 
   grantee            =>     'Jones', 
   admin_option       =>      FALSE);

Java (JDBC): Granting System Privilege

Feature not available through Java API

Revoking System Privilege

Figure 9-16 Revoking System Privilege

Purpose

To revoke AQ system privileges from users and roles. The privileges are ENQUEUE_ANY, DEQUEUE_ANY and MANAGE_ANY. The ADMIN option for a system privilege cannot be selectively revoked.

Usage Notes

Not applicable.

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.REVOKE_SYSTEM_PRIVILEGE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): There is no applicable syntax reference for this use case

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• Using PL/SQL (DBMS_AQADM): Revoking System Privilege
• VB (OO4O): Example not provided.
• Java (JDBC): Example not provided.

Using PL/SQL (DBMS_AQADM): Revoking System Privilege

/* To revoke the DEQUEUE_ANY system privilege from Jones. */
CONNECT system/manager;
   execute DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE(privilege=>'DEQUEUE_ANY', 
                             grantee=>'Jones');

Granting Queue Privilege

Figure 9-17 Granting Queue Privilege

Purpose

To grant privileges on a queue to users and roles. The privileges are ENQUEUE or DEQUEUE. Initially, only the queue table owner can use this procedure to grant privileges on the queues.

Usage Notes

Not applicable.

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.GRANT_QUEUE_PRIVILEGE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ, grantQueuePrivilege

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Granting Queue Privilege
• VB (OO4O): Example not provided.
• Java (JDBC): Granting Queue Privilege

PL/SQL (DBMS_AQADM): Granting Queue Privilege

/* User grants the access right for both enqueue and dequeue rights using
 DBMS_AQADM.GRANT. */ 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege     =>     'ALL', 
   queue_name    =>     'aq.multiconsumermsg81_queue',
   grantee       =>     'Jones', 
   grant_option  =>      TRUE);   

Java (JDBC): Granting Queue Privilege

/* Grant enqueue and dequeue privileges on queue to user 'Jones' */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue                 queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("AQ", "multiconsumermsg81_queue");
     /* Enable enqueue and dequeue */
     queue.grantQueuePrivilege("ALL", "Jones", true);
}

Revoking Queue Privilege

Figure 9-18 Revoking Queue Privilege

Purpose

To revoke privileges on a queue from users and roles. The privileges are ENQUEUE or DEQUEUE.

Usage Notes

To revoke a privilege, the revoker must be the original grantor of the privilege. The privileges propagated through the GRANT option are revoked if the grantor's privileges are revoked.

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.REVOKE_QUEUE_PRIVILEGE procedure
• Visual Basic (OO4O): There is no applicable syntax reference for this use case
• Java (JDBC): Oracle9i Supplied Java Packages Reference oracle.AQ, revokeQueuePrivledge

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Revoking Queue Privilege
• VB (OO4O): Example not provided.
• Java (JDBC): Revoking Queue Privilege

PL/SQL (DBMS_AQADM): Revoking Queue Privilege

/* User can revoke the dequeue right of a grantee on a specific queue 
 leaving the grantee with only the enqueue right: */ 
CONNECT scott/tiger; 
EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE(
   privilege     =>     'DEQUEUE', 
   queue_name    =>     'scott.ScottMsgs_queue', 
   grantee       =>     'Jones'); 

Java (JDBC): Revoking Queue Privilege

/* User can revoke the dequeue right of a grantee on a specific 
   queue, leaving only the enqueue right */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue                 queue;

     /* Get the queue object */
     queue = aq_sess.getQueue("SCOTT", "ScottMsgs_queue");

     /* Enable enqueue and dequeue */
     queue.revokeQueuePrivilege("DEQUEUE", "Jones");
}

Adding a Subscriber

Figure 9-19 Adding a Subscribe

Purpose

Adds a default subscriber to a queue.

Usage Note

• A program can enqueue messages to a specific list of recipients or to the default list of subscribers. This operation will only succeed on queues that allow multiple consumers. This operation takes effect immediately and the containing transaction is committed. Enqueue requests that are executed after the completion of this call will reflect the new behavior.
• Note that any string within the rule has to be quoted as follows:

  rule   => 'PRIORITY <= 3 AND CORRID =  ''FROM JAPAN'''
  

  Note that these are all single quotation marks.

• When a queue, queue table, or subscriber is created and if GLOBAL_TOPIC_ENABLED = TRUE, a corresponding LDAP entry is also created.
• Specify the name of the transformation to be applied during dequeue or propagation. The transforma