Skip Headers

Oracle9i Application Developer's Guide - Advanced Queuing
Release 2 (9.2)
Part Number A96587-01
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback

Go to previous page Go to next page

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 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:

Creating a Queue Table

Figure 9-1 Creating a Queue Table

Text description of adque026.gif follows
Text description of the illustration adque026.gif

Text description of adque045.gif follows
Text description of the illustration adque045.gif

See Also:

Purpose

Create a queue table for messages of a predefined type.

Usage Notes

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:

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;

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.

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

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]

Text description of adque046.gif follows
Text description of the illustration adque046.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

Altering a Queue Table

Figure 9-3 Altering a Queue Table

Text description of adque047.gif follows
Text description of the illustration adque047.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque048.gif follows
Text description of the illustration adque048.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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');
Caution:

You may need to set up or drop data structures for certain examples to work.

 
/* 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

Text description of adque049.gif follows
Text description of the illustration adque049.gif

Text description of adque050.gif follows
Text description of the illustration adque050.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Create a queue in the specified queue table.

Usage Notes

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:

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 Queue
Caution:

You may need to set up or drop data structures for certain examples to work.

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

Text description of adque051.gif follows
Text description of the illustration adque051.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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

Text description of adque052.gif follows
Text description of the illustration adque052.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque053.gif follows
Text description of the illustration adque053.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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

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

Text description of adque434.gif follows
Text description of the illustration adque434.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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

Text description of adque435.gif follows
Text description of the illustration adque435.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

Applying a Transformation

Figure 9-11 Applying a Transformation

Text description of adque433.gif follows
Text description of the illustration adque433.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Dropping a Transformation

Figure 9-12 Dropping a Transformation

Text description of adque436.gif follows
Text description of the illustration adque436.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

Starting a Queue

Figure 9-13 Starting a Queue

Text description of adque054.gif follows
Text description of the illustration adque054.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque055.gif follows
Text description of the illustration adque055.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque056.gif follows
Text description of the illustration adque056.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* User AQADM grants the rights to enqueue and dequeue to ANY queues: */
Note:

You may need to set up the following data structures for certain examples to work:

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

Text description of adque057.gif follows
Text description of the illustration adque057.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque058.gif follows
Text description of the illustration adque058.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque059.gif follows
Text description of the illustration adque059.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

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:

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
/* 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

Text description of adque060.gif follows
Text description of the illustration adque060.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Adds a default subscriber to a queue.

Usage Note

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:

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): Adding Subscriber
/* Anonymous PL/SQL block for adding a subscriber at a designated queue in a 
designated schema at a database link: */ 
DECLARE 
   subscriber          sys.aq$_agent; 
BEGIN 
   subscriber := sys.aq$_agent ('subscriber1', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
     queue_name         => 'aq.multi_queue', 
      subscriber            =>  subscriber); 
 END; 

/* Add a subscriber with a rule: */ 
DECLARE 
   subscriber       sys.aq$_agent; 
BEGIN 
   subscriber := sys.aq$_agent('subscriber2', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
     queue_name         =>  'aq.multi_queue', 
      subscriber            =>    subscriber, 
      rule                  =>    'priority < 2'); 
END;

Add a Subscriber and Specify a Transformation

/* Add a subscriber with a rule and specify a transformation */ 
DECLARE 
   subscriber       sys.aq$_agent; 
BEGIN 
   subscriber := sys.aq$_agent('subscriber2', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
     queue_name         =>  'aq.multi_queue', 
      subscriber            =>    subscriber, 
      transformation        => 'AQ.msg_map');
/* Where the transformation was created as */ 
EXECUTE DBMS_TRANSFORM.CREATE_TRANSFORMATION
 ( schema => 'AQ',
   name => 'msg_map',
   from_schema => 'AQ',
   from_type => 'purchase_order1',
   to_schema => 'AQ',
   to_type => 'purchase_order2',
   transformation => 'AQ.transform_PO(source.user_data)');
END;
PL/SQL (DBMS_AQADM): Adding a Rule-Based Subscriber
DECLARE 
   subscriber          sys.aq$_agent; 
BEGIN 
   subscriber :=  sys.aq$_agent('East_Shipping','ES.ES_bookedorders_que',null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
      queue_name           => 'OE.OE_bookedorders_que', 
      subscriber           => subscriber, 
      rule                 => 'tab.user_data.orderregion =  ''EASTERN'' OR 
                              (tab.user_data.ordertype =  ''RUSH'' AND 
                               tab.user_data.customer.country = ''USA'') '); 
END; 

/* Add a rule-based subscriber for Overseas Shipping */
DECLARE 
  subscriber     aq$_agent; 
BEGIN 
  subscriber := aq$_agent('Overseas_DHL', null, null); 

  dbms_aqadm.add_subscriber( 
        queue_name     => 'OS.OS_bookedorders_que', 
        subscriber     => subscriber, 
        rule           => 'tab.user_data.xdata.extract(''/ORDER_
TYP/ORDERTYPE/text()'').getStringVal()=''RUSH''');

END;
Java (JDBC): Adding a Subscriber
/* Setup */
public static void setup(AQSession aq_sess) throws AQException
{
     AQQueueTableProperty    qtable_prop;
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;

     /* Create a AQQueueTable property object */
     qtable_prop = new AQQueueTableProperty("AQ.MESSAGE_TYP"); 
     qtable_prop.setMultiConsumer(true);

     q_table = aq_sess.createQueueTable ("aq", "multi_qtab", qtable_prop);

     /* Create a new AQQueueProperty object: */
     queue_prop = new AQQueueProperty();
     queue = aq_sess.createQueue (q_table, "multi_queue", queue_prop);

}

/* Add subscribers to a queue */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     /* add a subscriber */
     agent1 = new AQAgent("subscriber1", "aq2.msg_queue2@london");
     queue.addSubscriber(agent1, null);

     /* add a subscriber with a rule */
     agent2 = new AQAgent("subscriber2", "aq2.msg_queue2@london");

     queue.addSubscriber(agent2, "priority < 2");
}

/* Add a subscriber with a rule */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        

     /* Get the queue object */
     queue = aq_sess.getQueue("OE", "OE_bookedorders_que");

     /* add a subscriber */
     agent1 = new AQAgent("East_Shipping", "ES.ES_bookedorders_que");

     queue.addSubscriber(agent1, 
             "tab.user_data.orderregion='EASTERN' OR " +
             "(tab.user_data.ordertype='RUSH' AND " +
             "tab.user_data.customer.country='USA')");
}

Altering a Subscriber

Figure 9-20 Altering a Subscriber

Text description of adque061.gif follows
Text description of the illustration adque061.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Alter existing properties of a subscriber to a specified queue. Only the rule can be altered.

Usage Notes

The rule, the transformation, or both can be altered. If you only alter one of the attributes, the rule, or the transformation of the subscriber, specify the existing value of the other attribute to the alter call.

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:

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 Subscriber
Note:

You may need to set up the following data structures for certain examples to work:

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
   queue_table            => 'aq.multi_qtab',
   multiple_consumers     => TRUE,   
   queue_payload_type     => 'aq.message_typ',
   compatible             => '8.1.5');
EXECUTE DBMS_AQADM.CREATE_QUEUE (
   queue_name             =>  'multi_queue',
   queue_table            => 'aq.multi_qtab');


/* Add a subscriber with a rule: */ 
DECLARE 
   subscriber       sys.aq$_agent; 
BEGIN 
   subscriber := sys.aq$_agent('SUBSCRIBER1', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
      queue_name         =>    'aq.msg_queue', 
      subscriber         =>     subscriber, 
      rule               =>    'priority < 2'); 
END; 
/* Change rule for subscriber: */ 
DECLARE 
   subscriber          sys.aq$_agent; 
BEGIN 
   subscriber := sys.aq$_agent('SUBSCRIBER1', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ALTER_SUBSCRIBER(
      queue_name         =>    'aq.msg_queue', 
      subscriber         =>     subscriber, 
      rule              =>     'priority = 1'); 
END;
Add a Subscriber with a Transformation
/* Add a subscriber with transformation */ 
EXECUTE DBMS_AQADM.ADD_SUBSCRIBER
   ('aq.msg_queue',
     aq$_agent('subscriber1',
        'aq2.msg_queue2@london',
            null),
'AQ.MSG_MAP1');
/* Alter the subscriber*/ 
EXECUTE DBMS_AQADM.ALTER_SUBSCRIBER
   ('aq.msg_queue',
     aq$_agent ('subscriber1',
      'aq2.msg_queue2@london',
                null),
       'AQ.MSG.MAP2');
Java (JDBC): Altering a Subscriber
/* Alter the rule for a subscriber */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     /* add a subscriber */
     agent1 = new AQAgent("subscriber1", "aq2.msg_queue2@london");

     queue.alterSubscriber(agent1, "priority=1");
}

Removing a Subscriber

Figure 9-21 Removing a Subscriber

Text description of adque062.gif follows
Text description of the illustration adque062.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Remove a default subscriber from a queue.

Usage Notes

This operation takes effect immediately and the containing transaction is committed. All references to the subscriber in existing messages are removed as part of the operation.

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:

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.

Examples in the following programmatic environments are provided:

PL/SQL (DBMS_AQADM): Removing Subscriber
DECLARE
   subscriber       sys.aq$_agent;
BEGIN
   subscriber := sys.aq$_agent('subscriber1','aq2.msg_queue2', NULL);
   DBMS_AQADM.REMOVE_SUBSCRIBER(
      queue_name => 'aq.multi_queue',
      subscriber => subscriber);
END;
Java (JDBC): Removing a Subscriber
/* Remove a subscriber */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     /* add a subscriber */
     agent1 = new AQAgent("subscriber1", "aq2.msg_queue2@london");

     queue.removeSubscriber(agent1);
}

Scheduling a Queue Propagation

Figure 9-22 Scheduling a Queue Propagation

Text description of adque063.gif follows
Text description of the illustration adque063.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Schedule propagation of messages from a queue to a destination identified by a specific dblink.

Usage Notes

Messages may also be propagated to other queues in the same database by specifying a NULL destination. If a message has multiple recipients at the same destination in either the same or different queues the message will be propagated to all of them at the same time.

See Chapter 17, "Internet Access to Advanced Queuing" for information on propagating messages over HTTP or HTTPS.

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:

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): Scheduling a Queue Propagation
Note:

You may need to set up the following data structures for certain examples to work:

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
   queue_table        => 'aq.objmsgs_qtab', 
   queue_payload_type => 'aq.message_typ',
   multiple_consumers => TRUE);
EXECUTE DBMS_AQADM.CREATE_QUEUE (
   queue_name         => 'aq.q1def',
   queue_table        => 'aq.objmsgs_qtab');

Scheduling a Propagation from a Queue to other Queues in the Same Database

/* Schedule propagation from queue aq.q1def to other queues in the same 
 database  */
EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(
   Queue_name    =>    'aq.q1def');

Scheduling a Propagation from a Queue to other Queues in Another Database

/* Schedule a propagation from queue aq.q1def to other queues in another 
   database */
EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world');
Java (JDBC): Scheduling a Queue Propagation
/* Setup  */
public static void setup(AQSession aq_sess) throws AQException
{
     AQQueueTableProperty    qtable_prop;
     AQQueueProperty         queue_prop;
     AQQueueTable            q_table;
     AQQueue                 queue;

     qtable_prop = new AQQueueTableProperty("AQ.MESSAGE_TYP"); 
     qtable_prop.setMultiConsumer(true);

     q_table = aq_sess.createQueueTable ("aq", "objmsgs_qtab", qtable_prop);

     /* Create a new AQQueueProperty object: */
     queue_prop = new AQQueueProperty();
     queue = aq_sess.createQueue (q_table, "q1def", queue_prop);
}

/* Schedule propagation from a queue to other queues in the same database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.schedulePropagation(null, null, null, null, null);
}

/* Schedule propagation from a queue to other queues in another database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.schedulePropagation("another_db.world", null, null, null, null);
}

Unscheduling a Queue Propagation

Figure 9-23 Unscheduling a Queue Propagation

Text description of adque064.gif follows
Text description of the illustration adque064.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Unscheduled previously scheduled propagation of messages from a queue to a destination identified by a specific dblink.

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:

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): Unscheduling a Propagation

Unscheduling Propagation from Queue To Other Queues in the Same Database

/* Unschedule propagation from queue aq.q1def to other queues in the same 
   database */ 
EXECUTE DBMS_AQADM.UNSCHEDULE_PROPAGATION(queue_name => 'aq.q1def');

Unscheduling Propagation from a Queue to other Queues in Another Database

/* Unschedule propagation from queue aq.q1def to other queues in another 
 database reached by the database link another_db.world */
EXECUTE DBMS_AQADM.UNSCHEDULE_PROPAGATION(
   Queue_name    =>   'aq.q1def', 
   Destination   =>   'another_db.world');
Java (JDBC): Unscheduling a Queue propagation
/* Unschedule propagation from a queue to other queues in the same database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.unschedulePropagation(null);
}

/* Unschedule propagation from a queue to other queues in another database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.unschedulePropagation("another_db.world");
}

Verifying a Queue Type

Figure 9-24 Verifying a Queue Type

Text description of adque065.gif follows
Text description of the illustration adque065.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Verify that the source and destination queues have identical types. The result of the verification is stored in sys.aq$_Message_types tables, overwriting all previous output of this command.

Usage Notes

If the source and destination queues do not have identical types and a transformation was specified, the transformation must map the source queue type to the destination queue type.

Note:

The sys.aq$_message_types table can have multiple entries for the same source queue, destination queue, and dblink, but with different transformations.

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:

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): Verifying a Queue Type
Note:

You may need to set up the following data structures for certain examples to work:

EXECUTE DBMS_AQADM.CREATE_QUEUE ( 
   queue_name         => 'aq.q2def',
   queue_table        => 'aq.objmsgs_qtab');


/*  Verify if the source and destination queues have the same type. The 
 function has the side effect of inserting/updating the entry for the source 
 and destination queues in the dictionary table AQ$_MESSAGE_TYPES */ 
DECLARE 
rc      BINARY_INTEGER; 
BEGIN 
/* Verify if the queues aquser.q1def and aquser.q2def in the local database 
 have the same payload type */ 
   DBMS_AQADM.VERIFY_QUEUE_TYPES(
      src_queue_name  => 'aq.q1def', 
      dest_queue_name => 'aq.q2def',
      rc              => rc); 
   DBMS_OUTPUT.PUT_LINE(rc);
END;
Java (JDBC): Verifying a Queue type

Feature not available through Java API

Altering a Propagation Schedule

Figure 9-25 Altering a Propagation Schedule

Text description of adque066.gif follows
Text description of the illustration adque066.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

To alter parameters for a propagation schedule.

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:

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 Propagation Schedule

Altering a Schedule from a Queue to Other Queues in the Same Database

/* Alter schedule from queue aq.q1def to other queues in the same database */
EXECUTE DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Duration      =>    '2000', 
   Next_time     =>    'SYSDATE + 3600/86400',
   Latency       =>    '32');

Altering a Schedule from a Queue to Other Queues in Another Database

/* Alter schedule from queue aq.q1def to other queues in another database 
reached by the database link another_db.world */
EXECUTE DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world', 
   Duration      =>    '2000', 
   Next_time     =>    'SYSDATE + 3600/86400',
   Latency       =>    '32');
Java (JDBC): Altering a Propagation Schedule
/* Alter propagation schedule from a queue to other queues 
   in the same database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.alterPropagationSchedule(null, new Double(2000), 
                    "SYSDATE + 3600/86400", new Double(32));
}

/* Unschedule propagation from a queue to other queues in another database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.alterPropagationSchedule("another_db.world", new Double(2000), 
                    "SYSDATE + 3600/86400", new Double(32));
}

Enabling a Propagation Schedule

Figure 9-26 Enabling a Propagation Schedule

Text description of adque067.gif follows
Text description of the illustration adque067.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

To enable a previously disabled propagation schedule.

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:

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): Enabling a Propagation

Enabling Propagation from a Queue to Other Queues in the Same Database

/* Enable propagation from queue aq.q1def to other queues in the same 
 database */
EXECUTE DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE(
   Queue_name   =>   'aq.q1def');

Enabling Propagation from a Queue to Queues in Another Database

/* Enable propagation from queue aq.q1def to other queues in another 
 database reached by the database link another_db.world */
EXECUTE DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world');
Java (JDBC): Enabling a Propagation Schedule
/* Enable propagation from a queue to other queues in the same database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.enablePropagationSchedule(null); 
}

/* Enable propagation from a queue to other queues in another database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.enablePropagationSchedule("another_db.world");
}

Disabling a Propagation Schedule

Figure 9-27 Disabling a Propagation Schedule

Text description of adque068.gif follows
Text description of the illustration adque068.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

To disable a previously enabled propagation schedule.

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:

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): Disabling a Propagation

Enabling Propagation from a Queue to Other Queues in the Same Database

/* Disable a propagation from queue aq.q1def to other queues in the same 
 database */
EXECUTE DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE(
   Queue_name   =>   'aq.q1def');

Enabling Propagation from a Queue to Queues in Another Database

/* Disable a propagation from queue aq.q1def to other queues in another 
 database reached by the database link another_db.world */
EXECUTE DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world');
Java (JDBC): Disabling a Propagation Schedule
/* Disable propagation from a queue to other queues in the same database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.disablePropagationSchedule(null);    
}

/* Disable propagation from a queue to other queues in another database */
public static void example(AQSession aq_sess) throws AQException
{
     AQQueue         queue;
     AQAgent         agent1;        
     AQAgent         agent2;        

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

     queue.disablePropagationSchedule("another_db.world");
}

Creating an AQ Agent

Figure 9-28 Creating an AQ Agent

Text description of adque440.gif follows
Text description of the illustration adque440.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Registers an agent for AQ Internet access using HTTP/SMTP protocols.

Usage Notes

The SYS.AQ$INTERNET_USERS view has a list of all AQ Internet agents.

When an AQ agent is created, altered, or dropped, an LDAP entry is created for the agent if the following are true:

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:

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:

Altering an AQ Agent

Figure 9-29 Altering an AQ Agent

Text description of adque441.gif follows
Text description of the illustration adque441.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Alters an agent registered for AQ Internet access.

Usage Notes

When an AQ agent is created, altered, or dropped, an LDAP entry is created for the agent if the following are true:

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:

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:

Dropping an AQ Agent

Figure 9-30 Dropping an AQ Agent

Text description of adque442.gif follows
Text description of the illustration adque442.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Drops an agent that was previously registered for AQ Internet access.

Usage Notes

When an AQ agent is created, altered, or dropped, an LDAP entry is created for the agent if the following are true:

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:

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:

Enabling Database Access

Figure 9-31 Enabling Database Access

Text description of adque443.gif follows
Text description of the illustration adque443.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Grants an AQ Internet agent the privileges of a specific database user. The AQ Internet agent should have been previously created using the CREATE_AQ_AGENT procedure.

Usage Notes

The SYS.AQ$INTERNET_USERS view has a list of all AQ Internet agents and the names of the database users whose privileges are granted to them.

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:

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:

Disabling Database Access

Figure 9-32 Disabling Database Access

Text description of adque444.gif follows
Text description of the illustration adque444.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

Revokes the privileges of a specific database user from an AQ Internet agent. The AQ Internet agent should have been previously granted those privileges using the ENABLE_DB_ACCESS procedure.

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:

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:

Adding an Alias to the LDAP Server

Figure 9-33 Adding an Alias to the LDAP Server

Text description of adque402.gif follows
Text description of the illustration adque402.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

To add an alias to the LDAP server.

Usage Notes

This call takes the name of an alias and the distinguished name of an AQ object in LDAP, and creates the alias that points to the AQ object. The alias is placed immediately under the distinguished name of the database server. The object to which the alias points can be a queue, an agent, or a connection factory.

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:

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:

Removing an Alias from the LDAP Server

Figure 9-34 Removing an Alias from the LDAP Server

Text description of adque403.gif follows
Text description of the illustration adque403.gif

See Also:

Table 9-1 for a list of adminstrative interface basic operations

Purpose

To remove an alias from the LDAP server.

Usage Notes

This call takes the name of an alias as the argument, and removes the alias entry in the LDAP server. It is assumed that the alias is placed immediately under the database server in the LDAP directory.

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:

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:

Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback