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’.
Now when I run the process, we get this result:
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT OUTBOUND_QUEUE_ID DBA_MAIN_PROCESS 31-7-2009 12:36:58 ACTIVE #NULL START START 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE #NULL INITIALIZE_FLOW XXX_WF_DBA.init 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE COMPLETE COMPARETEXT WF_STANDARD.COMPARE 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE EQ CHECK_INVALIDS XXX_CHECK_INVALIDS 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE Y GET_INVALIDS XXX_WF_UTILS.get_invalids 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE LOOPCOUNTER WF_STANDARD.LOOPCOUNTER 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE LOOP PROCESS_INVALIDS XXX_WF_UTILS.process_invalids 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE DEFER WF_STANDARD.DEFER 31-7-2009 12:37:33 31-7-2009 12:37:33 COMPLETE #NULL AND WF_STANDARD.ANDJOIN 31-7-2009 12:37:33 WAITING TRACK_FLOW-1 TRACK_FLOW_PROGRESS 31-7-2009 12:37:42 31-7-2009 12:37:42 COMPLETE
Now we can abort the item with the API:
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’.
And this is the result afterwards.
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT OUTBOUND_QUEUE_ID DBA_MAIN_PROCESS 31-7-2009 12:36:58 31-7-2009 12:39:10 COMPLETE #FORCE START START 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE #NULL INITIALIZE_FLOW XXX_WF_DBA.init 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE COMPLETE COMPARETEXT WF_STANDARD.COMPARE 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE EQ CHECK_INVALIDS XXX_CHECK_INVALIDS 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE Y GET_INVALIDS XXX_WF_UTILS.get_invalids 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE LOOPCOUNTER WF_STANDARD.LOOPCOUNTER 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE LOOP PROCESS_INVALIDS XXX_WF_UTILS.process_invalids 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE DEFER WF_STANDARD.DEFER 31-7-2009 12:37:33 31-7-2009 12:37:33 COMPLETE #NULL AND WF_STANDARD.ANDJOIN 31-7-2009 12:37:33 31-7-2009 12:39:10 COMPLETE #FORCE TRACK_FLOW-1 TRACK_FLOW_PROGRESS 31-7-2009 12:37:42 31-7-2009 12:37:42 COMPLETE
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.