A reader pointed out that I didn’t fulfill my promise to write about suspend, resume and abort in part 4 of ‘Oracle workflow for eBS DBA’s.
So to make up for that omission, I will first write a separate note about it here. Then I’ll incorporate it in the article at a later time.
Let’s start with the abort.
In part 3, we saw that we can get workflows in a state where they will never be able to continue again. We did this by setting the ‘On Revisit’ property to ignore.
The correct way to handle these workflows is to run a workflow background engine with parameter ‘process_stuck’ set to ‘TRUE’. That will set the item status to ‘Error’ and run the appropriate error process.
But there may be reasons where you want to just abort the item, without error processing.
For those situations Oracle provides the ‘wf_engine.abortprocess’ API.
The API will set the status of the process to complete. If a result is needed, you can set this on the call to the API. It defaults to ‘#FORCE’ (wf_engine.eng_force constant).
Let’s see how this works. First I used the ‘MAIN_DBA_PROCESS’ from part 3 of the series, and I set the ‘On Revisit’ for the ‘LOOP_COUNTER’ to ‘Ignore’.
begin
wf_engine.abortprocess(itemtype=>'DBA_TYPE'
,itemkey=>'30'
,process=>'ROOT:DBA_MAIN_PROCESS');
end;
Note how we have to indicate that we want to abort the root process of the DBA_MAIN_PROCESS. The workflow engine needs to know unambiguously which process to abort. The way to do that is to set ‘process:<activity>’ to indicate the process. In our case this would be ‘ROOT:DBA_MAIN_PROCESS’.
Compare this with the result from running a background engine with parameter ‘process_stuck=>TRUE’:
ORA-20002: 3150: Process 'DBA_TYPE/33' is being worked upon. Please retry the current request on the process later.
ORA-06512: at "APPS.WF_CORE", line 300
ORA-06512: at "APPS.WF_ENGINE", line 4528
ORA-06512: at line 2
Of course this error can be captured and handled as we saw in ‘Oracle workflow for eBS DBA’s (Part 4)’
Then lets take a look at the ‘wf_engine.Suspend’ function. This is basically a ‘pause’-API for a workflow item. It sets the active process to ‘SUSPEND’.
The workflow engine will not pick it up any more until the wf_engine.resume API is called.
Let’s see the resume and suspend with a small example. I used the same dba_control_process. After launching it, it will be deferred. Instead of running a background engine, we suspend it.
begin
wf_engine.suspend(itemtype=>'DBA_TYPE',itemkey=>'34');
end;
And the status becomes:
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT OUTBOUND_QUEUE_ID
DBA_CONTROL_PROCESS 31-7-2009 18:30:45 SUSPEND #NULL
START START 31-7-2009 18:30:45 31-7-2009 18:30:45 COMPLETE #NULL
DEFER WF_STANDARD.DEFER 31-7-2009 18:31:02 DEFERRED #NULL 6FFEFFF31C2604F5E0440003BAB3AD6B
The interesting thing is that the deferred status is still there. Including its queue_id. So when we run a background engine. We’ll see that it indeed picks up the item. It dequeues the message but leaves the status on deferred.
Now when we resume the process:
begin
wf_engine.resume(itemtype=>'DBA_TYPE',itemkey=>'34');
end;
The function is performed, and the item continues as usual.
For many Oracle eBS-DBA’s workflow is a strange and hardly understood module.Still it is widely used in 11i and 12i. So let’s dive into its workings in more detail.
This is part one of a so far unlimited series. Don’t be put off by the lack of code in this part. We first need to go through the basics. In the next parts we’ll get more action.
During this series, I used an 11.5.10 instance on a 9.2.0.8 database. The basic statements in these articles will still hold for earlier and later versions, but small modifications may be needed.
In this first part we go into the definitions and the basics of the Workflow engine. We start with some definitions, and then we build a simple basic workflow.
We’ll see how this relates to the wf_tables in the database.
The basic terminology
First. What is a workflow? A workflow is a sequence of functions and events that follow a certain path based on decisions made during the progress of the sequence.
Most of us know the pictures from workflow builder. With the pictograms for functions joined together with lines.
That set is a definition of a workflow. In the Oracle workflow world it is called a ‘process’. The nodes in the process can be functions, processes or notifications.
All these are grouped together in containers that Oracle called an ‘Itemtype’. The itemtype is very important, since it will become part of the primary key in the underlying tables.
The actual workflows that are running according to the definition of the itemtype are called ‘Items´. The item is started as a specific process within an ‘itemtype’. And it is uniquely identified by the ‘itemtype’ and an ‘itemkey’.
Every process consists of 2 or more nodes, which are joined together by transitions. At least 2 nodes are required, because a process needs a ’start’ and a ’stop’-node.
Ok. We talked enough for now. Let’s build a process and find out the rest along the way.
By the way, all the definitions above will be linked to a glossary later on.
Getting Started
To start building our process, we first need the itemtype. To create an itemtype, we use ‘Workflow builder’. In workflow builder, when we click the new button we are greeted with this screen:
On right clicking the ‘untitled’ map, we can create a new itemtype.
Internal name is the unique name that will be used in the table keys for this itemtype and its items. It is limited to 8 characters. So choose wisely!
Display name is the name that will be shown to users when they need to interact with items from this itemtype.
The description…….. you can guess that one.
We will discuss the other three fields in a later article.
My first Itemtype
I choose to start building a flow that will do some DBA checks and tries to fix problems or notify the DBA if there is a problem.
During the course of building this flow, we’ll stumble on different features of the workflow engine.
The first step is to build the itemtype.
I called it: DBA_TYPE.
With a display name: DBA Itemtype
And a description: Itemtype to hold DBA processes and functions.
When you open your newly created itemtype, you see the components that can be created within this itemtype.
You’ll remember that the flow definition was called a process. So next we create a new ‘Process’:
Because this is a process that we will only be calling from our client, we have no use for the result type at the moment.
Later on, we’ll see nested processes, where the result of a process will determine the direction of the calling process.
When we go to the Process Detail (right click the process). We again have a virgin drawing board.
This will be where the actual flow is created. Every process consists of activities (functions, notifications and processes) and the transitions between them (based on the results of the activities).
Also every process has to start with a ‘Start’ Activity and finish with an ‘End’ activity. (Take care to avoid loose ends, since the end finalizes the flow and gives back control, or makes the flow purgeable).
So first we create a new function to start our flow.
Note the wf_standard.noop for the function. This is a dummy function because the only purpose of this node is to indicate the starting point for the process.
Even though we named this function ‘START’, we still need to flag it as a ‘Start’ function. That is in the node tab.
We then create an ‘END’ function in the same way.
Finally we create our own function.
Now we have an item_type with 1 process, and 3 functions. It’s time to connect the functions together.
Right click START, and drag to INITIALIZE_FLOW. Then right click there and drag to END. The result should be like:
Now we have a runnable flow.
You can even start it already, if you create an empty packaged function: ‘ XXX_WF_DBA.init’.
But there is more work to be done.
First we are going to see how this is recorded in the wf_ tables in our database in part 2 of our series.
In this part we will look at the controls for the workflow items. This includes ‘Defer thread’, ‘Block’, ‘Wait for flow’ and ‘Wait’. Also we will see the suspend and abort functions from the workflow engine.
These functions allow the execution of a workflow item to be halted, delayed or taken over from online processing to background processing. The ‘Wait for Flow’ allows some interaction between different items.
The first function we will look into is the ‘Defer Thread’ (or short ‘Defer’).
By default the session that starts an item executes all functions in that item. That means that long running activities are also executed online. The ‘Defer’ function makes it possible to queue those long running activities to be executed by a background process.
Let’s start with a small process to show how this works. We create an item with only a function ‘Defer’ (copied from the STANDARD itemtype). I created the following process:
The ‘Defer Thread’ has no node attributes.
When we run this process, and check the activity_statuses we get the following result:
select wpa.instance_label
, case when wpa.start_end is not null then wpa.start_end else wa.function end function
, wias.begin_date
, wias.end_date
, wias.activity_status status
, wias.activity_result_code result
, outbound_queue_id
from wf_item_activity_statuses wias
join wf_process_activities wpa
on (wias.process_activity=wpa.instance_id)
join wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wa.end_date is null)
join wf_activities_tl wat
on (wa.item_type=wat.item_type
and wa.name=wat.name
and wa.version=wat.version
and wat.language='US')
where wias.item_type='DBA_TYPE'
and wias.item_key='14'
order by wias.begin_date,wias.execution_time;
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT OUTBOUND_QUEUE_ID
------------------- ----------------- ------------------ ------------------ -------- ------ -----------------
DBA_CONTROL_PROCESS 24-7-2009 14:02:46 ACTIVE #NULL
START START 24-7-2009 14:02:46 24-7-2009 14:02:46 COMPLETE #NULL
DEFER WF_STANDARD.DEFER 24-7-2009 14:02:46 DEFERRED #NULL 6F728D74CA8142E6E0440003BAB3AD6B
Even though control was given back to the client, the item did not complete yet. The root is still active and the second function got status ‘Deferred’. This function is waiting to be processed by a background engine.
Before we start a background engine, we first take a look at the deferring mechanism.
The WF_STANDARD.DEFER function only sets the result_code of the process_activity to ‘DEFERRED’. To be precise it is set to the constant ‘wf_engine.eng_deferred’. When the engine encounters this result_code, it will queue a message to the ‘WF_DEFERRED_QUEUE_M’queue with a payload of item_type, item_key and process_activity.
The table for the ‘WF_DEFERRED_QUEUE_M’ is ‘WF_DEFERRED_TABLE_M’. The queue table can be queried directly or through the queuing view. (Which shows some translated columns). The key is the outbound_queue_id from the wf_item_activity_statuses:
select queue
, corr_id
, msg_priority
, msg_state
, enq_time
, def.user_data.itemtype
, def.user_data.itemkey
, def.user_data.actid
, consumer_name
from applsys.AQ$WF_DEFERRED_TABLE_M def
where msg_id=(select outbound_queue_id
from wf_item_activity_statuses
where item_type='DBA_TYPE'
and item_key='14'
and activity_status='DEFERRED');
No surprises there. The message is queued, and ready to be picked up.
The queue is read by the ‘Workflow Background Process’. In eBS this is a concurrent request under the ‘System Administrator’ responsibility. We will discuss the parameters later in this article.
An alternative is to run a background engine directly from SQL. That is what we will do now.
Begin
Wf_engine.background (itemtype=>’DBA_TYPE’
,process_deferred=>TRUE
,process_timeout=>FALSE
,process_stuck=>FALSE);
End;
We will discuss the timeout and stuck parameters later in this article. And idem for 2 parameters for ‘Treshold’.
The background engine will dequeue the message from the queue, and process the workflow item as of that process_activity.
When we look at the ‘wf_item_activity_statuses’ we notice something strange.
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 24-7-2009 14:02:46 24-7-2009 14:20:43 COMPLETE #NULL
START START 24-7-2009 14:02:46 24-7-2009 14:02:46 COMPLETE #NULL
DEFER WF_STANDARD.DEFER 24-7-2009 14:20:43 24-7-2009 14:20:43 COMPLETE #NULL
END END 24-7-2009 14:20:43 24-7-2009 14:20:43 COMPLETE #NULL
The begin_date for the ‘Defer’ is the time that it was run by the background engine. This is because after dequeueing the workflow engine will restart the process starting with this process_activity. So it will actually be re-executed at that time.
We can see that more clearly with an adjusted ‘Track_flow_progress’ function (see part 2 of this series for the original). This version will return a completion code of ‘wf_engine.eng_deferred’ to defer the item. We then replace the original ‘Defer’ function with our ‘track_flow_progress’ function.
CREATE OR REPLACE Procedure track_flow_progress (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
v_activity_name varchar2(30);
v_activity_version number;
v_process_name varchar2(30);
v_instance_label varchar2(30);
v_count number;
begin
select activity_name,wa.version,process_name,instance_label
into v_activity_name, v_activity_version, v_process_name, v_instance_label
from wf_process_activities wpa
join wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wa.end_date is null)
where wpa.instance_id=p_actid;
insert into xxx_track_flow (id
,item_type
,item_key
,activity_name
,activity_version
,process_name
,instance_id
,instance_label
,funcmode)
values (xxx_track_flow_s.nextval
,p_item_type ,p_item_key
,v_activity_name
,v_activity_version
,v_process_name
,p_actid
,v_instance_label
,p_funcmode
);
select count(*)
into v_count
from xxx_track_flow x
where item_type=p_item_type
and item_key=p_item_key
and instance_id=p_actid;
if v_count=1 then
p_result:=wf_engine.eng_deferred;
end if;
end;
/
Let’s see what happens when we put this instead of the standard ‘Defer’
begin
wf_engine.launchprocess(itemtype=>'DBA_TYPE',itemkey=>'15',process=>'DBA_CONTROL_PROCESS');
end;
We can confirm that the process is deferred:
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 24-7-2009 17:44:32 ACTIVE #NULL
START START 24-7-2009 17:44:32 24-7-2009 17:44:32 COMPLETE #NULL
TRACK_FLOW TRACK_FLOW_PROGRESS 24-7-2009 17:44:32 DEFERRED #NULL
The track_flow_progress function has executed once:
select count(*)
from xxx_track_flow
where item_type=’DBA_TYPE’
and item_key=’15’;
COUNT(*)
--------
1
Now when we run the background engine. And check again:
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 24-7-2009 17:44:32 24-7-2009 17:57:56 COMPLETE #NULL
START START 24-7-2009 17:44:32 24-7-2009 17:44:32 COMPLETE #NULL
TRACK_FLOW TRACK_FLOW_PROGRESS 24-7-2009 17:57:56 24-7-2009 17:57:56 COMPLETE #NULL
END END 24-7-2009 17:57:56 24-7-2009 17:57:56 COMPLETE #NULL
And ‘track_flow_progress’:
ID ITEM_TYPE KEY ACTIVITY_NAME VERSION PROCESS_NAME INSTANCE INSTANCE_LABEL FUNCMODE
174 DBA_TYPE 15 TRACK_FLOW 35 DBA_CONTROL_PROCESS 245549 TRACK_FLOW RUN175 DBA_TYPE 15 TRACK_FLOW 35 DBA_CONTROL_PROCESS 245549 TRACK_FLOW RUN
The flow has completed now, and you can see the 2 calls to Track_flow_progress.
This is something that you need to be aware of, whenever an issue arises with a function that defers itself.
Now let’s look at the ‘costing’-model for the workflow engine. The goal of the background engines is to process activities with a long runtime, or that need heavy resources.
As we saw, the call to the background engine picks up all deferred items for a certain item_type. Especially with multiple or complex processes within an item_type, it might be convenient to make a further split.
This is done by assigning costs to different activities. Originally the cost is meant to be the runtime of an activity in seconds. (In workflow builder. It is stored in microseconds in the database).
When calling the background process, you can enter 2 parameters:
Mintreshold and maxtreshold.
The background engine will only process activities until the next defer. It does not matter if it is within the cost range of the background engine. The item will be deferred, and wait for the next run of an eligible background engine.
This is a good time to show the versioning of the workflow engine in action. We investigated the versioning system of the workflow in part 1 (and 2). Where we saw that the workflow will always follow the same process definition.
We can now see that this also goes for the activity_level. When we update the cost of an activity, while an item is active, the background engine will still use the original cost. Consider this:
I put a cost of 10 (seconds) to our ‘Track_flow_progress’ activity:
When we run an item now, it is deferred on the ‘Track_flow_progress’ activity. Deferring an item automatically means that the workflow engine will commit. (After all, the queue message needs to be visible to the background engine).
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT
------------------- ---- ------------------- ------------------ ------------------ -------- ------
DBA_CONTROL_PROCESS 0 24-7-2009 20:12:51 ACTIVE #NULL
START 0 START 24-7-2009 20:12:51 24-7-2009 20:12:51 COMPLETE #NULL
TRACK_FLOW-1 1000 TRACK_FLOW_PROGRESS 24-7-2009 20:12:51 DEFERRED
While the first item is deferred, I increased the cost of the activity to 20:
And started another item. This item is also deferred:
INSTANCE_LABEL COST ITEM_KEY FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 0 17 24-7-2009 20:13:01 ACTIVE #NULL
START 0 17 START 24-7-2009 20:13:01 24-7-2009 20:13:01 COMPLETE #NULL
TRACK_FLOW 2000 17 TRACK_FLOW_PROGRESS 24-7-2009 20:13:01 DEFERRED
Now when I run a background engine with minthreshold 0 and maxthreshold 1100 it nicely picks up the item with key ’16′. Only when the background engine is run with a maxthreshold of 2000 or more, it runs the item with key ’17′.
One final thing to mention about cost, is that the ‘online’ workflow engine will not defer activities with a cost under 50 microseconds. (0.50 in workflow builder).
You can change this threshold by adjusting wf_engine.threshold. In a pl/sql procedure call: wf_engine.threshold:= n;
All items launched with this setting, will use the adjusted threshold. Anything above this threshold needs to be picked up by the background engine.
Let’s take a look at the background engine then. We already saw that it uses the WF_DEFERRED_QUEUE_M to select activities to process.
But a common problem in eBS environments is the long run time of the background processes.
The first point of action would be to purge your obsolete workflow data. Try to use the concurrent program from eBS to do that. In a later part of this series, we will look at the purging mechanism and suggest some ways to optimize it.
But if after the purging the background engine still runs for a long time, some other factors might be involved.
A common issue is with processes that are dependent on some checks. For example, you might check if a condition is true. And if not, defer the process without a timeout. Every time the workflow background engine runs this function, it will be deferred again. Causing it to be re-queued for the background engine. Ultimately causing the background engine to get in a loop on this activity. Rather than deferring these activities, the developer should use a ‘Block’, or preferably the ‘Business Event System’. Using a ‘Wait’ with sufficient time-out time might also be an option.
A similar thing can happen with time-out values with very short wait times.
When you notice a high CPU usage for QMON (QMNx), it might be time to coalesce the AQ IOT’s (Index Organized Tables). At the time of writing Oracle published note: 271855.1 on Metalink to do this.
So far for the deferred function. Let’s take a look at the next control function: Block.
The block function is also available from the ‘STANDARD’ itemtype. It holds your item until the wf_engine.completeactivity API is called.
Originally this was developed to let the item wait for a call from an external system. But with the arrival of BES (Business Event System), which we’ll discuss later in this series, it’s usefulness has decreased.
However, the function is still found in lots of places. So we’ll look at the internals.
The block function is a call to ‘WF_STANDARD.BLOCK’. This procedure returns a resultcode of wf_engine.eng_notified. In wf_item_activity_statuses the activity_status of the process_activity will be ‘NOTIFIED’.
This status is also used for Notifications (see next part in the series), when a reply from the user is expected. Since there is no notification, the process_activity cannot continue.
I changed the ‘Control Process’ to include a ‘Block’ activity as follows:
Now when we launch an item, we see that it will hold on the block.
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 0 24-7-2009 20:55:00 ACTIVE #NULL
START 0 START 24-7-2009 20:55:00 24-7-2009 20:55:00 COMPLETE #NULL
BLOCK 1 WF_STANDARD.BLOCK 24-7-2009 20:55:00 NOTIFIED
To progress this item we now have to call the API: ‘wf_engine.completeactivity’. This API will initiate a workflow engine to continue the item.
The parameters for the API besides itemtype and itemkey are activity and result.
Activity is a concatenation of the process_name, ‘:’ and the instance_label from wf_process_activities.
In our case the call would be:
begin
wf_engine.completeactivity(itemtype=>'DBA_TYPE'
,itemkey=>'20'
,activity=>'DBA_CONTROL_PROCESS:BLOCK'
,result=>wf_engine.eng_completed);
end;
When the activity has a result type, the result has to match one of the lookup codes in the result type. Otherwise any value is allowed, even tough it is a good habit to use the result codes in use by the wf_engine.
So what happens when an item does not get the ‘CompleteActivity’ call? Since the ‘Block’ is designed for External Completion, it is not unlikely that an error in the external system will cause the ‘CompleteActivity’ not to be called. In that case the item will be in the ‘Notified’ state forever.
For these situations, you can put a ‘TimeOut’ on the process activity. A Time-out is either an absolute timestamp obtained from an item_attribute or a relative time set on the process_activity.
For this example, I set a relative time of 5 minutes on the ‘Block’ activity:
And of course an extra transition for the ‘Time out’, to a second ‘End’ node.
Now when we run the process, we see that the ‘Due_date’ column in WF_ITEM_ACTIVITY_STATUSES has been set:
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 17:26:05 ACTIVE #NULL
START 0 START 25-7-2009 17:26:05 25-7-2009 17:26:05 COMPLETE #NULL
BLOCK 1 WF_STANDARD.BLOCK 25-7-2009 17:26:05 NOTIFIED 25-7-2009 17:31:05
It is set for exactly 5 minutes after the begin_date of the process_activity.
After 5 minutes we can start a background process. With parameter ‘process_timeout=>TRUE’, or no parameter, since TRUE is the default.
Then we see that it has ran through the timeout transition:
Note that this time, the begin_date of the ‘block’ activity did not change. This is because the activity was not rerun. It was just cut-off, and the engine picked up at the ‘timeout’ transition.
Even when the timeout period has expired, it is still possible to call the ‘CompleteActivity’ API. In that case, the workflow engine will still take the default transition, instead of the timeout one.
But once the item has gone through the timeout transition, it is not possible to call the ‘CompleteActivity’ API anymore. Trying to do so will result in a
‘ORA-20002: 3133: Activity instance ‘DBA_CONTROL_PROCESS:BLOCK’ is not a notified activity for item ‘DBA_TYPE/21’.
This error is usually innocent. It just means that the item has progressed beyond the process activity that is called to be completed. And a time-out is the most common cause. Of course all programs calling the ‘CompleteActivity’ API need error handling for this.
At this point, I’ll give a short example of the error handling. In a later part in this series, we’ll go deeper into workflow troubleshooting and error handling.
Now when we call the ‘CompleteActivity’ API for the item above, we run the following:
declare
v_errorname varchar2(30);
v_errormsg varchar2(2000);
v_errorstack varchar2(32000);
invalid_action EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_action, -20002);
begin
wf_engine.completeactivity(itemtype=>'DBA_TYPE'
,itemkey=>'21'
,activity=>'DBA_CONTROL_PROCESS:BLOCK'
,result=>wf_engine.eng_completed);
exception
when invalid_action THEN
wf_core.get_error(v_errorname,v_errormsg,v_errorstack);
dbms_output.put_line(v_errorname);
dbms_output.put_line(v_errormsg);
dbms_output.put_line(v_errorstack);
end;
/
WFENG_NOT_NOTIFIED3133: Activity instance 'DBA_CONTROL_PROCESS:BLOCK' is not a notified activityfor item 'DBA_TYPE/21'.
Wf_Engine.CompleteActivity(DBA_TYPE, 21, DBA_CONTROL_PROCESS:BLOCK, COMPLETE)
Let’s go through the procedure.
First we see a declaration of a custom exception for ORA-20002. This is the custom error that workflow will issue. It is optional in this case, since any error can only occur from the API-call. But that might not always be the case.
In the exception block we first call ‘wf_core.get_error’ to retrieve the information about this error. If needed we can parse this error further. But the error_name already tells us that we tried to complete a process_activity that is not in a notified state. Based on this the caller should be able to decide what needs to be done.
With that we can move to the next function in this article.
That will be the ‘WAIT’ function. The ‘WAIT’ is used to postpone further processing of the item to a time set by an item_attribute or relative to the start of the process_activity.
It works differently from the way the ‘timeout’ worked on the ‘Block’ activity. That was only picked up by a background engine with parameter ‘process_timeout=>TRUE’.
Let’s replace the block in our process with a ‘Wait’ function. The ‘Wait’ can be copied from the ‘STANDARD’ itemtype. It also needs a lookup type ‘Wait Mode’. This lookup type defines how to determine the timestamp to complete the process activity.
The possible values are ‘Day of the week’, ‘Day of the month’, ‘Absolute date’ and ‘Relative time’.
When you add the ‘Wait’ function to your item, this will be the first ‘node attribute’ to set. As usual this can be set with a constant, or an item attribute value. When you choose to set it to a fixed date, you can also set the ‘Time of day’ attribute to set the timestamp. This is of course not needed for the ‘Relative time’ mode.
All node attributes can be set to a constant or an item_attribute_value. In our sample we will first set a relative time, based on an item attribute value. (for a change).
We use one function to set a new item_attribute:
And the second node is a ‘Wait’ till the requested time:
The relative time is in {days} . {fraction of day}. So 5/(24*60) is 5 minutes. 0.03 is just about 4.5 minutes therefore.
When we launch a new item, and check the status:
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 20:23:22 ACTIVE #NULL
START 0 START 25-7-2009 20:23:22 25-7-2009 20:23:22 COMPLETE #NULL
ASSIGN 1 WF_STANDARD.ASSIGN 25-7-2009 20:23:22 25-7-2009 20:23:22 COMPLETE #NULL
WAIT 1 WF_STANDARD.WAIT 25-7-2009 20:27:41 DEFERRED #NULL
So the ‘Wait’ activity is deferred. But it does not get a due_date. Instead the begin_date is set to the time till which the activity has to wait.
Since the status=’DEFERRED’, we also have a record in the wf_deferred_queue_m:
select queue
, corr_id
, msg_state
, enq_time
, delay
, deq_time
, def.user_data.itemtype itemtype
, def.user_data.itemkey itemkey
, def.user_data.actid actid
from applsys.AQ$WF_DEFERRED_TABLE_M def
where msg_id=(select outbound_queue_id
from wf_item_activity_statuses
where item_type='DBA_TYPE'
and item_key='22'
and activity_status='DEFERRED');
So the message has been queued with a delay till the start_time of the process_activity.
Note that the msg_state=’WAIT’. This means that the message cannot be dequeued yet. One or more QMON processes need to be running. (Set with ‘alter system set aq_tm_processes=x scope=both;). These processes are responsible for moving messages from the ‘WAIT’ state to the ‘READY’ state at the designated moment.
Depending on the load on your system and the number of messages to be handled, you can put more QMON processes. But for most systems one QMON process will be sufficient for the workflow activity. If you need more than one QMON process, you’ll also have to be running multiple background engines.
Only messages with msg_state ‘READY’ can be dequeued and processed by the background engine.
When the message is eligible to be dequeued, you can run a background engine to process the process_activity as any ‘Deferred’ activity. The begin_date of the process_activity will then be updated to the time it was processed by the background engine.
Now let’s change the process to use a ‘Wait’ till a day of the week. In this case we’ll wait till Monday morning. First we set the attribute to the ‘Date’ type.
Then we change the ‘Assign activity’. Here we set the value to any date with the timestamp that we want. The date-part of the assignment will be ignored by the ‘Wait’ function.
Finally we update the Node attributes on the ‘Wait’ activity to be ‘Day of the Week’, a ‘Monday’, and the ‘Delay time’ attribute for ‘Time of the Day’.
Now when we launch a process, we can see that the activity is indeed postponed till the next Monday. (In this case 27th july 2009). At 8.00AM.
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 20:50:14 ACTIVE #NULL
START 0 START 25-7-2009 20:50:14 25-7-2009 20:50:14 COMPLETE #NULL
ASSIGN 1 WF_STANDARD.ASSIGN 25-7-2009 20:50:14 25-7-2009 20:50:14 COMPLETE #NULL
WAIT 1 WF_STANDARD.WAIT 27-7-2009 8:00:00 DEFERRED #NULL
At this point it’s time to look at a special ‘feature’ of the ‘DEFERRED’ status. Before I said that you can put an activity in ‘DEFER’ mode just by returning a result_code of wf_engine.eng_defer.
There is a bit more to that. The WF_STANDARD.DEFER procedure checks whether it is being executed for the first or second time. The first time it is executed, it is to set the status to ‘DEFERRED’. That time, the result_code from wf_item_activity_statuses has not been set yet. So the procedure returns the result_code ‘wf_engine.eng_defer’. When it is being called, while the result_code in wf_item_activity_statuses has been set, it will assume it is being called from a background process and return ‘wf_engine.eng_completed’. Which will then signal the workflow engine to continue with the item.
We can use this mechanism to our own advantage. When an item is deferred, and we want it to be run immediately, without the background process, then we can retry the process activity. The workflow engine has an API for this:
Begin
Wf_engine.handleerror(itemtype=>’DBA_TYPE’
,itemkey=>’21’
,activity=>’DBA_CONTROL_PROCESS:DEFER’
,command=>’RETRY’
);
End;
We will see more about the handleerror API in a later part. With the command ‘RETRY’, it will re-execute the indicated process_activity. In the case of a ‘DEFER’, it will therefore complete the activity. And continue the process.
The same mechanism also applies to the ‘WAIT’ function, since it basically only defers an activity.
Be aware of this, when activities are re-executed. Since it may not always be the desired result to complete the activity.
Now let’s move on to the last controls that we are discussing in this part.
We start with the ‘Launch Process’ function. You can copy it from the ‘STANDARD’ item_type. Including the lookup_type ‘YES/NO’, if you don’t already have that. The lookup_type is required for one of it’s activity_attributes.
It will be clear what this function is doing from the name. It launches another item from the current item. There is no restriction on the item_type. The function will perform the same ‘wf_engine.launch’, that we execute from sql*plus during this series.
The function has six activity_attributes, one of which is mandatory (2 in our case, since we don’t have an automatic selection for the process yet.).
The first attribute is item_type. This shouldn’t require a further explanation.
Item_key is optional. If no item_key is provided, the function will build an item of the ‘<current item_type>: <current item_key>-n’ where n is a sequence number for the number of items launched by the current item. The sequence is stored in a new item_attribute: ‘LAUNCH_COUNT’.
Then comes the process_name. It is possible to use a selector function to automatically start the right process within an item_type. But when this is not used, you need to provide the process_name.
User_key and owner are optional, and refer to a user-defined reference key and a workflow role that will assume ownership of this item.
The last attribute is ‘Defer Immediate’, with a lookup_type of ‘YES/NO’. When set to no, the new item will be launched immediately, and be executed by the current workflow engine.
When ‘Defer Immediate’ is set to ‘Yes’, the activity will be deferred. And both the ‘Launch Process’ activity as the new item will be executed by a background engine.
After the ‘Launch Process’ activity, the item will continue it’s own processing. (Without the ‘Defer Immediate’ attribute set to ‘Yes’, the engine will first process the new item as far as possible, before returning to the original item). There is no further relationship between the two items.
The last two controls do create a relationship between two or more items. It is possible to create parent/child relationships on the item-level.
We are going to create a sample of this by using ‘DBA_CONTROL_PROCESS’ to launch ‘DBA_MAIN_PROCESS’ and wait for it’s completion.
On the ‘Launch Process’ function it is not possible to specify the parent/child relationship. So we have to use the ‘wf_engine.CreateProcess’ and ‘wf_engine.StartProcess’ functions. The first one only creates an item, without executing it.
After the ‘CreateProcess’, we can call the ‘SetItemParent’ API to set the parent/child relationship.
So we have to create a new procedure:
CREATE OR REPLACE PROCEDURE XXX_LAUNCH_CHILD (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
BEGIN
if p_funcmode='RUN' then
wf_engine.CreateProcess(itemtype=>p_item_type
,itemkey=>p_item_key||'-1'
,process=>'DBA_MAIN_PROCESS');
wf_engine.SetItemParent(itemtype=>p_item_type
,itemkey=>p_item_key||'-1'
,parent_itemtype=>p_item_type
,parent_itemkey=>p_item_key
,parent_context=>NULL
,masterdetail=>TRUE);
wf_engine.StartProcess(itemtype=>p_item_type
,itemkey=>p_item_key||'-1');
end if;
p_result:=wf_engine.eng_completed;
END XXX_LAUNCH_CHILD;
/
The ‘CreateProcess’ and ‘StartProcess’ should be obvious in their usage. In the ‘SetItemParent, you can indicate a ‘parent_context’. This is a free text value, that can be referenced again in the ‘WaitforFlow’ and ‘ContinueFlow’ functions. In case you launch multiple child items, you use the context value to know which items should continue. We will come back to this at the end of the example.
First we are going to build our process with the new function:
The process will look like this:
And of course we need to put the ‘ContinueFlow’ in the ‘DBA_MAIN_PROCESS’. Actually twice, since I want to put it in front of the ‘End’ and we have 2 exits.
Now when we start the ‘DBA_CONTROL_PROCESS’ we can see what happens:
select wpa.instance_label
, wias.item_key
, case when wpa.start_end is not null then wpa.start_end else wa.function end function
, wias.begin_date
, wias.end_date
, wias.activity_status status
, wias.activity_result_code result
from wf_item_activity_statuses wias
join wf_process_activities wpa
on (wias.process_activity=wpa.instance_id)
join wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wa.end_date is null)
join wf_activities_tl wat
on (wa.item_type=wat.item_type
and wa.name=wat.name
and wa.version=wat.version
and wat.language='US')
where wias.item_type='DBA_TYPE'
and wias.item_key like '20%'
order by wias.begin_date,wias.execution_time;
I purposely left the ‘Defer’ in the ‘DBA_MAIN_PROCESS’ so we can see the status while the ‘DBA_MAIN_PROCESS’ is running. When the workflow engine could not process ‘DBA_MAIN_PROCESS’ further, it returned to the original item, and executed ‘WAITFORFLOW’, which of course got the ‘NOTIFIED’ status.
We can also see the parent_child relationship by querying ‘WF_ITEMS’:
select item_type
, item_key key
, begin_date
, parent_item_type parent_type
, parent_item_key parent_key
from wf_items
where item_type='DBA_TYPE'
and item_key like '20%'
The ContinueFlow nicely completed the ‘WaitforFlow’ and both items completed successfully.
So how does the ‘ContinueFlow’ activity know which process_activity to continue? The first check being done is whether the activity_attribute ‘Waiting Flow’ is set to Master or Detail. When it is set to ‘Detail, the ContinueFlow is a parent process. So it will look for it’s children in wf_items, and complete all activities that have the instance_label from the ‘Waiting Activity Label’ activity_attribute.
When it is set to ‘Master’, this is just one of the (possibly) multiple children that have completed. So it will count the number of children for the parent item. And check all of them to see if they have reached the ‘ContinueFlow’ process_activity. When all children have reached the ‘ContinueFlow’, the last item will complete the activity on the parent item.
Of course it is possible for a parent item to have multiple ‘WaitforFlow’ activities, for different child items. In this case, you need to distinguish the different parent/child relationships. That is where the ‘parent_context’ parameter from the ‘SetItemParent’ function comes in.
The parent_context can be used to group together all child items that were started from the same process_activity.
For many eBS-DBA’s workflow is a strange and hardly understood module. Still it is widely used in 11i and 12i. So let’s dive into it’s workings in more detail.
This is part one of a series of 3. During this series, I used a 11.5.10 instance on a 9.2.0.8 database. The basics will still hold for earlier and later versions, but small modifications may be needed.
In this part we go into the definitions and the basics of Workflow. We start with some definitions, then build a simple basic workflow. And we see how this relates to the wf_tables in the database.
Â
The basic terminology
First. What is a workflow? A workflow is a sequence of functions and events that follow a certain path based on decisions made during the progress of the sequence.
Most of us know the pictures from workflow builder. With the pictograms for functions joined together with lines.
That set is a definition of a workflow. That definition in Oracle is called a ‘process’. The nodes in the process can be functions, processes or notifications.
All these are grouped together in containers that Oracle called an ‘Itemtype’. The itemtype is very important, since it will become part of the primary key in the underlying tables.
The actual workflows that are running according to the definition of the itemtype are called ‘Item’s. The item is started as an itemtype and a process. And it is uniquely identified by itemtype and an itemkey.
Every process consists of 2 or more nodes, that are joined together by transitions. At least 2 nodes are required, because a process needs a ’start’ and a ’stop’-node.
Ok. Enough talking. Let’s build a process and find out the rest along the way. By the way. All the definitions above will be linked to a glossary later on.
Â
Getting Started
To start building our process, we first need the itemtype.
To create an itemtype, we use ‘Workflow builder’. In workflow builder, when we click the new button we are greeted with this screen:
Â
Â
On rightclicking the untitled map, we can create a new itemtype.
Â
Internal name is the unique name that will be used in the table keys for this itemtype and it’s items. It is limited to 8 characters. So choose wisely!
Display name is the name that will be shown to users when they need to interact with items from this itemtype.
The description…….. you can guess that one.
We will discuss the other three fields in a later article.
Â
My first Itemtype
I choose to start building a flow that will do some DBA checks and tries to fix problems or notify the DBA if there is a problem. During the course of building this flow, we’ll stumble on different features of the workflow engine.
The first step is to build the itemtype. I called it: DBA_TYPE.
With a display name: DBA Itemtype
And a description: Itemtype to hold DBA processes and functions.
Â
When you open your newly created itemtype, you see the components that can be created within this itemtype.
You’ll remember that the flow definition was called a processes. So next we create a new ‘Process’:
Â
Â
Because this is a process that we will only be calling from our client, we have no use for the result type at the moment. Later on, we’ll see nested processes, where the result of a process will determine the direction of the calling process.
When we go to the Process Detail (right click the process). We again have a virgin drawing board. This will be where the actual flow is created.
Every process consists of activities (functions, notifications and processes) and the transitions between them (based on the results of the activities).
Also every process has to start with a ‘Start’ Activity and finish with an ‘End’ activity. (Take care to avoid loose ends, since the end finalizes the flow and gives back control, or makes the flow purgeable).
So first we create a new function to start our flow.
Â
Note the wf_standard.noop for the function. This is a dummy function. Because the only purpose of this node is to indicate the starting point for the process.
Even though we named this function ‘START’, we still need to flag it as a ‘Start’ function. That is in the node tab.
Â
Â
 We then create an ‘END’ function in the same way.
Finally we create our own function.
Â
Now we have an item_type with 1 process, and 3 functions.
Time to connect the functions together. Right click START, and drag to INITIALIZE_FLOW. Then right click there and drag to END.
The result should be like:
Â
Now we have a runnable flow. You can even start it already, if you create an empty packaged function: XXX_WF_DBA.init.
But there is more work to be done.
First we are going to see how this is recorded in the wf_ tables in our database. But that is part 2 of our series.
