Use the stored procedures in the DBMS_AQADM package to create and manage message queues and queue tables - PolarDB

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

Note

Superuser permissions are required to use the stored procedures in the DBMS_AQADM package to create and manage message queues and queue tables. If you need to use this feature, Contact us.

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	Creates 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 in 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 aq$_purge_options_t.

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.