DBMS_AQ

ENQUEUE

You can use the ENQUEUE procedure to add an entry to a queue. The procedure has the following signature:

ENQUEUE(
  queue_name IN VARCHAR2,
  enqueue_options IN DBMS_AQ.ENQUEUE_OPTIONS_T,
  message_properties IN DBMS_AQ.MESSAGE_PROPERTIES_T,
  payload IN <type_name>,
  msgid OUT RAW)

Parameters

• queue_name

  The name of an existing queue. This may be a schema-qualified name. If you omit the schema name, the server uses the schema specified by SEARCH_PATH. Different from native Oracle, unquoted identifiers are converted to be lowercase before the identifiers are stored. To include special characters or use a case-sensitive name, enclose the name in double quotation marks.

• enqueue_options

  The enqueue_options parameter is a parameter of the enqueue_options_t type. The following example shows the structure of enqueue_options_t:

  DBMS_AQ.ENQUEUE_OPTIONS_T IS RECORD(
    visibility BINARY_INTEGER DEFAULT ON_COMMIT,
    relative_msgid RAW(16) DEFAULT NULL,
    sequence_deviation BINARY INTEGER DEFAULT NULL,
    transformation VARCHAR2(61) DEFAULT NULL,
    delivery_mode PLS_INTEGER NOT NULL DEFAULT PERSISTENT);

  The following table lists the only parameter values supported by enqueue_options_t.

  visibility	ON_COMMIT
  delivery_mode	PERSISTENT
  sequence_deviation	NULL
  transformation	NULL
  relative_msgid	NULL

• message_properties

  The message_properties parameter is a parameter of the message_properties_t type. The following example shows the structure of message_properties_t:

        message_properties_t IS RECORD(
      priority INTEGER,
      delay INTEGER,
      expiration INTEGER,
      correlation CHARACTER VARYING(128) COLLATE pg_catalog." C",
      attempts INTEGER,
      recipient_list"AQ$_RECIPIENT_LIST_T",
      exception_queue CHARACTER VARYING(61) COLLATE pg_catalog." C",
      enqueue_time TIMESTAMP WITHOUT TIME ZONE,
        state INTEGER,
       original_msgid BYTEA,
        transaction_group CHARACTER VARYING(30) COLLATE pg_catalog." C",
        delivery_mode INTEGER
      DBMS_AQ.PERSISTENT);

  The following table lists the values supported by message_properties_t.

  Parameter	Description
  priority	If the queue table definition includes sort_list that references priority, this parameter affects the order in which messages are dequeued. A lower value specifies a higher dequeuing priority.
  delay	The number of seconds elapsed before a message is available for dequeuing. The NO_DELAY constant specifies that a message is dequeued immediately after the message is available.
  expiration	The number of seconds elapsed before a message expires.
  correlation	The message associated with the entry. The default value is NULL.
  attempts	The number of attempts to dequeue the message. This parameter is maintained by the system.
  recipient_list	This parameter is not supported.
  exception_queue	The name of an exception queue to which a message is moved if the message expires or is dequeued by a transaction that rolls back excessive times.
  enqueue_time	The time when the entry was added to the queue. This value is provided by the system.
  state	This parameter is maintained by DBMS_AQ. Valid values: DBMS_AQ.READY: The delay has not been reached. DBMS_AQ.WAITING: The queue entry is ready for processing. DBMS_AQ.PROCESSED: The queue entry has been processed. DBMS_AQ.EXPIRED: The queue entry has been moved to the exception queue.
  original_msgid	This parameter is ignored, but is included for compatibility.
  transaction_group	This parameter is ignored, but is included for compatibility.
  delivery_mode	This parameter is not supported. Specify a value of DBMS_AQ.PERSISTENT.

• payload

  You can use the payload parameter to provide the data associated with the queue entry. The payload type must match the type specified when you create the corresponding queue table. For more information, see DBMS_AQADM.CREATE_QUEUE_TABLE.

• msgid

  You can use the msgid parameter to retrieve a unique message identifier generated by the system.

Examples

The following anonymous block calls DBMS_AQ.ENQUEUE to add a message to a queue named work_order:

DECLARE

  enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_handle     raw(16);
  payload            work_order;

BEGIN

  payload := work_order('Smith', 'system upgrade');

DBMS_AQ.ENQUEUE(
  queue_name         => 'work_order',
  enqueue_options    => enqueue_options,
  message_properties => message_properties,
  payload            => payload,
  msgid              => message_handle
    );
 END;

DEQUEUE

You can use the DEQUEUE procedure to dequeue a message. The procedure has the following signature:

DEQUEUE(
  queue_name IN VARCHAR2,
  dequeue_options IN DBMS_AQ.DEQUEUE_OPTIONS_T,
  message_properties OUT DBMS_AQ.MESSAGE_PROPERTIES_T,
  payload OUT type_name,
  msgid OUT RAW)

Parameters

• queue_name

  The name of an existing queue. This may be a schema-qualified name. If you omit the schema name, the server uses the schema specified by SEARCH_PATH. Different from native Oracle, unquoted identifiers are converted to be lowercase before the identifiers are stored. To include special characters or use a case-sensitive name, enclose the name in double quotation marks.

• dequeue_options

  The dequeue _options parameter is a parameter of the dequeue_options_t type. The following example shows the structure of dequeue_options_t:

  DEQUEUE_OPTIONS_T IS RECORD(
    consumer_name CHARACTER VARYING(30),
    dequeue_mode INTEGER,
    navigation INTEGER,
    visibility INTEGER,
    wait INTEGER,
    msgid BYTEA,
    correlation CHARACTER VARYING(128),
    deq_condition CHARACTER VARYING(4000),
    transformation CHARACTER VARYING(61),
    delivery_mode INTEGER);

  The following table lists the only parameter values supported by dequeue_options_t.

  Parameter	Description
  consumer_name	Must be NULL.
  dequeue_mode	The locking behavior of the dequeuing operation. Valid values: DBMS_AQ.BROWSE: reads a message without obtaining a lock. DBMS_AQ.LOCKED: reads a message after acquiring a lock. DBMS_AQ.REMOVE: reads a message before deleting the message. DBMS_AQ.REMOVE_NODATA: reads a message but does not delete the message.
  navigation	Specifies the message to be retrieved. Valid values: FIRST_MESSAGE: the first message within the queue that matches the search criteria. NEXT_MESSAGE: the next available message that matches the first term.
  visibility	Must be ON_COMMIT: If you roll back the current transaction, the dequeued item remains in the queue.
  wait	Must be a number larger than 0, or be set to: DBMS_AQ.FOREVER: waits indefinitely. DBMS_AQ.NO_WAIT: does not wait.
  msgid	The ID of the message to be dequeued.
  correlation	This parameter is ignored, but is included for compatibility.
  deq_condition	A VARCHAR2 expression that calculates a BOOLEAN value and specifies whether a message must be dequeued.
  transformation	This parameter is ignored, but is included for compatibility.
  delivery_mode	Must be PERSISTENT. Buffered messages are not supported in this mode.

• message_properties

  The message_properties parameter is a parameter of the message_properties_t type. The following example shows the structure of message_properties_t:

      message_properties_t IS RECORD(
      priority INTEGER,
      delay INTEGER,
      expiration INTEGER,
      correlation CHARACTER VARYING(128) COLLATE pg_catalog." C",
      attempts INTEGER,
      recipient_list"AQ$_RECIPIENT_LIST_T",
      exception_queue CHARACTER VARYING(61) COLLATE pg_catalog." C",
      enqueue_time TIMESTAMP WITHOUT TIME ZONE,
      state INTEGER,
      original_msgid BYTEA,
      transaction_group CHARACTER VARYING(30) COLLATE pg_catalog." C",
      delivery_mode INTEGER
    DBMS_AQ.PERSISTENT);

  The following table lists the parameters supported by message_properties_t:

  Parameter	Description
  priority	If the queue table definition includes sort_list that references priority, this parameter affects the order in which messages are dequeued. A lower value specifies a higher dequeuing priority.
  delay	The number of seconds elapsed before a message is available for dequeuing. The NO_DELAY constant specifies that a message is dequeued immediately after the message is available.
  expiration	The number of seconds elapsed before a message expires.
  correlation	The message associated with the entry. The default value is NULL.
  attempts	The number of attempts to dequeue the message. This parameter is maintained by the system.
  recipient_list	This parameter is not supported.
  exception_queue	The name of an exception queue to which a message is moved if the message expires or is dequeued by a transaction that rolls back excessive times.
  enqueue_time	The time when the entry was added to the queue. This value is provided by the system.
  state	This parameter is maintained by DBMS_AQ. Valid values: DBMS_AQ.WAITING: The delay has not been reached. DBMS_AQ.READY: The queue entry is ready for processing. DBMS_AQ.PROCESSED: The queue entry has been processed. DBMS_AQ.EXPIRED: The queue entry has been moved to the exception queue.
  original_msgid	This parameter is ignored, but is included for compatibility.
  transaction_group	This parameter is ignored, but is included for compatibility.
  delivery_mode	This parameter is not supported. Specify a value of DBMS_AQ.PERSISTENT.

• payload

  You can use the payload parameter to retrieve the payload of a message that is involved in a dequeuing operation. The payload type must match the type specified when you create the queue table.

• msgid

  You can use the msgid parameter to retrieve a unique message identifier.

Examples

The following anonymous block calls DBMS_AQ.DEQUEUE to retrieve a message from the queue and payload:

DECLARE

  dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_handle     raw(16);
  payload            work_order;

BEGIN
  dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;

  DBMS_AQ.DEQUEUE(
    queue_name         => 'work_queue',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => payload,
    msgid              => message_handle
  );

  DBMS_OUTPUT.PUT_LINE(
  'The next work order is [' || payload.subject || '].'
  );
END;

The payload is displayed by DBMS_OUTPUT.PUT_LINE.

REGISTER

You can use the REGISTER procedure to register an email address, procedure, or URL used for notification when an item is enqueued or dequeued. The procedure has the following signature:

REGISTER(
  reg_list IN SYS.AQ$_REG_INFO_LIST,
  count IN NUMBER)

Parameters

• reg_list

  The reg_list parameter specifies a list of the AQ$_REG_INFO_LIST type. This list provides information about each subscription that you want to register. Each entry within the list is of the AQ$_REG_INFO type. The following table lists the attributes included in each entry.

  Attribute	Type	Description
  name	VARCHAR2 (128)	The name of a subscription. This may be a schema-qualified name.
  namespace	NUMERIC	The only supported value is DBMS_AQ.NAMESPACE_AQ (0).
  callback	VARCHAR2 (4000)	Describes the action to be performed upon notification. Only PL/SQL procedures are supported. The procedures are called in this format: plsql://schema.procedure, where: The schema field specifies the schema where the procedure is located. The procedure field specifies the name of the procedure to be notified.
  context	RAW (16)	Any user-defined value required by the callback procedure.

• count

  The count parameter specifies the number of entries in reg_list.

Examples

The following anonymous block calls DBMS_AQ.REGISTER to register procedures that are notified when an item is added to or removed from a queue. A set of attributes of the sys.aq$_reg_info type is provided for each subscription identified in the DECLARE section:

DECLARE
   subscription1 sys.aq$_reg_info;
   subscription2 sys.aq$_reg_info;
   subscription3 sys.aq$_reg_info;
   subscriptionlist sys.aq$_reg_info_list;
BEGIN
   subscription1 := sys.aq$_reg_info('q', DBMS_AQ.NAMESPACE_AQ, 'plsql://assign_worker? PR=0',HEXTORAW('FFFF'));
   subscription2 := sys.aq$_reg_info('q', DBMS_AQ.NAMESPACE_AQ, 'plsql://add_to_history? PR=1',HEXTORAW('FFFF'));
   subscription3 := sys.aq$_reg_info('q', DBMS_AQ.NAMESPACE_AQ, 'plsql://reserve_parts? PR=2',HEXTORAW('FFFF'));

   subscriptionlist := sys.aq$_reg_info_list(subscription1, subscription2, subscription3);
   dbms_aq.register(subscriptionlist, 3);
   commit;
  END;
   /

The subscriptionlist parameter is a parameter of the sys.aq$_reg_info_list type, and contains the sys.aq$_reg_info objects described in this example. The list name and object count are passed to dbms_aq.register.

UNREGISTER

You can use the UNREGISTER procedure to disable notifications related to enqueuing and dequeuing. The procedure has the following signature:

UNREGISTER(
  reg_list IN SYS.AQ$_REG_INFO_LIST,
  count
IN NUMBER)

Parameters

• reg_list

  The reg_list parameter specifies a list of the AQ$_REG_INFO_LIST type, and provides the information about each subscription that you want to register. Each entry within the list is of the AQ$_REG_INFO type. The following table lists the attributes included in each entry.

  Attribute	Type	Description
  name	VARCHAR2 (128)	The name of a subscription. This may be a schema-qualified name.
  namespace	NUMERIC	The only supported value is DBMS_AQ.NAMESPACE_AQ (0).
  callback	VARCHAR2 (4000)	Describes the action to be performed upon notification. Only PL/SQL procedures are supported. The procedures are called in this format: plsql://schema.procedure, where: The schema field specifies the schema where the procedure is located. The procedure field specifies the name of the procedure to be notified.
  context	RAW (16)	Any user-defined value required by the procedure.

• count

  The count parameter specifies the number of entries in reg_list.

Examples

The following anonymous block calls DBMS_AQ.UNREGISTER to disable the notifications specified in the example for DBMS_AQ.REGISTER:

DECLARE
   subscription1 sys.aq$_reg_info;
   subscription2 sys.aq$_reg_info;
   subscription3 sys.aq$_reg_info;
   subscriptionlist sys.aq$_reg_info_list;
BEGIN
   subscription1 := sys.aq$_reg_info('q', DBMS_AQ.NAMESPACE_AQ, 'plsql://assign_worker? PR=0',HEXTORAW('FFFF'));
   subscription2 := sys.aq$_reg_info('q', DBMS_AQ.NAMESPACE_AQ, 'plsql://add_to_history? PR=1',HEXTORAW('FFFF'));
   subscription3 := sys.aq$_reg_info('q', DBMS_AQ.NAMESPACE_AQ, 'plsql://reserve_parts? PR=2',HEXTORAW('FFFF'));

   subscriptionlist := sys.aq$_reg_info_list(subscription1, subscription2, subscription3);
   dbms_aq.unregister(subscriptionlist, 3);
   commit;
  END;
   /

The subscriptionlist parameter is a parameter of the sys.aq$_reg_info_list type, and contains the sys.aq$_reg_info objects described in this example. The list name and object count are passed to dbms_aq.unregister.