DBMS_AQADM

ALTER_QUEUE

You can use the ALTER_QUEUE procedure to modify an existing queue. The procedure has the following signature:

ALTER_QUEUE(
  max_retries IN NUMBER DEFAULT NULL,
  retry_delay IN NUMBER DEFAULT 0
  retention_time IN NUMBER DEFAULT 0,
  auto_commit IN BOOLEAN DEFAULT TRUE)
  comment IN VARCHAR2 DEFAULT NULL,

Parameters

Examples

The following command is used to alter a queue named work_order and set the retry_delay parameter to 5 seconds:

EXEC DBMS_AQADM.ALTER_QUEUE(queue_name => 'work_order', retry_delay => 5);

ALTER_QUEUE_TABLE

You can use the ALTER_QUEUE_TABLE procedure to modify an existing queue table. The procedure has the following signature:

ALTER_QUEUE_TABLE (
  queue_table IN VARCHAR2,
  comment IN VARCHAR2 DEFAULT NULL,
  primary_instance IN BINARY_INTEGER DEFAULT 0,
  secondary_instance IN BINARY_INTEGER DEFAULT 0,

Parameters

Examples

The following command is used to modify a queue table named work_order_table:

EXEC DBMS_AQADM.ALTER_QUEUE_TABLE
      (queue_table => 'work_order_table', comment => 'This queue table contains work orders for the shipping department.') ;

The name of the queue table is work_order_table. The command is used to add a comment to the definition of the queue table.

CREATE_QUEUE

You can use the CREATE_QUEUE procedure to create a queue in an existing queue table. The procedure has the following signature:

CREATE_QUEUE(
  queue_name IN VARCHAR2
  queue_table IN VARCHAR2,
  queue_type IN BINARY_INTEGER DEFAULT NORMAL_QUEUE,
  max_retries IN NUMBER DEFAULT 5,
  retry_delay IN NUMBER DEFAULT 0
  retention_time IN NUMBER DEFAULT 0,
  dependency_tracking IN BOOLEAN DEFAULT FALSE,
  comment IN VARCHAR2 DEFAULT NULL,
  auto_commit IN BOOLEAN DEFAULT TRUE)

Parameters

Examples

The following anonymous block is used to create a queue named work_order in the work_order_table table:

BEGIN
DBMS_AQADM.CREATE_QUEUE ( queue_name => 'work_order', queue_table => 'work_order_table', comment => 'This queue contains pending work orders.') ;
END;

CREATE_QUEUE_TABLE

You can use the CREATE_QUEUE_TABLE procedure to create a queue table. The procedure has the following signature:

CREATE_QUEUE_TABLE (
  queue_table IN VARCHAR2,
  queue_payload_type IN VARCHAR2,
  storage_clause IN VARCHAR2 DEFAULT NULL,
  sort_list IN VARCHAR2 DEFAULT NULL,
  multiple_consumers IN BOOLEAN DEFAULT FALSE,
  message_grouping IN BINARY_INTEGER DEFAULT NONE,
  comment IN VARCHAR2 DEFAULT NULL,
  auto_commit IN BOOLEAN DEFAULT TRUE,
  primary_instance IN BINARY_INTEGER DEFAULT 0,
  secondary_instance IN BINARY_INTEGER DEFAULT 0,
  compatible IN VARCHAR2 DEFAULT NULL,
  secure IN BOOLEAN DEFAULT FALSE)

Parameters

Examples

The following anonymous block is used to create the work_order type with the attributes that hold the VARCHAR2 name and the TEXT project description. Then, the block uses this type to create a queue table.

BEGIN

CREATE TYPE work_order AS (name VARCHAR2, project TEXT, completed BOOLEAN);

EXEC DBMS_AQADM.CREATE_QUEUE_TABLE
      (queue_table => 'work_order_table',
       queue_payload_type => 'work_order',
       comment => 'Work order message queue table');
END;

The queue table is named work_order_table and contains a payload of the work_order type. A comment is added to indicate that this is the work order message queue table.

DROP_QUEUE

You can use the DROP_QUEUE procedure to drop a queue. The procedure has the following signature:

DROP_QUEUE(
  queue_name  IN VARCHAR2,
  auto_commit IN BOOLEAN DEFAULT TRUE)

Parameters

Examples

The following anonymous block drops the queue named work_order:

BEGIN
DBMS_AQADM.DROP_QUEUE(queue_name => 'work_order');
END;

DROP_QUEUE_TABLE

You can use the DROP_QUEUE_TABLE procedure to drop a queue table. The procedure has the following signature:

DROP_QUEUE_TABLE(
  queue_table IN VARCHAR2,
  force IN BOOLEAN default FALSE,
  auto_commit IN BOOLEAN default TRUE)

Parameters

Examples

The following anonymous block is used to drop a table named work_order_table:

BEGIN
   DBMS_AQADM.DROP_QUEUE_TABLE ('work_order_table', force => TRUE);
END;

PURGE_QUEUE_TABLE

You can use the PURGE_QUEUE_TABLE procedure to delete messages from a queue table. The procedure has the following signature:

PURGE_QUEUE_TABLE(
  queue_table IN VARCHAR2,
  purge_condition IN VARCHAR2,
  purge_options IN aq$_purge_options_t)

Parameters

Examples

The following anonymous block is used to remove any messages from work_order_table where the value of the column named completed is YES:

DECLARE
   purge_options dbms_aqadm.aq$_purge_options_t;
BEGIN
   dbms_aqadm.purge_queue_table('work_order_table', 'completed = YES', purge_options);
  END;

START_QUEUE

You can use the START_QUEUE procedure to make a queue available for enqueuing and dequeuing. The procedure has the following signature:

START_QUEUE(
  queue_name IN VARCHAR2,
  enqueue IN BOOLEAN DEFAULT TRUE,
  dequeue IN BOOLEAN DEFAULT TRUE)

Parameters

Examples

The following anonymous block is used to make a queue named work_order available for enqueuing:

BEGIN
DBMS_AQADM.START_QUEUE
(queue_name => 'work_order);
END;

STOP_QUEUE

You can use the STOP_QUEUE procedure to disable enqueuing or dequeuing on a specified queue. The procedure has the following signature:

STOP_QUEUE(
  queue_name IN VARCHAR2,
  enqueue IN BOOLEAN DEFAULT TRUE,
  dequeue IN BOOLEAN DEFAULT TRUE,
  wait IN BOOLEAN DEFAULT TRUE)

Parameters

Examples

The following anonymous block is used disable enqueuing to and dequeuing from the queue named work_order:

BEGIN
DBMS_AQADM.STOP_QUEUE(queue_name =>'work_order', enqueue=>TRUE, dequeue=>TRUE, wait=>TRUE);
END;

Enqueuing and dequeuing are stopped after all outstanding transactions are completed.