CREATE_QUEUE v13

Use the CREATE_QUEUE procedure to create a queue in an existing queue table. The signature is:

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

queue_name

The name of the new queue.

queue_table

The name of the table in which the new queue will reside.

queue_type

The type of the new queue. The valid values for queue_type are:

DBMS_AQADM.NORMAL_QUEUE – This value specifies a normal queue (the default).

DBMS_AQADM.EXCEPTION_QUEUE – This value specifies that the new queue is an exception queue. An exception queue will support only dequeue operations.

max_retries

max_retries specifies the maximum number of attempts to remove a message with a dequeue statement. The value of max_retries is incremented with each ROLLBACK statement. 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 created table is 5.

retry_delay

retry_delay specifies the number of seconds until a message is scheduled for re-processing after a ROLLBACK. Specify 0 to indicate that the message should be retried immediately (the default).

retention_time

retention_time specifies the length of time (in seconds) that a message will be stored after being dequeued. You can also specify 0 (the default) to indicate the message should not be retained after dequeueing, or INFINITE to retain the message forever.

dependency_tracking

This parameter is accepted for compatibility and ignored.

comment

comment specifies a comment associated with the queue.

auto_commit

This parameter is accepted for compatibility and ignored.

Example

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;