AQ (Advanced Queuing)

2.9.5 AQ (Advanced Queuing)

The AQ command in SQLcl enables you to access the Oracle Database Advanced Queuing functionality to create queues and queue tables, enqueue and dequeue messages, add and remove subscribers, and manage transactional event queues (TxEventQ).

Prerequisites

This command requires the ADMIN role.

You must have some working knowledge of Advanced Queuing and Transactional Event Queues and be familiar with the concepts, which are explained in the Oracle Database Advanced Queuing and Transactional Event Queues User's Guide.

Supported Commands

You can run Advanced Queuing commands in SQLcl using aq.

To view the help information for Advanced Queuing, enter aq help.

Example

The following example uses Advanced Queuing commands in SQLcl to create a queue, start the queue, add a subscriber to the queue, enqueue a message and then dequeue it.

--Create a queue named demo2 with multiple consumers and payload type JSON
SQL> aq createteq -name demo2 -mul true -pt JSON

--Show the queue properties
SQL> select name, qid, queue_type, enqueue_enabled, dequeue_enabled, sharded, queue_category, recipients from user_queues;

--Start the queue demo2
SQL> aq startq -name demo2
SQL> select name, qid, queue_type, enqueue_enabled, dequeue_enabled, sharded, queue_category, recipients from user_queues;

–-Add subscriber named Bob to queue demo2
SQL> aq add subscriber -queue demo2 -name bob;

--Enqueue message "hello" to queue demo2
SQL> aq enq -name demo2 -p {"message":"hello"};

--Commit the message (send)
SQL> commit;

–-Dequeue from queue demo2 by subscriber named Bob using NEXT_MESSAGE navigation after waiting for 2 seconds
SQL> aq deq -name demo2 -nav NEXT_MESSAGE -consumer_name bob -wait 2;

2.9.5.1 Create a Queue Table

Syntax

AQ CREATE QUEUE TABLE/CREATEQT -n[ame] table_name
  [-pay[load_type] {JSON | RAW | [schema.]type_name}]  
  [-sto[rage_clause] string]
  [-sort[_list] string]  
  [-mul[tiple_consumers] {NO | YES}]  
  [-com[ment] string]
  [-primary[_instance] number]  
  [-secondary[_instance] number]  
  [-secure {YES | NO}]  
  [-repl[ication_mode] {NONE | REPLICATION}]

Parameters

Parameter	Description	Default
`-name,-n`	<name> Name of a queue table to create.	-
`-payload_type,-pt`	`[OBJECT NAME\|TYPE NAME\|JSON]` Type of the user data stored.	JSON
`-storage_clause,-sto`	`<storage_clause>`Storage parameter. The storage parameter is included in the CREATE TABLE statement when the queue table is created. The storage parameter can be made up of any combinations of the following parameters: PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, LOB, and a table storage clause.	NULL
`-sort_list,-sort`	<sort_list> The columns to be used as the sort key in ascending order. This parameter has the following format: 'sort_column_1,sort_column_2' The allowed column names are priority, enq_time, and commit_time.	ENQ_TIME
`-multiple_consumers,-mul`	`[TRUE\|FALSE]` TRUE: Queues created in the table can only have one consumer for each message. FALSE: Queues created in the table can have multiple consumers for each message.	False
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-primary_instance,-primary`	<primary_instance> The primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table are done in this instance.	0
`-secondary_instance,-secondary`	<secondary_instance> The queue table fails over to the secondary instance if the primary instance is not available.	0
`-secure,-sec`	`[TRUE\|FALSE]` TRUE: Use the queue table for secure queues. Secure queues are queues for which AQ agents must be associated explicitly with one or more database users.	False
`-replication_mode,-repl`	`[0:NONE\|1:REPLICATION_MODE]`	0:NONE

2.9.5.2 Alter a Queue Table

Syntax

AQ ALTER QUEUE TABLE/ALTERQT -n[ame] table_name
  [-com[ment] string]
  [-primary[_instance] number]
  [-secondary[_instance] number]
  [-repl[ication_mode] {NONE | REPLICATION}]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue table to alter.	-
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-primary_instance,-primary`	<primary_instance> The primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table are done in this instance.	0
`-secondary_instance,-secondary`	<secondary_instance> The queue table fails over to the secondary instance if the primary instance is not available.	0
`-replication_mode,-repl`	`[0:NONE\|1:REPLICATION_MODE]`	0:NONE

2.9.5.3 Drop a Queue Table

Syntax

AQ DROP QUEUE TABLE/DROPQT -n[ame] table_name
  [-for[ce] {NO | YES}]

Parameters

Parameter Description Default

-name,-n <name> Name of a queue table to drop. -

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue table to drop.	-
`-force,-f`	`[TRUE\|FALSE]` TRUE: All queues in the table are stopped and dropped automatically. FALSE: The operation does not succeed if there are any queues in the table.	False

-force,-f

[TRUE|FALSE]

TRUE: All queues in the table are stopped and dropped automatically.

FALSE: The operation does not succeed if there are any queues in the table.

False

2.9.5.4 Create a Queue

Syntax

AQ CREATE QUEUE/CREATEQ -n[ame] queue_name
  -tab[le_name] [schema.]table_name
  [-type {NORMAL | EXCEPTION}]  
  [-max[_retries] number]  
  [-retry[_delay] number]  
  [-retention[_time] number] 
  [-com[ment] string]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to create.	-
`-table_name,-tab`	`[schema].<table_name>` Name of the queue table that will contain the queue.	-
`-type,-t`	`[0:NORMAL QUEUE\|1:EXCEPTION QUEUE]` Specifies whether the queue being created is an exception queue or a normal queue.	0:NORMAL QUEUE
`-max_retries,-max`	<max_retries number> A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. RETRY_COUNT is incremented when the application issues a rollback after executing the dequeue.	5
`-retry_delay,-retry`	<retry_delay number> Delay time, in seconds, before this message is scheduled for processing again after an application rollback.	0
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-retention_time,-rtime`	<retention_time number> `[-1:INFINITE]` means the message is retained forever. Number of seconds that a message is retained in the queue table after being dequeued from the queue.	0

2.9.5.5 Alter a Queue

Syntax

AQ ALTER QUEUE/ALTERQ -n[ame] queue_name
  [-max[_retries] number]
  [-retry[_delay] number]
  [-retention[_time] number] 
  [-com[ment] string]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to alter.	-
`-max_retries,-max`	<max_retries number> A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. RETRY_COUNT is incremented when the application issues a rollback after executing the dequeue.	NULL - current value will not be altered.
`-retry_delay,-retry`	<retry_delay number> Delay time, in seconds, before this message is scheduled for processing again after an application rollback.	NULL - current value will not be altered.
`-retention_time,-rtime`	<retention_time number> `[-1:INFINITE]` means the message is retained forever. Number of seconds that a message is retained in the queue table after being dequeued from the queue.	0
`-comment,-c`	<comment> User-specified description of the queue table.	NULL

2.9.5.6 Drop a Queue

Syntax

AQ DROP QUEUE/DROPQ -n[ame] queue_name

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to drop.	-

2.9.5.7 Create a Sharded Queue

Syntax

AQ CREATE SHARDED QUEUE/CREATESQ -n[ame] queue_name
  [-sto[rage_clause] string]
  [-mul[tiple_consumers] {NO | YES}]  
  [-max[_retries] number]  
  [-com[ment] string]
  [-pay[load_type] {JSON | RAW | [schema.]type_name}]  
  [-retry[_delay] number]  
  [-retention_time number]  
  [-sort[_list] {PRIORITY,ENQ_TIME | ENQ_TIME}]  
  [-cache[_hint] {AUTO | UNCACHED | CACHED }] 
  [-retention_type string]  
  [-repl[ication_mode] NONE]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue table to create.	-
`-storage_clause,-sto`	`<storage_clause>`Storage parameter. The storage parameter is included in the CREATE TABLE statement when the queue table is created. The storage parameter can be made up of any combinations of the following parameters: PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, LOB, and a table storage clause.	NULL
`-multiple_consumers,-mul`	`[TRUE\|FALSE]` TRUE: Queues created in the table can only have one consumer for each message. FALSE: Queues created in the table can have multiple consumers for each message.	False
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-payload_type,-pt`	`[OBJECT NAME\|TYPE NAME\|JSON\|JMS]` Type of the user data stored.	JMS
`-retry_delay,-retry`	<retry_delay number> Delay time, in seconds, before this message is scheduled for processing again after an application rollback.	0
`-retention_time,-rtime`	<retention_time number> `[-1:INFINITE]` means the message is retained forever. Number of seconds that a message is retained in the queue table after being dequeued from the queue.	0
`-sort_list,-sort`	<sort_list> The columns to be used as the sort key in ascending order. This parameter has the following format: 'sort_column_1,sort_column_2' The allowed column names are priority, enq_time, and commit_time.	PRIORITY,ENQ_TIME

`-cache_hint,-cache`	`[1:AUTO\|2:CACHED\|3:UNCACHED]` Specifies a hint to sharded queue whether to cache messages or not. User can specify following: [1:AUTO]: Message cache does best effort based on available memory. [2:CACHED]: Message cache will cache messages and give preference to those queues with CACHED hint.	1:AUTO
`-retention_type,-rtype`	`[0: DEQUEUE_TIME]` The retention time starts after a subshard is dequeued by all the subscribers. If a new subscriber seeks back before or to this subshard, then the retention clock is reset.	0:DEQUEUE_TIME
`-replication_mode,-repl`	`[0:NONE\|1:REPLICATION_MODE]`	0:NONE

2.9.5.8 Alter a Sharded Queue

Syntax

AQ ALTER SHARDED QUEUE/ALTERSQ -n[ame] queue_name
  [-max[_retries] number]
  [-com[ment] string]
  [-retry[_delay] number]
  [-retention_time number]  
  [-cache[_hint] {AUTO | UNCACHED | CACHED}]
  [-retention_type string]  
  [-repl[ication_mode] NONE]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to alter.	-
`-max_retries,-max`	<max_retries number> A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. RETRY_COUNT is incremented when the application issues a rollback after executing the dequeue.	NULL - current value will not be altered.
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-retry_delay,-retry`	<retry_delay number> Delay time, in seconds, before this message is scheduled for processing again after an application rollback.	0
`-retention_time,-rtime`	<retention_time number> `[-1:INFINITE]` means the message is retained forever. Number of seconds that a message is retained in the queue table after being dequeued from the queue.	0
`-sort_list,-sort`	<sort_list> The columns to be used as the sort key in ascending order. This parameter has the following format: 'sort_column_1,sort_column_2' The allowed column names are priority, enq_time, and commit_time.	PRIORITY,ENQ_TIME
`-cache_hint,-cache`	`[1:AUTO\|2:CACHED\|3:UNCACHED]` Specifies a hint to sharded queue whether to cache messages or not. User can specify following: [1:AUTO]: Message cache does best effort based on available memory. [2:CACHED]: Message cache will cache messages and give preference to those queues with CACHED hint.	1:AUTO
`-retention_type,-rtype`	`[0: DEQUEUE_TIME]` The retention time starts after a subshard is dequeued by all the subscribers. If a new subscriber seeks back before or to this subshard, then the retention clock is reset.	0:DEQUEUE_TIME
`-replication_mode,-repl`	`[0:NONE\|1:REPLICATION_MODE]`	0:NONE

2.9.5.9 Drop a Sharded Queue

Syntax

AQ DROP SHARDED QUEUE/DROPSQ -n[ame] queue_name
  [-for[ce] {NO | YES}]

Parameters

Parameter Description Default

-name,-n <name> Name of a queue table to drop. -

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue table to drop.	-
`-force,-f`	`[TRUE\|FALSE]` TRUE: The sharded queue is dropped even if the queue is not stopped. FALSE: The sharded queue is not dropped even if the queue is not stopped.	False

-force,-f

[TRUE|FALSE]

TRUE: The sharded queue is dropped even if the queue is not stopped.

FALSE: The sharded queue is not dropped even if the queue is not stopped.

False

2.9.5.10 Create an Exception Queue

Syntax

AQ CREATE EXCEPTION QUEUE/CREATEEQ -n[ame] queue_name
  -sharded[_queue_name] [schema.]queue_name

Parameters

Parameter Description Default

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to create.	NULL
`-sharded_queue_name,-sharded`	`[schema].<name>` The name of the sharded queue.	FALSE

-name,-n

<name>

Name of a queue to create.

NULL

-sharded_queue_name,-sharded

[schema].<name>

The name of the sharded queue.

FALSE

2.9.5.11 Create a Transactional Event Queue

Note:

This command is only available for Oracle Database release 21c and later versions.

Syntax

AQ CREATE TRANSACTIONAL EVENT QUEUE/CREATETEQ -n[ame] queue_name
  [-sto[rage_clause] string]
  [-mul[tiple_consumers] {NO | YES}]  
  [-max[_retries] number]  
  [-com[ment] string]
  [-pay[load_type] {JSON | RAW | JMS | [schema.]type_name}]  
  [-retry[_delay] number]  
  [-retention_time number]  
  [-sort[_list] {PRIORITY,ENQ_TIME | ENQ_TIME}]  
  [-cache[_hint] {AUTO | UNCACHED | CACHED}]  
  [-retention_type string]  
  [-repl[ication_mode NONE]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to create.	-
`-storage_clause,-sto`	`<storage_clause>`Storage parameter. The storage parameter is included in the CREATE TABLE statement when the queue table is created. The storage parameter can be made up of any combinations of the following parameters: PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, LOB, and a table storage clause.	NULL
`-multiple_consumers,-mul`	`[TRUE\|FALSE]` TRUE: Queues created in the table can only have one consumer for each message. FALSE: Queues created in the table can have multiple consumers for each message.	False
`-max_retries,-max`	<max_retries number> A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. RETRY_COUNT is incremented when the application issues a rollback after executing the dequeue.	5
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-payload_type,-pt`	`[OBJECT NAME\|TYPE NAME\|JSON\|JMS]` Type of the user data stored.	JMS
`-retry_delay,-retry`	<retry_delay number> Delay time, in seconds, before this message is scheduled for processing again after an application rollback.	0
`-retention_time,-rtime`	<retention_time number> `[-1:INFINITE]` means the message is retained forever. Number of seconds that a message is retained in the queue table after being dequeued from the queue.	0
`-sort_list,-sort`	<sort_list> The columns to be used as the sort key in ascending order. This parameter has the following format: 'sort_column_1,sort_column_2' The allowed column names are priority, enq_time, and commit_time.	PRIORITY,ENQ_TIME
`-cache_hint,-cache`	`[1:AUTO\|2:CACHED\|3:UNCACHED]` Specifies a hint to sharded queue whether to cache messages or not. User can specify following: [1:AUTO]: Message cache does best effort based on available memory. [2:CACHED]: Message cache will cache messages and give preference to those queues with CACHED hint.	1:AUTO
`-retention_type,-rtype`	`[0: DEQUEUE_TIME]` The retention time starts after a subshard is dequeued by all the subscribers. If a new subscriber seeks back before or to this subshard, then the retention clock is reset.	0:DEQUEUE_TIME
`-replication_mode,-repl`	`[0:NONE\|1:REPLICATION_MODE]`	0:NONE

2.9.5.12 Alter a Transactional Event Queue

Note:

This command is only available for Oracle Database release 21c and later versions.

Syntax

AQ ALTER TRANSACTIONAL EVENT QUEUE/ALTERTEQ -n[ame] queue_name
  [-max[_retries] number]
  [-com[ment] string]
  [-retry[_delay] number]
  [-retention_time number]  
  [-cache[_hint] {AUTO | UNCACHED | CACHED}]
  [-retention_type string]  
  [-repl[ication_mode NONE]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to alter.	-
`-max_retries,-max`	<max_retries number> A message is moved to an exception queue if RETRY_COUNT is greater than MAX_RETRIES. RETRY_COUNT is incremented when the application issues a rollback after executing the dequeue.	NULL - current value will not be altered.
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-retry_delay,-retry`	<retry_delay number> Delay time, in seconds, before this message is scheduled for processing again after an application rollback.	0
`-retention_time,-rtime`	<retention_time number> `[-1:INFINITE]` means the message is retained forever. Number of seconds that a message is retained in the queue table after being dequeued from the queue.	0
`-sort_list,-sort`	<sort_list> The columns to be used as the sort key in ascending order. This parameter has the following format: 'sort_column_1,sort_column_2' The allowed column names are priority, enq_time, and commit_time.	PRIORITY,ENQ_TIME
`-cache_hint,-cache`	`[1:AUTO\|2:CACHED\|3:UNCACHED]` Specifies a hint to sharded queue whether to cache messages or not. User can specify following: [1:AUTO]: Message cache does best effort based on available memory. [2:CACHED]: Message cache will cache messages and give preference to those queues with CACHED hint.	1:AUTO
`-retention_type,-rtype`	`[0: DEQUEUE_TIME]` The retention time starts after a subshard is dequeued by all the subscribers. If a new subscriber seeks back before or to this subshard, then the retention clock is reset.	0:DEQUEUE_TIME
`-replication_mode,-repl`	`[0:NONE\|1:REPLICATION_MODE]`	0:NONE

2.9.5.13 Drop a Transactional Event Queue

Note:

This command is only available for Oracle Database release 21c and later versions.

Syntax

AQ DROP TRANSACTIONAL EVENT QUEUE/DROPTEQ -n[ame] queue_name
  [-for[ce] {NO | YES}]

Parameters

Parameter Description Default

-name,-n <name> Name of a queue to drop. -

-force,-f

[TRUE|FALSE]

TRUE: The sharded queue is dropped even if the queue is not stopped.

FALSE: The sharded queue is not dropped even if the queue is not stopped.

FALSE

2.9.5.14 Create an EQ Exception Queue

Syntax

AQ CREATE EQ EXCEPTION QUEUE/CREATEEQEQ -n[ame] queue_name
  -teq[_queue_name] [schema.]queue_name

Parameters

Parameter Description Default

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to create.	NULL
`-teq_queue_name,-teq`	`[schema].<name>` The name of the transactional event queue.	FALSE

-name,-n

<name>

Name of a queue to create.

NULL

-teq_queue_name,-teq

[schema].<name>

The name of the transactional event queue.

FALSE

2.9.5.15 Start a Queue

Syntax

AQ START QUEUE/STARTQ -n[ame] queue_name
  [-enq[ueue] {ENABLE | KEEP}]
  [-deq[ueue] {ENABLE | KEEP}]

Parameters

Parameter Description Default

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to enable.	NULL
`-enqueue,-enq`	`[TRUE\|FALSE]` TRUE: Enable ENQUEUE. FALSE: Do not alter the current setting.	TRUE
`-dequeue,-deq`	`[TRUE\|FALSE]` TRUE: Enable DEQUEUE. FALSE: Do not alter the current setting.	TRUE

-name,-n

<name>

Name of a queue to enable.

NULL

-enqueue,-enq

[TRUE|FALSE]

TRUE: Enable ENQUEUE.

FALSE: Do not alter the current setting.

TRUE

-dequeue,-deq

[TRUE|FALSE]

TRUE: Enable DEQUEUE.

FALSE: Do not alter the current setting.

TRUE

2.9.5.16 Stop a Queue

Syntax

AQ STOP QUEUE/STOPQ -n[ame] queue_name
  [-enq[ueue] {DISABLE | KEEP}]  
  [-deq[ueue] {DISABLE | KEEP}]  
  [-wait {YES | NO}]

Parameters

Parameter	Description	Default
`-name,-n`	`<name>` Name of a queue to disable.	NULL
`-enqueue,-enq`	`[TRUE\|FALSE]` TRUE: Enable ENQUEUE. FALSE: Do not alter the current setting.	TRUE
`-dequeue,-deq`	`[TRUE\|FALSE]` TRUE: Enable DEQUEUE. FALSE: Do not alter the current setting.	TRUE
`-wait,-w`	`[TRUE\|FALSE` TRUE: Wait if there are any outstanding transactions. FALSE: Return immediately either with a success or an error.	TRUE

2.9.5.17 Add a Subscriber

Syntax

AQ ADD SUBSCRIBER/ADDSUB -n[ame] subscriber_name
  -queue[_name] queue_name
  [-subscriber_address string]  
  [-subscriber_protocol number] 
  [-rule string]
  [-trans[formation] [schema.]name]
  [-queue_to_queue {NO | YES}] t
  [-mode {PERSISTENT | BUFFERED | PERSISTENT_OR_BUFFERED}]

Parameters

Parameter	Description	Default
`-name,-n`	Name of a producer or consumer of a message.	-
`-queue_name,-queue`	`<name>` Name of a queue.	-
`-subscriber_address,-sa`	<subscriber_address> Protocol-specific address of the recipient. If the protocol is 0, then the address is of the form [schema.]queue[@dblink]. Name of the queue table that will contain the queue.	""
`-subscriber_protocol,-sp`	<subscriber_protocol> Protocol to interpret the address and propagate the message.	0
`-rule,-r`	<rule> A conditional expression based on the message properties, the message data properties and PL/SQL functions. A rule is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. You must prefix each attribute with tab.user_data as a qualifier to indicate the specific column of the queue table that stores the payload.	NULL
`-transformation,-trans`	<transformation> Specifies a transformation that will be applied when this subscriber dequeues the message.	NULL
`-queue_to_queue,-q2q`	`[TRUE\|FALSE]` TRUE: propagation is from queue-to-queue	FALSE
`-mode,-m`	`[1:PERSISTENT\|2:BUFFERED\|3:PERSISTENT_OR_BUFFERED]` Delivery mode of the messages the subscriber is interested in.	PERSISTENT

2.9.5.18 Alter a Subscriber

Syntax

AQ ALTER SUBSCRIBER/ALTERSUB -n[ame] subscriber_name
  [-subscriber_address string] 
  [-subscriber_protocol number]
  -queue[_name] [schema.]queue_name
  [-rule string]
  [-trans[formation] [schema.]name]

Parameters

Parameter	Description	Default
`-name,-n`	Name of a producer or consumer of a message.	-
`-queue_name,-queue`	`<name>` Name of a queue.	-
`-subscriber_address,-sa`	<subscriber_address> Protocol-specific address of the recipient. If the protocol is 0, then the address is of the form [schema.]queue[@dblink]. Name of the queue table that will contain the queue.	""
`-subscriber_protocol,-sp`	<subscriber_protocol> Protocol to interpret the address and propagate the message.	0
`-rule,-r`	<rule> A conditional expression based on the message properties, the message data properties and PL/SQL functions. A rule is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. You must prefix each attribute with tab.user_data as a qualifier to indicate the specific column of the queue table that stores the payload.	NULL
`-transformation,-trans`	<transformation> Specifies a transformation that will be applied when this subscriber dequeues the message.	NULL

2.9.5.19 Remove a Subscriber

Syntax

AQ REMOVE SUBSCRIBER/REMOVESUB -n[ame] subscriber_name
  [-subscriber_address string]  
  [-subscriber_protocol number] 
  -queue[_name] [schema.]queue_name

Parameters

Parameter	Description	Default
`-name,-n`	Name of a producer or consumer of a message.	-
`-queue_name,-queue`	`<name>` Name of a queue.	-
`-subscriber_address,-sa`	<subscriber_address> Protocol-specific address of the recipient. If the protocol is 0, then the address is of the form [schema.]queue[@dblink]. Name of the queue table that will contain the queue.	""
`-subscriber_protocol,-sp`	<subscriber_protocol> Protocol to interpret the address and propagate the message.	0

2.9.5.20 Enqueue

Syntax

AQ ENQ[UEUE] -n[ame] queue_name
  { 
    -pay[load] payload  
    |
    -file[name] name 
  }
  [-type {JSON | HEX | BINARY}]  
                                 
  [-vis[ibility] {ON_COMMIT | IMMEDIATE}]  
  [-trans[formation] [schema.]name]
  [-mode {PERSISTENT | BUFFERED}]  
  [-pri[ority] priority] 
  [-del[ay] delay]  
  [-exp[iration] expiration]  
  [-corr[elation] string]
  [-rec[ipient_list] agents]  
  [-exc[eption_queue] name]
  [-sender[_agent] name] 
  [-out_msgid string]

Parameters

Parameter	Description	Default
`-name,-n`	<name> Name of the queue to which this message should be enqueued.	-
`-payload,-p`	Message payload.	-
`-filename,-file`	File to read the message payload.	-
`-payload_type,-pt`	Payload format type.	JSON
`-visibility,-vis`	Specifies the transactional behavior of the enqueue request. Possible settings are: [2:ON_COMMIT]: The enqueue is part of the current transaction. The operation is complete when the transaction commits. This setting is the default. [1: IMMEDIATE]: The enqueue operation is not part of the current transaction, but an autonomous transaction which commits at the end of the operation. This is the only value allowed when enqueuing to a non-persistent queue.	2:ON_COMMIT
`-mode,-m`	`[1:PERSISTENT\|2:BUFFERED\|3:PERSISTENT_OR_BUFFERED]` Delivery mode of the messages the subscriber is interested in.	PERSISTENT
`-priority,-pri`	Specifies the priority of the message. A smaller number indicates higher priority. The priority can be any number, including negative numbers.	1
`-comment,-c`	<comment> User-specified description of the queue table.	NULL
`-delay,-del`	The delay represents the number of seconds after which a message is available for dequeuing.	0
`-expiration,-exp`	It determines, in seconds, the duration the message is available for dequeuing.	-1 - NEVER
`-correlation,-corr`	Specifies the name of the queue into which the message is moved if it cannot be processed successfully.	-
`-sender_id,-sid`	The application-sender identification specified at enqueue time by the message producer. Sender name is required for secure queues at enqueue time.	-
`-out_message_id,-out_msgid`	System generated identification of the message. This is a globally unique identifier that can be used to identify the message at dequeue time. A bind variable can also be used as an input to store the message id. (Example: -out_msgid :xyz)	-

2.9.5.21 Dequeue

Syntax

AQ DEQ[UEUE] -n[ame] queue_name
  [-cons[umer] name]  
  {
    -pay[load] payload  
    |
    -file[name] name                                
  }
  [-type {JSON | HEX | BINARY}]  
  [-deq[ueue]_mode {REMOVE | BROWSE | LOCKED | REMOVE_NODATA}]  
  [-nav[igation {NEXT_MESSAGE | NEXT_TRANSACTION | FIRST_MESSAGE}]  
  [-vis[ibility] {ON_COMMIT | IMMEDIATE}]   
  [-wait number]   
  [-msgid string]  
  [-corr[elation] string] 
  [-cond[ition] string]  
  [-trans[formation] [schema.]name]
  [-mode {PERSISTENT | BUFFERED | PERSISTENT_OR_BUFFERED}]  
  [-for[mat_enqueue_time] string]  
  [-out_msgid string]  
  [-out_pri[ority] number]  
  [-out_del[ay] number]  
  [-out_exp[iration] number]  
  [-out_corr[elation] string]  
  [-out_attempts number]   
  [-out_exc[eption_queue] name]  
  [-out_enqueue_time string]  
  [-out_state string]  
  [-out_sender_agent name]  
  [-out_original_msgid string]
  [-out_mode string]

Parameters

Parameter	Description	Default
`-name,-n`	<name> Name of the queue to which this message should be dequeued.	-
`-payload,-p`	Message payload to be written on the console.	-
`-filename,-file`	File to write the message payload.	-
`-payload_type,-pt`	Payload format type.	JSON
`-consumer_name,-cons`	Only those messages matching the consumer name are accessed. If a queue is not set up for multiple consumers, then this field should be set to NULL.	-
`-dequeue_mode,-dm`	Specifies the locking behavior associated with the dequeue. Possible settings are: [1: BROWSE] : Read the message without acquiring any lock on the message. This specification is equivalent to a select statement. [2: LOCKED] : Read and obtain a write lock on the message. The lock lasts for the duration of the transaction. This setting is equivalent to a select for update statement. [3: REMOVE - DEFAULT] : Read the message and delete it. [4: REMOVE_NODATA] : Mark the message as updated or deleted. The message can be retained in the queue table based on the retention properties.	-
`-navigation,-nav`	Specifies the position of the message that will be retrieved. Possible settings are: [1: FIRST_MESSAGE:] : Retrieves the first message which is available and matches the search criteria. [2: NEXT_TRANSACTION] : Skip the remainder of the current transaction group (if any) and retrieve the first message of the next transaction group. [3: NEXT_MESSAGE - DEFAULT] : Retrieve the next message that is available and matches the search criteria.	-
`-visibility,-vis`	Specifies the transactional behavior of the enqueue request. Possible settings are: [2:ON_COMMIT]: The enqueue is part of the current transaction. The operation is complete when the transaction commits. This setting is the default. [1: IMMEDIATE]: The enqueue operation is not part of the current transaction, but an autonomous transaction which commits at the end of the operation. This is the only value allowed when enqueuing to a non-persistent queue.	2:ON_COMMIT
`-deq_wait,-deq_w`	Specifies the wait time if there is currently no message available which matches the search criteria. Possible settings are: [-1: FOREVER]: Wait forever. This setting is the default. [0 : NO_WAIT] : Do not wait. [x] : Wait time in seconds. (x seconds)
`-correlation,-corr`	Specifies the name of the queue into which the message is moved if it cannot be processed successfully.	-
`-condition,-con`	A conditional expression based on the message properties, the message data properties, and PL/SQL functions.	-
`-message_id, msg_id`	Specifies the message identifier of the message to be dequeued. A bind variable can also be used as an input (Example: -msgid :xyz).	-
`-transformation,-trans`	<transformation> Specifies a transformation that will be applied after dequeuing the message.	NULL
`-mode,-m`	`[1:PERSISTENT\|2:BUFFERED\|3:PERSISTENT_OR_BUFFERED]` Delivery mode of the messages the subscriber is interested in.	PERSISTENT
`-out_message_id,-out_msgid`	System generated identification of the message. This is a globally unique identifier that can be used to identify the message at dequeue time. A bind variable can also be used as an input to store the message id. (Example: -out_msgid :xyz)	-
`-out_priority,-out_pri`	Priority of the message. A smaller number indicates higher priority. A bind variable can also be used as an input to store the priority. (Example: -out_priority :xyz)	-
`-out_delay,-out_del`	The delay represents the number of seconds after which a message is available for dequeuing. A bind variable can also be used as an input to store the delay. (Example: -out_delay :xyz)	-
`-out_expiration,-out_exp`	The duration the message is available for dequeuing. A bind variable can also be used as an input to store the expiration. (Example: -out_expiration :xyz)	-
`-out_correlation,-out_corr`	Correlation identifier of the message to be dequeued. A bind variable can also be used as an input to store the correlation. (Example: -out_correlation :xyz)	-
`-out_attempts,-out_att`	Number of attempts made to dequeue the message. A bind variable can also be used as an input to store the attempts. (Example: -out_attempts :xyz)	-
`-out_exception_queue,-out_exc`	Name of the queue into which the message is moved if it cannot be processed successfully. A bind variable can also be used as an input to store the exception queue (Example: -out_exception_queue :xyz)	-
`-out_enqueue_time,-out_eqt`	The time the message was enqueued. A bind variable can also be used as an input to store the enqueue time. (Example: -out_enqueue_time :xyz)	-
`-out_state,-out_st`	State of the message at the time of the dequeue. A bind variable can also be used as an input to store the state (Example: -out_state :xyz)	-
`-out_sender_id,-out_sid`	The application-sender identification specified at enqueue time by the message producer. A bind variable can also be used as an input to store the sender id (Example: -out_sender_id :xyz)	-
`-out_mode,-out_m`	The delivery mode of the messages. A bind variable can also be used as an input to store the mode (Example: -out_mode :xyz)	-