Tag Archives: script

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;

8)      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.

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.

 
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.
 

More on 1 != 1

I was reading the WorkflowFaq Blog today, where they discuss when 1 != 1 in Oracle.

Of I did some testing with this. First of course I reproduced the issue easy enough:

 

SQL> select 1/3*3 from dual;

 

     1/3*3

----------

         1

 

SQL> select * from dual where 1/3*3=1;

No rows selected.

It is obviously a rounding issue. So what happens if we force Oracle to round the result?

 

SQL> select * from dual where round(1/3*3)=1;

 

D

-

X

 

If the rounding went wrong, it will most likely be on the low side (0.3333……*3=0.9999……)

 

SQL> select trunc(1/3*3) from dual where ceil(1/3*3)=1;

 

D

-

0

 

We can see the real contents by dumping the result:

 

SQL> select dump(1/3*3) d from dual
  2  union
  3  select dump(1) d from dual;

 

D

--------------------------------------------------------------------------------

Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10

0,100,100,100,100

 

Typ=2 Len=2: 193,2 

I will go into the internal number format in a while. For now, it is sufficient to say that the 1/3*3 is indeed 0.9999999…….

Now how many decimals can we get?

 

SQL> select to_char(1/3*3,'9.99') from dual;

 

TO_CH

-----

 1.00

 

SQL> select to_char(1/3*3,'9.99999999999999999999999999999999999999999') from dual;

 

TO_CHAR(1/3*3,'9.999999999999999999999999999

--------------------------------------------

  .99999999999999999999999999999999999999990

 

There is the real value of 1/3*3.

The Oracle documentation says that the precision of the number format is 126 binary digits, approximately 38 decimals digits.

So when we force a mask with more decimals, Oracle will show the real value.

Àn interesting 'feature'. And definitely something to keep in mind.

One last note on the dumping of numbers.
dump(<number>,10) will dump the decimal value.
dump(<number>,16) will dump the hexadecimal value.

How do we translate it back to the real value then?
Let's do the excercise for a few numbers: 1234.5678 and -1234.5678.

There is some difference between positive and negative values. So let's do the positive one first.

 

SQL> select dump(1234.5678,10),dump(1234.5678,16) from dual;

 

DUMP(1234.5678,10)           DUMP(1234.5678,16)

---------------------------- --------------------------

Typ=2 Len=5: 194,13,35,57,79 Typ=2 Len=5: c2,d,23,39,4f

 

We start with the second to the last numbers. And we subtract 1:
194,12,34,56,78

Ignore the 194 for now, and put the decimal . after the second number: 12.345678

Now subtract 193 from the first value: 194-193=1.

Multiply the decimal value this number of times by 100: 12.345678 * 100 = 1234.5678.
(In our case we multiply by 100 once if the first value would be higher, mulitply more often).

The negative values are a little different:

 

SQL> select dump(-1234.5678,10),dump(-1234.5678,16) from dual;

 

DUMP(-1234.5678,10)             DUMP(-1234.5678,16)

------------------------------- ------------------------------

Typ=2 Len=6: 61,89,67,45,23,102 Typ=2 Len=6: 3d,59,43,2d,17,66

 

The 102 at the end means it is a negative value. Discard the 102 for the rest of the calculation.

61,89,67,45,23.

Subtract the second to the last number from 101.
101-89=12, 101-67=34, 101-45=56,101-23=78  gives
61,12 34 56 78

Put the decimal . after the second number. So we get 12.345678

Now subtract 62 from the first value and multiply by 100: 61-62 = -1.

Finally we divide the value this number of times by 100. In our case we have a negative, so we
multiply instead:

12.345678 * 100 = 1234.5678 -> And we had a negative. So -1234.5678