|Lesson 2||Overview of distributed database enhancements|
|Objective||List primary Oracle enhancements for distributed databases.|
DBMS_JOBpackage, Oracle has provided a very important tool called a queue. By using queues, multiple tasks can be serialized and executed in proper order, much like any input queue for online transactions. Oracle has enhanced the idea of queues within Oracle to allow the propagation of queue information to remote databases.
TYPE DEQUEUE_OPTIONS_T IS RECORD ( consumer_name VARCHAR2(30) DEFAULT NULL, dequeue_mode BINARY_INTEGER DEFAULT REMOVE, navigation BINARY_INTEGER DEFAULT NEXT_MESSAGE, visibility BINARY_INTEGER DEFAULT ON_COMMIT, wait BINARY_INTEGER DEFAULT FOREVER, msgid RAW(16) DEFAULT NULL, correlation VARCHAR2(128) DEFAULT NULL, deq_condition VARCHAR2(4000) DEFAULT NULL, signature aq$_sig_prop DEFAULT NULL, transformation VARCHAR2(61) DEFAULT NULL, delivery_mode PLS_INTEGER DEFAULT PERSISTENT);
|consumer_name||Name of the consumer. 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.For secure queues, consumer_name must be a valid AQ agent name, mapped to the database user performing the dequeue operation, through dbms_aqadm.enable_db_access procedure call.|
|dequeue_mode||Specifies the locking behavior associated with the dequeue. Possible settings are:
BROWSE: Read the message without acquiring any lock on the message. This specification is equivalent to a select statement.
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.
REMOVE: Read the message and delete it. This setting is the default. The message can be retained in the queue table based on the retention properties.
REMOVE_NODATA: Mark the message as updated or deleted. The message can be retained in the queue table based on the retention properties.
|navigation||Specifies the position of the message that will be retrieved. First, the position is determined. Second, the search criterion is applied. Finally, the message is retrieved. Possible settings are:
NEXT_MESSAGE: Retrieve the next message that is available and matches the search criteria. If the previous message belongs to a message group, then AQ retrieves the next available message that matches the search criteria and belongs to the message group. This setting is the default.
NEXT_TRANSACTION: Skip the remainder of the current transaction group (if any) and retrieve the first message of the next transaction group. This setting can only be used if message grouping is enabled for the current queue.
FIRST_MESSAGE: Retrieves the first message which is available and matches the search criteria. This setting resets the position to the beginning of the queue.
FIRST_MESSAGE_MULTI_GROUP: indicates that a call to DBMS_AQ.DEQUEUE_ARRAY will reset the position to the beginning of the queue and dequeue messages (possibly across different transaction groups) that are available and match the search criteria, until reaching the ARRAY_SIZE limit. Refer to the TRANSACTION_GROUP attribute for the message to distinguish between transaction groups.
NEXT_MESSAGE_MULTI_GROUP: indicates that a call to DBMS_AQ.DEQUEUE_ARRAY will dequeue the next set of messages (possibly across different transaction groups) that are available and match the search criteria, until reaching the ARRAY_SIZE limit. Refer to the TRANSACTION_GROUP attribute for the message to distinguish between transaction groups.
|visibility||Specifies whether the new message is dequeued as part of the current transaction.The visibility parameter is ignored when using the BROWSE dequeue mode. Possible settings are:
ON_COMMIT: The dequeue will be part of the current transaction. This setting is the default.
IMMEDIATE: The dequeue operation is not part of the current transaction, but an autonomous transaction which commits at the end of the operation
|wait||Specifies the wait time if there is currently no message available which matches the search criteria. Possible settings are:
FOREVER: Wait forever. This setting is the default.
NO_WAIT: Do not wait. number: Wait time in seconds.
|msgid||Specifies the message identifier of the message to be dequeued.|
|correlation||Specifies the correlation identifier of the message to be dequeued. Special pattern matching characters, such as the percent sign (%) and the underscore (_) can be used. If more than one message satisfies the pattern, then the order of dequeuing is undetermined.|
|deq_condition||A conditional expression based on the message properties, the message data properties, and PL/SQL functions. A deq_condition is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. This Boolean expression can include conditions on message properties, user data properties (object payloads only), and PL/SQL or SQL functions (as specified in the WHERE clause of a SQL query). Message properties include priority, corrid and other columns in the queue table To specify dequeue conditions on a message payload (object payload), use attributes of the object type in clauses. 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. The deq_condition parameter cannot exceed 4000 characters. If more than one message satisfies the dequeue condition, then the order of dequeuing is undetermined.|
|signature||Currently not implemented|
|transformation||Specifies a transformation that will be applied after dequeuing the message. The source type of the transformation must match the type of the queue.|
|delivery_mode||The dequeuer specifies the delivery mode of the messages it wishes to dequeue in the dequeue options. It can be BUFFERED or PERSISTENT or PERSISTENT_OR_BUFFERED. The message properties of the dequeued message indicate the delivery mode of the dequeued message. Array dequeue is only supported for buffered messages with an array size of '1'.|