This chapter describes the APIs for Oracle Workflow Advanced Queues processing. The APIs include PL/SQL functions and procedures to handle workflow Advanced Queues processing. Although these APIs will continue to be supported for backward compatibility, customers using Oracle Workflow Release 12 should use the Business Event System rather than the queue APIs to integrate with Oracle Advanced Queuing.
This chapter covers the following topics:
Oracle Workflow queue APIs can be called by an application program or a workflow function in the runtime phase to handle workflow Advanced Queues processing.
Note: Although these APIs will continue to be supported for backward compatibility, customers using Oracle Workflow Release 12 should use the Business Event System rather than the queue APIs to integrate with Oracle Advanced Queuing.
In Oracle Workflow, an 'outbound' and an 'inbound' queue are established. A package of data on the queue is referred to as an event or a message.
Note: An event in this context is different from the business events associated with the Business Event System, and a message in this context is different from the messages associated with notification activities.
Events are enqueued in the outbound queue for agents to consume and process. These agents may be any application that is external to the database. Similarly an agent may enqueue some message to the inbound queue for the Workflow Engine to consume and process. The outbound and inbound queues facilitate the integration of external activities into your workflow processes.
Note: Background engines use a separate 'deferred' queue.
All Oracle Workflow queue APIs are defined in a PL/SQL package called WF_QUEUE. You must execute these queue APIs from the same Oracle Workflow account since the APIs are account dependent.
Important: In using these APIs, we assume that you have prior knowledge of Oracle Advanced Queuing concepts and terminology. Refer to the Oracle Streams Advanced Queuing User's Guide and Reference for more information on Advanced Queues.
The following APIs are for developers who wish to write to the inbound queue by creating messages in the internal stack rather than using WF_QUEUE.EnqueueInbound(). The internal stack is purely a storage area and you must eventually write each message that you create on the stack to the inbound queue.
Note: For efficient performance, you should periodically write to the inbound queue to prevent the stack from growing too large.
Oracle Workflow queues use the datatype system.wf_payload_t to define the payload for any given message. The payload contains all the information that is required about the event. The following table lists the attributes of system.wf_payload_t.
| Attribute Name | Datatype | Description |
|---|---|---|
| ITEMTYPE | VARCHAR2(8) | The item type of the event. |
| ITEMKEY | VARCHAR2(240) | The item key of the event. |
| ACTID | NUMBER | The function activity instance ID. |
| FUNCTION_NAME | VARCHAR2(200) | The name of the function to execute. |
| PARAM_LIST | VARCHAR2(4000) | A list of "value_name=value" pairs. In the inbound scenario, the pairs are passed as item attributes and item attribute values. In the outbound scenario, the pairs are passed as all the attributes and attribute values of the function (activity attributes). |
| RESULT | VARCHAR2(30) | An optional activity completion result. Possible values are determined by the function activity's Result Type or can be an engine standard result. |
Related Topics
Standard API for PL/SQL Procedures Called by Function Activities, Oracle Workflow Developer's Guide
procedure EnqueueInbound (itemtype in varchar2, itemkey in varchar2, actid in number, result in varchar2 default null, attrlist in varchar2 default null, correlation in varchar2 default null, error_stack in varchar2 default null);
Enqueues the result from an outbound event onto the inbound queue. An outbound event is defined by an outbound queue message that is consumed by some agent.
Oracle Workflow marks the external function activity as complete with the specified result when it processes the inbound queue. The result value is only effective for successful completion, however. If you specify an external program error in the error_stack parameter, Oracle Workflow marks the external function activity as complete with an ERROR status, overriding the result value. Additionally, if a corresponding error process is defined in the item type, Oracle Workflow launches that error process.
| Variable | Description |
|---|---|
| itemtype | The item type of the event. |
| itemkey | The item key of the event. An item key is a string generated from the application object's primary key. The string uniquely identifies the item within an item type. The item type and key together identify the process instance. |
| actid | The function activity instance ID that this event is associated with. |
| result | An optional activity completion result. Possible values are determined by the function activity's Result Type. |
| attrlist | A longlist of "value name=value" pairs that you want to pass back as item attributes and item attribute values. Each pair must be delimited by the caret character (^), as in the example, "ATTR1=A^ATTR2=B^ATTR3=C". If a specified value name does not exist as an item attribute, Oracle Workflow creates the item attribute for you, of type varchar2. |
| correlation | Specify an optional correlation identifier for the message to be enqueued. Oracle Advanced Queues allow you to search a queue for messages based on a specific correlation value. If null, the Workflow Engine creates a correlation identifier based on the Workflow schema name and the item type. |
| error_stack | Specify an optional external program error that will be placed on Oracle Workflow's internal error stack. You can specify any text value up to a maximum length of 200 characters. |
procedure DequeueOutbound (dequeuemode in number, navigation in number default 1, correlation in varchar2 default null, itemtype in varchar2 default null, payload out system.wf_payload_t, message_handle in out raw, timeout out boolean);
Dequeues a message from the outbound queue for some agent to consume.
Important: If you call this procedure within a loop, you must remember to set the returned message handle to null, otherwise, the procedure dequeues the same message again. This may not be the behavior you want and may cause an infinite loop.
| Variable | Description |
|---|---|
| dequeuemode | A value of DBMS_AQ.BROWSE, DBMS_AQ.LOCKED, or DBMS_AQ.REMOVE, corresponding to the numbers 1, 2 and 3 respectively, to represent the locking behavior of the dequeue. A mode of DBMS_AQ.BROWSE means to read the message from the queue without acquiring a lock on the message. A mode of DBMS_AQ.LOCKED means to read and obtain a write lock on the message, where the lock lasts for the duration of the transaction. A mode of DBMS_AQ.REMOVE means read the message and delete it. |
| navigation | Specify DBMS_AQ.FIRST_MESSAGE or DBMS_AQ.NEXT_MESSAGE, corresponding to the number 1 or 2 respectively, to indicate the position of the message that will be retrieved. A value of DBMS_AQ.FIRST_MESSAGE retrieves the first message that is available and matches the correlation criteria. The first message is inherently the beginning of the queue. A value of DBMS_AQ.NEXT_MESSAGE retrieves the next message that is available and matches the correlation criteria, and lets you read through the queue. The default is 1. |
| correlation | Specify an optional correlation identifier for the message to be dequeued. Oracle Advanced Queues allow you to search a queue for messages based on a specific correlation value. You can use the Like comparison operator, '%', to specify the identifier string. If null, the Workflow Engine creates a correlation identifier based on the Workflow schema name and the item type. |
| itemtype | The item type of the event. |
| message_handle | Specify an optional message handle ID for the specific event to be dequeued. If you specify a message handle ID, the correlation identifier is ignored. |
Important: The timeout output returns TRUE when there is nothing further to read in the queue.
Following is an example of code that loops through the outbound queue and displays the output.
declare
event system.wf_payload_t;
i number;
msg_id raw(16);
queuename varchar2(30);
navigation_mode number;
end_of_queue boolean;
begin
queuename:=wf_queue.OUTBOUNDQUEUE;
i:=0;
LOOP
i:=i+1;
-- always start with the first message then progress to next
if i = 1 then
navigation_mode := dbms_aq.FIRST_MESSAGE;
else
navigation_mode := dbms_aq.NEXT_MESSAGE;
end if;
-- not interested in specific msg_id. Leave it null so
-- as to loop through all messages in queue
msg_id :=null;
wf_queue.DequeueOutbound(
dequeuemode => dbms_aq.BROWSE,
payload => event,
navigation => navigation_mode,
message_handle => msg_id,
timeout => end_of_queue);
if end_of_queue then
exit;
end if;
-- print the correlation itemtype:itemKey
dbms_output.put_line('Msg '||to_char(i)||' = '||
event.itemtype||':'||event.itemkey||' '||
event.actid||' '||event.param_list);
END LOOP;
end;
/
procedure DequeueEventDetail (dequeuemode in number, navigation in number default 1, correlation in varchar2 default null, itemtype in out varchar2, itemkey out varchar2, actid out number, function_name out varchar2, param_list out varchar2, message_handle in out raw, timeout out boolean);
Dequeue from the outbound queue, the full event details for a given message. This API is similar to DequeueOutbound except it does not reference the payload type. Instead, it outputs itemkey, actid, function_name, and param_list, which are part of the payload.
Important: If you call this procedure within a loop, you must remember to set the returned message handle to null, otherwise, the procedure dequeues the same message again. This may not be the behavior you want and may cause an infinite loop.
| Variable | Description |
|---|---|
| dequeuemode | A value of DBMS_AQ.BROWSE, DBMS_AQ.LOCKED, or DBMS_AQ.REMOVE, corresponding to the numbers 1, 2 and 3 respectively, to represent the locking behavior of the dequeue. A mode of DBMS_AQ.BROWSE means to read the message from the queue without acquiring a lock on the message. A mode of DBMS_AQ.LOCKED means to read and obtain a write lock on the message, where the lock lasts for the duration of the transaction. A mode of DBMS_AQ.REMOVE means read the message and update or delete it. |
| navigation | Specify DBMS_AQ.FIRSTMESSAGE or DBMS_AQ.NEXTMESSAGE, corresponding to the number 1 or 2 respectively, to indicate the position of the message that will be retrieved. A value of DBMS_AQ.FIRSTMESSAGE retrieves the first message that is available and matches the correlation criteria. It also resets the position to the beginning of the queue. A value of DBMS_AQ.NEXTMESSAGE retrieves the next message that is available and matches the correlation criteria. The default is 1. |
| correlation | Specify an optional correlation identifier for the message to be dequeued. Oracle Advanced Queues allow you to search a queue for messages based on a specific correlation value. You can use the Like comparison operator, '%', to specify the identifier string. If null, the Workflow Engine creates a correlation identifier based on the Workflow schema name and the item type. |
| acctname | The Oracle Workflow database account name. If acctname is null, it defaults to the pseudocolumn USER. |
| itemtype | Specify an optional item type for the message to dequeue if you are not specifying a correlation. |
| message_handle | Specify an optional message handle ID for the specific event to be dequeued. If you specify a message handle ID, the correlation identifier is ignored. |
Important: The timeout output returns TRUE when there is nothing further to read in the queue.
procedure PurgeEvent (queuename in varchar2, message_handle in raw);
Removes an event from a specified queue without further processing.
| Variable | Description |
|---|---|
| queuename | The name of the queue from which to purge the event. |
| message_handle | The message handle ID for the specific event to purge. |
procedure PurgeItemType (queuename in varchar2, itemtype in varchar2 default null, correlation in varchar2 default null);
Removes all events belonging to a specific item type from a specified queue without further processing.
| Variable | Description |
|---|---|
| queuename | The name of the queue from which to purge the events. |
| itemtype | An optional item type of the events to purge. |
| correlation | Specify an optional correlation identifier for the message to be purged. Oracle Advanced Queues allow you to search a queue for messages based on a specific correlation value. You can use the Like comparison operator, '%', to specify the identifier string. If null, the Workflow Engine creates a correlation identifier based on the Workflow schema name and the item type. |
procedure ProcessInboundQueue (itemtype in varchar2 default null, correlation in varchar2 default null);
Reads every message off the inbound queue and records each message as a completed event. The result of the completed event and the list of item attributes that are updated as a consequence of the completed event are specified by each message in the inbound queue. See: EnqueueInbound.
| Variable | Description |
|---|---|
| itemtype | An optional item type of the events to process. |
| correlation | If you wish to process only messages with a specific correlation, enter a correlation identifier. If correlation is null, the Workflow Engine creates a correlation identifier based on the Workflow schema name and the item type. |
function GetMessageHandle (queuename in varchar2, itemtype in varchar2, itemkey in varchar2, actid in number, correlation in varchar2 default null) return raw;
Returns a message handle ID for a specified message.
| Variable | Description |
|---|---|
| queuename | The name of the queue from which to retrieve the message handle. |
| itemtype | The item type of the message. |
| itemkey | The item key of the message. An item key is a string generated from the application object's primary key. The string uniquely identifies the item within an item type. The item type and key together identify the process instance. |
| actid | The function activity instance ID that this message is associated with. |
| correlation | Specify an optional correlation identifier for the message. If the correlation is null, the Workflow Engine creates a correlation identifier based on the Workflow schema name and the item type. |
procedure DequeueException (queuename in varchar2);
Dequeues all messages from an exception queue and places the messages on the standard Business Event System WF_ERROR queue with the error message 'Message Expired'. When the messages are dequeued from WF_ERROR, a predefined subscription is triggered that launches the Default Event Error process.
| Variable | Description |
|---|---|
| queuename | The name of the exception queue that has been enabled for dequeue. |
Related Topics
Default Event Error Process, Oracle Workflow Developer's Guide
function DeferredQueue return varchar2;
Returns the name of the queue and schema used by the background engine for deferred processing.
function InboundQueue return varchar2;
Returns the name of the inbound queue and schema. The inbound queue contains messages for the Workflow Engine to consume.
function OutboundQueue return varchar2;
Returns the name of the outbound queue and schema. The outbound queue contains messages for external agents to consume.
procedure ClearMsgStack;
Clears the internal stack. See: Developer APIs for the Inbound Queue.
procedure CreateMsg (itemtype in varchar2, itemkey in varchar2, actid in number);
Creates a new message in the internal stack if it doesn't already exist. See: Developer APIs for the Inbound Queue.
| Variable | Description |
|---|---|
| itemtype | The item type of the message. |
| itemkey | The item key of the message. An item key is a string generated from the application object's primary key. The string uniquely identifies the item within an item type. The item type and key together identify the process instance. |
| actid | The function activity instance ID that this message is associated with. |
procedure WriteMsg (itemtype in varchar2, itemkey in varchar2, actid in number);
Writes a message from the internal stack to the inbound queue. See: Developer APIs for the Inbound Queue.
| Variable | Description |
|---|---|
| itemtype | The item type of the message. |
| itemkey | The item key of the message. An item key is a string generated from the application object's primary key. The string uniquely identifies the item within an item type. The item type and key together identify the process. |
| actid | The function activity instance ID that this message is associated with. |
procedure SetMsgAttr (itemtype in varchar2, itemkey in varchar2, actid in number, attrName in varchar2, attrValue in varchar2);
Appends an item attribute to the message in the internal stack. See: Developer APIs for the Inbound Queue.
| Variable | Description |
|---|---|
| itemtype | The item type of the message. |
| itemkey | The item key of the message. An item key is a string generated from the application object's primary key. The string uniquely identifies the item within an item type. The item type and key together identify the process instance. |
| actid | The function activity instance ID that this message is associated with. |
| attrName | The internal name of the item attribute you wish to append to the message. |
| attrValue | The value of the item attribute you wish to append. |
procedure SetMsgResult (itemtype in varchar2, itemkey in varchar2, actid in number, result in varchar2);
Sets a result to the message written in the internal stack. See: Developer APIs for the Inbound Queue.
| Variable | Description |
|---|---|
| itemtype | The item type of the message. |
| itemkey | The item key of the message. An item key is a string generated from the application object's primary key. The string uniquely identifies the item within an item type. The item type and key together identify the process instance. |
| actid | The function activity instance ID that this message is associated with. |
| result | The completion result for the message. Possible values are determined by the activity's Result Type. |