You can use the stored procedures in the DBMS_AQADM package to create and manage message queues and queue tables.

Table 1. DBMS_AQADM functions and stored procedures
Function or stored procedure Return value type Description
ALTER_QUEUE N/A Modifies an existing queue.
ALTER_QUEUE_TABLE N/A Modifies an existing queue table.
CREATE_QUEUE N/A Create a queue.
CREATE_QUEUE_TABLE N/A Creates a queue table.
DROP_QUEUE N/A Drops an existing queue.
DROP_QUEUE_TABLE N/A Drops an existing queue table.
PURGE_QUEUE_TABLE N/A Removes one or more messages from a queue table.
START_QUEUE N/A Makes a queue available for enqueuing and dequeuing procedures.
STOP_QUEUE N/A Makes a queue unavailable for enqueuing and dequeuing procedures.

The following table describes the constants that are supported by PolarDB for Oracle.

Constant Description Applicable parameter
DBMS_AQADM.TRANSACTIONAL(1) This constant is defined. An error message is returned if this constant is used. message_grouping
DBMS_AQADM.NONE(0) Specifies message grouping for a queue table. message_grouping
DBMS_AQADM.NORMAL_QUEUE(0) Used with create_queue to specify queue_type. queue_type
DBMS_AQADM.NORMAL_QUEUE(0) Used with create_queue to specify queue_type. queue_type
DBMS_AQADM.INFINITE(-1) Used with create_queue to specify retention_time. retention_time
DBMS_AQADM.PERSISTENT (0) The message must be stored in a table. enqueue_options_t.delivery_mode
DBMS_AQADM.BUFFERED (1) This constant is defined. An error message is returned if this constant is used. enqueue_options_t.delivery_mode
DBMS_AQADM.PERSISTENT_OR_BUFFERED (2) This constant is defined. An error message is returned if this constant is used. enqueue_options_t.delivery_mode

ALTER_QUEUE

You can use the ALTER_QUEUE stored procedure to modify an existing queue. Syntax:

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

Parameter Description
queue_name The name of the new queue.
max_retries The maximum number of failed attempts allowed before a message is removed with the DEQUEUE statement.

The value of max_retries is incremented each time the ROLLBACK statement is executed. When the number of failed attempts reaches the value specified by max_retries, the message is moved to the exception queue. A value of 0 means that no retries are allowed.

retry_delay The number of seconds elapsed between a rollback and message scheduling for re-processing. The default value of 0 means that the message must be re-processed immediately.
retention_time The number of seconds elapsed between dequeuing and storage for a message. Unit: seconds.
Note
  • The default value of 0 means that the message cannot be retained after being dequeued.
  • A value of INFINITE means that a message is retained forever.
comment A comment associated with a queue.

Examples

The following code block alters a queue named retry_delay and sets 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 stored procedure to modify an existing queue table. Syntax:

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

Parameter Description
queue_table The name of the queue table.
comment A comment about a queue table.
primary_instance This parameter is used for compatibility and can be ignored.
secondary_instance This parameter is used for compatibility and can be ignored.

Examples

The following code block modifies 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.');

CREATE_QUEUE

You can use the CREATE_QUEUE stored procedure to create a queue in an existing queue table. Syntax:

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

Parameter Description
queue_name The name of the new queue.
queue_table The name of the table where the new queue is located.
queue_type The type of the new queue. Valid values:
  • DBMS_AQADM.NORMAL_QUEUE: a normal queue. This is the default value.
  • DBMS_AQADM.EXCEPTION_QUEUE: an exception queue. An exception queue only supports dequeue operations.
max_retries The maximum number of failed attempts allowed before a message is removed with the DEQUEUE statement. The value of the dequeue increases as the number of ROLLBACK statements increases. When the number of failed attempts reaches the value specified by max_retries, the message is moved to the exception queue. The default value for a system table is 0. The default value for a user-defined table is 5.
retry_delay The number of seconds elapsed between a rollback and message scheduling for re-processing. If specified as the default value of 0, the message should be retried immediately.
retention_time The number of seconds elapsed between dequeuing and storage for a message. Unit: seconds.
Note
  • The default value of 0 means that the message cannot be retained after being dequeued.
  • A value of INFINITE means that a message is retained forever.
dependency_tracking This parameter is used for compatibility and can be ignored.
comment A comment associated with a queue.
auto_commit This parameter is used for compatibility and can be ignored.

Examples

The following anonymous block creates 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 stored procedure to create a queue table. The procedure has the following signature: Syntax:

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

Parameter Description
queue_table The name of the queue table.
queue_payload_type The user-defined type of the data to be stored in the queue table.
Note To specify a RAW data type, you must create a user-defined type that identifies a RAW type.
storage_clause Specifies the attributes for the queue table.
  • storage_clause can be set to one or more of the following options:
    • TABLESPACE tablespace_name
    • PCTFREE integer
    • PCTUSED integer
    • INITRANS integer
    • MAXTRANS integer
    • STORAGE storage_option
  • storage_option can be set to one or more of the following options:
    • MINEXTENTS integer
    • MAXEXTENTS integer
    • PCTINCREASE integer
    • INITIAL size_clause
    • NEXT
    • FREELISTS integer
    • OPTIMAL size_clause
    • BUFFER_POOL {KEEP|RECYCLE|DEFAULT}
sort_list This parameter controls the dequeuing order of the queue and specifies the names of the columns that are used to sort the queue in ascending order. Valid values:
  • enq_time, priority
  • priority, enq_time
  • priority
  • enq_time
multiple_consumers This parameter must be set to FALSE if required.
message_grouping This parameter must be set to NONE if required.
comment A comment about a queue table.
auto_commit This parameter is used for compatibility and can be ignored.
primary_instance This parameter is used for compatibility and can be ignored.
secondary_instance This parameter is used for compatibility and can be ignored.
compatible This parameter is used for compatibility and can be ignored.
secure This parameter is used for compatibility and can be ignored.

Examples

The following anonymous block creates the work_order type with the attributes that hold the VARCHAR2 name and the 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 stored procedure to drop a queue. Syntax:

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

Parameters

Parameter Description
queue_name The name of the queue that you want to drop.
auto_commit This parameter is used for compatibility and can be ignored.

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 stored procedure to drop a queue table. Syntax:

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

Parameters

Parameter Description
queue_table The name of the queue table.
force Specifies the behavior of the DROP_QUEUE_TABLE command when the command is used to drop a table that contain entries:
  • If the table contains entries and force is set to FALSE, the command fails and an error message is returned.
  • If the table contains entries and force is set to TRUE, the command drops the table and all dependent objects.
auto_commit This parameter is used for compatibility and can be ignored.

Examples

The following anonymous block drops the 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 stored procedure to delete messages from a queue table. Syntax:

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

Parameters

Parameter Description
queue_table The name of the queue table from which you want to delete a message.
purge_condition Specifies as the condition that the server evaluates when the server determines the messages to be deleted. The condition is specified in a SQL WHERE clause.
purge_options An object of the aq$_purge_options_t type. An aq$_purge_options_t object contains certain attributes. For more information, see Table 2.
Table 2. aq$_purge_options_t
Attribute Type Description
Block Boolean Specifies whether to retain exclusive locks on all queues in the table. Default value: FALSE. Valid values:
  • TRUE
  • FALSE
delivery_mode INTEGER Specifies the type of message to be deleted. The value must be DBMS_AQ.PERSISTENT.

Examples

The following anonymous block removes 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 stored procedure to enable enqueuing or dequeuing on a specified queue. Syntax:

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

Parameters

Parameter Description
queue_name The name of the queue.
enqueue
  • Specifies whether to enable queuing. Default value: TRUE. Valid values: TRUE: enables queuing.
  • FALSE: keeps the current settings.
dequeue
  • Specifies whether to enable dequeuing. Default value: TRUE. Valid values: TRUE: enables dequeuing.
  • FALSE: keeps the current settings.

Examples

The following anonymous block makes 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 stored procedure to disable enqueuing or dequeuing on a specified queue. Syntax:

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

Parameters

Parameter Description
queue_name The name of the queue.
enqueue
  • Specifies whether to disable queuing. Default value: TRUE. Valid values: TRUE: disables queuing.
  • FALSE: keeps the current settings.
dequeue
  • Specifies whether to disable dequeuing. Default value: TRUE. Valid values: TRUE: disables dequeuing.
  • FALSE: keeps the current settings.
wait
  • Specified whether the server waits for any uncompleted transactions to complete before the server applies the specified changes. Valid values: TRUE: the server waits. When the server waits to disable the queue, no transactions are allowed to be enqueued to or dequeued from the specified queue.
  • FALSE: immediately disables enqueuing or dequeuing on the queue.

Examples

The following anonymous block disables enqueuing and dequeuing for 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 disabled after all pending transactions are complete.