eBS Troubleshooting
Oracle eBS troubleshooting
On this webpage, I’ll make a FAQ on common eBS problems and troubleshooting. Probably the format will change over time. But I’ll just start. I use basic flow diagrams, to give an overview of the process. Under the diagrams is an explanation of all the steps.
Ø My concurrent manager does not start
1) Check if the Internal Manager is running. On Unix, you can run: ps –ef | grep ‘FNDLIBR FND CPMGR’. If this returns a process, check the start time for the process to make sure it is the correct process.
2) If a process is returned, and it started at the time you started the managers, the ICM is running.
3) The ICM is not running. First check the adcmctl.txt file. It is found in the process_log directory. (Check your autoconfig xml file or the adcmctl.sh script). It will show the logs for the start-up scripts of the ICM.
4) The ICM is running, but can’t start the other managers. Most common cause is the APPS Listener is down. Check for it running with: ps –ef | grep ‘tnslsnr APPS_<SID>’
5) If no process is returned, start the apps listener with adalnctl.sh. Wait a minute afterwards, for the ICM to retry starting the managers.
6) The ICM is running, or was started successfully before it died. Check the logfile from the ICM in $APPLCSF/$APPLLOG. By default the file is named <SID>_<proc>.mgr, where proc is the process_id from adcmctl.txt.
Ø Notifications are not being sent by the notification mailer
1) Look for the notification in ‘wf_notifications’. Check the status, mail_status, recipient_role and notification_id.
2) If Mail_status = ‘SENT’, the message has been mailed already. If status<>’OPEN’ (Note the difference between status and mail_status), the notification is not eligible to be mailed anymore.
3) The Notification Mailer has already sent this message. Maybe it was sent to the TEST_ADDRESS from the Notification Mailer.
4) Check the mail_preference for the recipient_role from wf_roles.
5) If the preference is ‘QUERY’, ‘SUMHTML’, ‘SUMMARY’ or ‘DISABLED’ then the recipient will not receive notifications by mail.
6) Change the preference in the Users preferences. Or test with a different user.
7) The Notification Mailer reads notifications to send from the queue WF_NOTIFICATION_OUT. Select from the queue-view:
select notification_id,msg_state,msg_id,role,corrid,enq_time,deq_time
from (select msg_id, o.enq_time, o.deq_time, msg_state
,(select str_value
from table (o.user_data.header.properties)
where name = 'NOTIFICATION_ID') notification_id
, (select str_value
from table (o.user_data.header.properties)
where name = 'ROLE') role
, (select str_value
from table (o.user_data.header.properties)
where name = 'Q_CORRELATION_ID') corrid
from applsys.aq$wf_notification_out o)
where notification_id=<notification_id>
and rownum=1;
If you received a result on the query, the notification is in the queue.
9) The status of the notification in the queue should be ‘READY’. If it is ‘PROCESSED’, or ‘ERROR’, the notification is already dequeued or errored. Otherwise check the status of the queue with:
select name,enqueue_enabled,dequeue_enabled
from dba_queues
where name='WF_NOTIFICATION_OUT';
10) The status of the message should be ‘READY’. The ‘ENQUEUE_ENABLED’ and ‘DEQUEUE_ENABLED’ columns should be ‘YES’.
11) Reprocess the message with the conc. Request. Or stop/start the queue with dbms_aqadm.
12) Check the Notification Mailer logfile. The notification is ready for the mailer, but it is unable to pick it up. Check if the mailer shut down due to too many errors.
13) The message is not in ‘WF_NOTIFICATION_OUT’. It can still be queued on the ‘WF_DEFERRED’ queue. Check this queue with:
select v.msg_id,v.msg_state,v.enq_time,v.deq_time,v.corr_id
from applsys.aq$wf_deferred v
, table(v.user_data.parameter_list) t
where v.corr_id like 'APPS:oracle.apps.wf.notification.%'
and name ='NOTIFICATION_ID'
and value = <Notification_id>;
14) If the query returned results and the msg_state is ‘READY’, the notification is still being processed. Skip to 16
15) Unfortunately, your notification seems to have gone missing. Consult Oracle Support. Please inform us of the solution in this case, so we can extend the FAQ.
16) The notification is still in the deferred queue, waiting to be picked up by the ‘Workflow Deferred Notification Agent Listener’. Check the status of this listener. And if needed, check its logfile.
17) If the Deferred Notification Agent Listener is not running, or erroring
18) (re)start it.
19) Check if the Business Event ‘oracle.apps.wf.notification.denormalize’ is enabled and has a subscription to procedure ‘Wf_Notification_Util.Denormalize_RF’.
20) If either the Business event is disabled, or the subscription non-existent or disabled, go to 21.
21) Make sure that the Business event is enabled and the subscription is valid and enabled. You will still have to reprocess the notification with the concurrent program to resubmit notifications.
22) Unfortunately, we can’t locate the issue yet. If you manage to solve the issue please inform us, so we can update this FAQ.
Selecting from custom types in (workflow) tables
After a long silence, it’s time to write another blog entry. I received a request to write about Oracle Reports. And I think that will be a new series (Even though I didn’t finish the workflow series yet).
But the last few days, I’ve been working on cleaning up workflow tables.
Most of these tables are very straightforward and you can find queries and descriptions in the workflow series. However, there are some more complex cases. There are the advanced queuing (aq) tables. And also some data hidden in wf_item_attribute_values for items started by the Business Event System (BES) (reminder to self: Write that article about BES too).
In the aq-tables, the payload of the message (i.e. the data transferred by the message) is stored in a custom type. The same goes for the event_data in wf_item_attribute_values. In this article we’ll see how we can get the data from those ‘strange’ columns.
Let us start with wf_item_attribute_values. Processes that are started from a business event, store the data from the originating business event in the column ‘EVENT_VALUE’. This has a type ‘WF_EVENT_T’. When you query it in a sql*plus session, you will see a huge column filled with something like:
EVENT_VALUE(PRIORITY, SEND_DATE, RECEIVE_DATE, CORRELATION_ID, PARAMETER_LIST(NAME, VALUE), EVENT_NA
----------------------------------------------------------------------------------------------------
WF_EVENT_T(0, '17-SEP-09', NULL, NULL, WF_PARAMETER_LIST_T(WF_PARAMETER_T('TASK_ID', '16719879'), WF('ABORT_WORKFLOW', 'N'), WF_PARAMETER_T('SUB_GUID', '73BAB9A51BAF5307E04400144F687CA0')), 'oracle.ap, NULL, NULL)
The problem that many ebs-dba’s are facing is how to select the data inside this column. In this case, it would be the task_id that we are interested in. Oracle delivers several API's for use in PL/SQL. But sometimes you want plain SQL.
One way to do it is to use a clever substr/instr construction or a regular expression. But the efficient way to do it, is to tell Oracle which info you want.
Let’s take a look at the custom type. As mentioned, it is WF_EVENT_T. We can find the description in the DBA_TYPES table.
select owner,type_name,typecode,attributes,methods from dba_types where type_name='WF_EVENT_T'; OWNER TYPE_NAME TYPECODE ATTRIBUTES METHODS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- APPS WF_EVENT_T OBJECT 13 31
This tells us that the WF_EVENT_T is an object type.
We can find its attributes in DBA_TYPE_ATTRS:
select owner,type_name,attr_name,attr_type_owner,attr_type_name,length
from dba_type_attrs
where type_name=’WF_EVENT_T’;
OWNER TYPE_NAME ATTR_NAME ATTR_TYPE_OWNER ATTR_TYPE_NAME
--------- ------------ ------------------- ---------------- --------------
APPS WF_EVENT_T CORRELATION_ID VARCHAR2
APPS WF_EVENT_T ERROR_MESSAGE VARCHAR2
APPS WF_EVENT_T ERROR_STACK VARCHAR2
APPS WF_EVENT_T ERROR_SUBSCRIPTION RAW
APPS WF_EVENT_T EVENT_DATA CLOB
APPS WF_EVENT_T EVENT_KEY VARCHAR2
APPS WF_EVENT_T EVENT_NAME VARCHAR2
APPS WF_EVENT_T FROM_AGENT APPS WF_AGENT_T
APPS WF_EVENT_T PARAMETER_LIST APPS WF_PARAMETER_LIST_T
APPS WF_EVENT_T PRIORITY NUMBER
APPS WF_EVENT_T RECEIVE_DATE DATE
APPS WF_EVENT_T SEND_DATE DATE
APPS WF_EVENT_T TO_AGENT APPS WF_AGENT_T
You see that the attributes are defined including their datatype, which can be a seeded datatype (VARCHAR2) or a custom one (WF_PARAMETER_LIST_T). Now that we now the attributes of the type, we can select them directly. To select the ‘PRIORITY’, just use an extra qualifier:
select v.event_value.priority
from wf_item_attribute_values v
where ROWNUM = 1;
EVENT_VALUE.PRIORITY
--------------------
0
But how about the ‘PARAMETER_LIST’? That is where the task_id was stored. Let’s check the WF_PARAMETER_LIST_T definition:
select owner,type_name,typecode,attributes,methods from dba_types where type_name ='WF_PARAMETER_LIST_T'; OWNER TYPE_NAME TYPECODE ATTRIBUTES METHODS --------- ------------------- ------------------- ----------- ------- APPS WF_PARAMETER_LIST_T COLLECTION 0 0
This time, the type is a collection. We can find more info about a collection with:
select type_name,coll_type,elem_type_owner,elem_type_name from dba_coll_types where type_name='WF_PARAMETER_LIST_T'; TYPE_NAME COLL_TYPE ELEM_TYPE_OWNER ELEM_TYPE_NAME ------------------------------ ------------------------------ ------------------------------ -------------- WF_PARAMETER_LIST_T VARYING ARRAY APPS WF_PARAMETER_T
So the WF_PARAMETER_LIST_T is a Varray of WF_PARAMETER_T. Before we look at selecting from Varrays, we first check what WF_PARAMETER_T looks like:
select owner,type_name,typecode,attributes,methods from dba_types where type_name ='WF_PARAMETER_T'; OWNER TYPE_NAME TYPECODE ATTRIBUTES METHODS --------- ------------------- --------- ---------- ---------- APPS WF_PARAMETER_T OBJECT 2 4
That is an object type again. So we select:
select owner,type_name,attr_name,attr_type_owner,attr_type_name from dba_type_attrs where type_name='WF_PARAMETER_T'; OWNER TYPE_NAME ATTR_NAME ATTR_TYPE_OWNER ATTR_TYPE_NAME --------- ------------------- --------- ------------------- -------------- APPS WF_PARAMETER_T NAME VARCHAR2 APPS WF_PARAMETER_T VALUE VARCHAR2
Ok. We now know the whole structure of the parameter list. Back to the Varray. A Varray (Varying Array) is of course an array structure. Since this is similar to a table structure, you can cast the Varray into a table. Then you use the casted table to select your data. Let’s do that to get the names from the parameter list.
select t.name from wf_item_attribute_values v , table(v.event_value.parameter_list) t Where v.event_value IS NOT NULL And ROWNUM = 1; NAME ------------------------------ TASK_ID
Now that’s a neat trick. We can join our table to its own column!
In our case, we only have a task_id parameter. We could do the same again, to get the value of the parameter from the value column.
But to join wf_item_attribute_values to itself is a very expensive operation. Take a look at the explain plan:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 432M| | |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | NESTED LOOPS | | 207G| 11T| 432M| | |
| 3 | PARTITION RANGE ALL | | | | | 1 | 77 |
| 4 | PARTITION HASH ALL | | | | | 1 | 8 |
|* 5 | TABLE ACCESS FULL | WF_ITEM_ATTRIBUTE_VALUES | 25M| 1453M| 948K| 1 | 616 |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | | | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - filter(SYS_OP_NOEXPAND("V"."EVENT_VALUE") IS NOT NULL)
Note: cpu costing is off
That can kind of hurt if it,were it not for the NL with a COUNT STOPKEY (Because of the rownum=1). The reason for the expensive plan, is because for every row from wf_item_attribute_values, Oracle needs to get the data from event_value. It basically does a carthesian join with itself.
So here is another way to get your data:
select v.item_key
, (select value
from table(v.event_value.parameter_list) t
where t.name='TASK_ID' ) task_id
from wf_item_attribute_values v
where v.event_value is not null
and rownum=1;
ITEM_KEY TASK_ID
------------------------------------------------- --------
oracle.apps.jtf.cac.task.createTask-155563676 16719879
Now we use a scalar subquery, where Oracle will access only the event_value for the rows that will be returned to the user. You can see this in the explain plan by the collection iterator picklefetch (= the operation that collects the data from a collection type) being pushed up to just before the select.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 948K| | |
|* 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | | |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | PARTITION RANGE ALL | | | | | 1 | 77 |
| 4 | PARTITION HASH ALL | | | | | 1 | 8 |
|* 5 | TABLE ACCESS FULL | WF_ITEM_ATTRIBUTE_VALUES | 25M| 1453M| 948K| 1 | 616 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)='TASK_ID')
2 - filter(ROWNUM=1)
5 - filter(SYS_OP_NOEXPAND("SYS_ALIAS_1"."EVENT_VALUE") IS NOT NULL)
Note: cpu costing is off
Still expensive. But much better.
So that will let us peek into the event_value column on wf_item_attribute values. Be aware that different processes will enter different parameters into the event_value column. But with the information above you should be able to understand how to select the data and use it in your own queries.
Now let’s take a look at the AQ-tables?
As I mentioned, they carry their payloads in custom types too. One that most ebs-dba’s will have seen is WF_NOTIFICATION_OUT. When the workflow engine generates a notification, it will be stored in ‘WF_NOTIFICATIONS’. However, a message will be posted on ‘WF_NOTIFICATION_OUT’ too. This message will be read by the workflow notification mailer, which will use it to get the relevant data from WF_NOTIFICATIONS.
If you haven’t run the notification mailer for a long time, you might want to clean up WF_NOTIFICATION_OUT a bit. There is a script available from Metalink to do the job. (There is also a quicker but unsupported way). But you might want to see the records in WF_NOTIFICATION_OUT related to WF_NOTIFICATIONS to decide if a cleanup is appropriate.
Let’s look at WF_NOTIFICATION_OUT:
Name Null? Type ----------------------- -------- ---------------- Q_NAME VARCHAR2(30) MSGID NOT NULL RAW(16) CORRID VARCHAR2(128) PRIORITY NUMBER STATE NUMBER DELAY DATE EXPIRATION NUMBER TIME_MANAGER_INFO DATE LOCAL_ORDER_NO NUMBER CHAIN_NO NUMBER CSCN NUMBER DSCN NUMBER ENQ_TIME DATE ENQ_UID NUMBER ENQ_TID VARCHAR2(30) DEQ_TIME DATE DEQ_UID NUMBER DEQ_TID VARCHAR2(30) RETRY_COUNT NUMBER EXCEPTION_QSCHEMA VARCHAR2(30) EXCEPTION_QUEUE VARCHAR2(30) STEP_NO NUMBER RECIPIENT_KEY NUMBER DEQUEUE_MSGID RAW(16) SENDER_NAME VARCHAR2(30) SENDER_ADDRESS VARCHAR2(1024) SENDER_PROTOCOL NUMBER USER_DATA SYS.AQ$_JMS_TEXT _MESSAGE
The regular AQ-information is there. And our payload in USER_DATA. This time it’s an AQ-type.
Let’s follow the same procedure:
select type_name,typecode,attributes,methods from dba_types where type_name='AQ$_JMS_TEXT_MESSAGE'; TYPE_NAME TYPECODE ATTRIBUTES METHODS ------------------------------ ------------------------------ ---------- ---------- AQ$_JMS_TEXT_MESSAGE OBJECT 4 34
An object type. So let’s see its attributes:
select type_name,attr_name,attr_type_owner,attr_type_name from dba_type_attrs where type_name='AQ$_JMS_TEXT_MESSAGE'; TYPE_NAME ATTR_NAME ATTR_TYPE_OWNER ATTR_TYPE_NAME ------------------------------ ------------------------------ ------------------------------ -------------- AQ$_JMS_TEXT_MESSAGE HEADER SYS AQ$_JMS_HEADER AQ$_JMS_TEXT_MESSAGE TEXT_LEN INTEGER AQ$_JMS_TEXT_MESSAGE TEXT_LOB CLOB AQ$_JMS_TEXT_MESSAGE TEXT_VC VARCHAR2
We can already read the text_len, text_lob and text_vc. The last 2 contain an XML with a reference to the notification. But the information that I want to show you is in the header. This is a type ‘AQ$_JMS_HEADER’. When we check this one, we see that it again is an object with these attributes:
select type_name,typecode,attributes,methods from dba_types where type_name='AQ$_JMS_HEADER'; TYPE_NAME TYPECODE ATTRIBUTES METHODS ------------------------------ ------------------------------ ---------- ---------- AQ$_JMS_HEADER OBJECT 7 31 select type_name,attr_name,attr_type_owner,attr_type_name from dba_type_attrs where type_name='AQ$_JMS_HEADER'; TYPE_NAME ATTR_NAME ATTR_TYPE_OWNER ATTR_TYPE_NAME ------------------------------ ------------------------------ ------------------------------ ------- AQ$_JMS_HEADER APPID VARCHAR2 AQ$_JMS_HEADER GROUPID VARCHAR2 AQ$_JMS_HEADER GROUPSEQ INTEGER AQ$_JMS_HEADER PROPERTIES SYS AQ$_JMS_USERPROPARRAY AQ$_JMS_HEADER REPLYTO SYS AQ$_AGENT AQ$_JMS_HEADER TYPE VARCHAR2 AQ$_JMS_HEADER USERID VARCHAR2
As you can imagine, we need to drill down into ‘PROPERTIES’. The other attributes might or might not contain any data, depending on the notification. properties is a Varray of ‘AQ$_JMS_USERPROPERTY’:
select type_name,typecode,attributes,methods from dba_types where type_name='AQ$_JMS_USERPROPARRAY'; TYPE_NAME TYPECODE ATTRIBUTES METHODS ------------------------------ ------------------------------ ---------- ---------- AQ$_JMS_USERPROPARRAY COLLECTION 0 0 select type_name,coll_type,elem_type_owner,elem_type_name from dba_coll_types where type_name='AQ$_JMS_USERPROPARRAY'; TYPE_NAME COLL_TYPE ELEM_TYPE_OWNER ELEM_TYPE_NAME ------------------------------ ------------------------------ ------------------------------ -------------------- AQ$_JMS_USERPROPARRAY VARYING ARRAY SYS AQ$_JMS_USERPROPERTY
One more level to check:
select type_name,typecode,attributes,methods
from dba_types
where type_name='AQ$_JMS_USERPROPERTY';
TYPE_NAME TYPECODE ATTRIBUTES METHODS
------------------------------ ------------------------------ ---------- ----------
AQ$_JMS_USERPROPERTY OBJECT 5 0
select type_name,attr_name,attr_type_owner,attr_type_name
from dba_type_attrs
where type_name='AQ$_JMS_USERPROPERTY';
TYPE_NAME ATTR_NAME ATTR_TYPE_OWNER ATTR_TYPE_NAME
------------------------------ ------------------------------ ------------------------------ -------
AQ$_JMS_USERPROPERTY JAVA_TYPE INTEGER
AQ$_JMS_USERPROPERTY NAME VARCHAR2
AQ$_JMS_USERPROPERTY NUM_VALUE NUMBER
AQ$_JMS_USERPROPERTY STR_VALUE VARCHAR2
AQ$_JMS_USERPROPERTY TYPE INTEGER
So let’s see what properties we have. I just query the whole contents of properties for the first row in wf_notification_out (This particular system doesn’t have a WF-mailer running. If it is running, chances are that you won’t have any records in WF_NOTIFICATION_OUT).
select p.*
from (select *
from wf_notification_out
where rownum=1) n
, table(n.user_data.header.properties) p;
NAME TYPE STR_VALUE NUM_VALUE JAVA_TYPE
----------------------- ----- -------------------------------------------- --------- ---------
BES_EVENT_NAME 100 oracle.apps.wf.notification.send 27
BES_EVENT_KEY 100 39388680 27
BES_PRIORITY 200 50 23
BES_SEND_DATE 100 2009/03/06 01:12:23 27
BES_RECEIVE_DATE 100 2009/03/06 01:12:34 27
BES_FROM_AGENT 100 WF_NOTIFICATION_OUT@TESTDB.STIJF.COM 27
BES_ERROR_SUBSCRIPTION 100 C10E7C2EF71253C1E0340800208D03E1 27
NOTIFICATION_ID 100 39388680 27
ROLE 100 FND_RESP535:21704 27
GROUP_ID 100 39388680 27
Q_CORRELATION_ID 100 XDPWFSTD 27
There you go. Among others, the ‘NOTIFICATION_ID’ is there. It maps to the notification_id on ‘WF_NOTIFICATIONS’. And by now, we have seen enough to select it directly in our queries:
select n.msgid,(select str_value
from table(n.user_data.header.properties)
where name='NOTIFICATION_ID') notification_id
from wf_notification_out n
where rownum=1;
MSGID NOTIFICATION_ID
-------------------------------- ---------------
64672D4985E57075E04400144F687CA0 39388680
That concludes this article. Based on the above, you will be able to select the data you want.
New article on custom types available
I just wrote an item about selecting from the special column types in the workflow tables. (for example EVENT_VALUE in WF_ITEM_ATTRIBUTE_VALUE and how to select notification_id from the USER_DATA in WF_NOTIFICATION_OUT)
Didn't have any time to format it in HTML format yet. So for the moment it is only available in PDF format from the link-page and from here.
Enjoy!
Oracle workflow for eBS DBA’s (Part 4b ;-)
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.
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
begin wf_engine.abortprocess(itemtype=>'DBA_TYPE' ,itemkey=>'30' ,process=>'ROOT:DBA_MAIN_PROCESS'); end;
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
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
begin wf_engine.suspend(itemtype=>'DBA_TYPE',itemkey=>'34'); end;
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
begin wf_engine.resume(itemtype=>'DBA_TYPE',itemkey=>'34'); end;
Redoing the first parts of the workflow series
Just to inform you that I’m in the process of reformatting the first parts of the ‘Oracle Workflow for eBS DBA’s’ series.
So there might be some links going wrong. But I’ll make sure the links page always has the correct links. Please check back there, if you think something is wrong.
Also you can leave a comment, of course!
Oracle Workflow for eBS DBA’s (Part 1)
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.
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.
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.
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.
Oracle Workflow for eBS DBA’s (Part 4)
Welcome to Part 4 of the series Oracle Workflow for eBS DBA’s.
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');
QUEUE CORR_ID PRIO MSG_STATE ENQ_TIME TYPE KEY ACTID CONSUMER_NAME ------------------- ------------ ---- --------- ------------------ -------- --- ------ ------------- WF_DEFERRED_QUEUE_M APPSDBA_TYPE 1 READY 24-7-2009 14:02:44 DBA_TYPE 14 245513 APPS
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:
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE DBA_CONTROL_PROCESS 0 25-7-2009 17:26:05 25-7-2009 17:31:55 COMPLETE #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 25-7-2009 17:31:55 COMPLETE #TIMEOUT 25-7-2009 17:31:05 END-1 0 END 25-7-2009 17:31:55 25-7-2009 17:31:55 COMPLETE #NULL
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');
QUEUE CORR_ID MSG_STATE ENQ_TIME DELAY ITEMTYPE ITEMKEY ACTID WF_DEFERRED_QUEUE_M APPSDBA_TYPE WAIT 25-7-2009 20:23:19 25-7-2009 20:27:39 DBA_TYPE 22 245973
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;
INSTANCE_LABEL KEY FUNCTION BEGIN_DATE END_DATE STATUS RESULT DBA_CONTROL_PROCESS 20 26-7-2009 14:26:37 ACTIVE #NULL START 20 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL XXX_LAUNCH_CHILD 20 xxx_launch_child 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE COMPLETE DBA_MAIN_PROCESS 20-1 26-7-2009 14:26:37 ACTIVE #NULL START 20-1 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL INITIALIZE_FLOW 20-1 XXX_WF_DBA.init 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE COMPLETE COMPARETEXT 20-1 WF_STANDARD.COMPARE 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE EQ CHECK_INVALIDS 20-1 XXX_CHECK_INVALIDS 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE Y DEFER 20-1 WF_STANDARD.DEFER 26-7-2009 14:26:37 DEFERRED #NULL GET_INVALIDS 20-1 XXX_WF_UTILS.get_invalids 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE LOOPCOUNTER 20-1 WF_STANDARD.LOOPCOUNTER 26-7-2009 14:26:38 26-7-2009 14:26:38 COMPLETE EXIT AND 20-1 WF_STANDARD.ANDJOIN 26-7-2009 14:26:38 WAITING WAITFORFLOW 20 WF_STANDARD.WAITFORFLOW 26-7-2009 14:26:38 NOTIFIED
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%'
ITEM_TYPE KEY BEGIN_DATE PARENT_TYPE PARENT_KEY DBA_TYPE 20-1 26-7-2009 14:26:37 DBA_TYPE 20 DBA_TYPE 20 26-7-2009 14:26:37
Now when we run a background engine, and check again:
INSTANCE_LABEL KEY FUNCTION BEGIN_DATE END_DATE STATUS RESULT DBA_CONTROL_PROCESS 20 26-7-2009 14:26:37 26-7-2009 14:38:40 COMPLETE #NULL START 20 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL XXX_LAUNCH_CHILD 20 xxx_launch_child 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE COMPLETE DBA_MAIN_PROCESS 20-1 26-7-2009 14:26:37 26-7-2009 14:38:40 COMPLETE #NULL START 20-1 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL INITIALIZE_FLOW 20-1 XXX_WF_DBA.init 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE COMPLETE COMPARETEXT 20-1 WF_STANDARD.COMPARE 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE EQ CHECK_INVALIDS 20-1 XXX_CHECK_INVALIDS 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE Y GET_INVALIDS 20-1 XXX_WF_UTILS.get_invalids 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE LOOPCOUNTER 20-1 WF_STANDARD.LOOPCOUNTER 26-7-2009 14:26:38 26-7-2009 14:26:38 COMPLETE EXIT WAITFORFLOW 20 WF_STANDARD.WAITFORFLOW 26-7-2009 14:26:38 26-7-2009 14:38:40 COMPLETE #NULL DEFER 20-1 WF_STANDARD.DEFER 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL AND 20-1 WF_STANDARD.ANDJOIN 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL CONTINUEFLOW 20-1 WF_STANDARD.CONTINUEFLOW 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL END 20 END 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL END-1 20-1 END 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL
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.
Workflow for eBS DBA’s. (Part 1)
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 ItemtypeI 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.