Monthly Archives: February 2010

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: 




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’;

---------	------------	-------------------	----------------	--------------
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      PRIORITY         				NUMBER
APPS     	WF_EVENT_T     	SEND_DATE        				DATE

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;


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';

--------- -------------------	-------------------	----------- -------	
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';

--------- ------------------- ---------	---------- ----------
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';

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

from   wf_item_attribute_values v
,      table(v.event_value.parameter_list) t
Where  v.event_value IS NOT NULL 
And    ROWNUM = 1;



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: 

| 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)
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'TASK_ID' ) task_id
from   wf_item_attribute_values v
where  v.event_value is not null
and    rownum=1;

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

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



Name                    Null?    Type
----------------------- -------- ----------------
Q_NAME                           VARCHAR2(30)
MSGID                   NOT NULL RAW(16)
CORRID                           VARCHAR2(128)
PRIORITY                         NUMBER
STATE                            NUMBER
DELAY                            DATE
EXPIRATION                       NUMBER
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_QUEUE                  VARCHAR2(30)
STEP_NO                          NUMBER
RECIPIENT_KEY                    NUMBER
DEQUEUE_MSGID                    RAW(16)
SENDER_NAME                      VARCHAR2(30)
SENDER_ADDRESS                   VARCHAR2(1024)
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( p;

NAME	              	TYPE  STR_VALUE	                                   NUM_VALUE JAVA_TYPE                             
----------------------- ----- -------------------------------------------- --------- ---------
BES_EVENT_NAME	      	  100                              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( 
		where  name='NOTIFICATION_ID') notification_id
from   wf_notification_out n
where  rownum=1;

-------------------------------- ---------------
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