How to serch composites for BPEL process defined index values in soa suite 11g

Problem:

I have migrated my 10g BPEL processes to 11g. Few BPEL processes, based on input values they receive, sets the 1 to 6 index values. For a process where instance volume is higher/large, it is easy to search the instance using the index value. In 10g, I used to search on BPEL Console --> Instances tab, using "Index/Custom Key" text box. But as soon as I went to 11g, I found there is no option on em(Enterprise Manager) console to search BPEL instance using index.

After research, I found out that in 11g Oracle has not provided option to serach BPEL instance using those indexes. You can search for composites using composite indexes, but not the BPEL instances using BPEL process defined indexes. However, the values are still being stored in CI_INDEXS table under soainfra database schema.

Some simple but very useful 10g functionality is omitted even after the release of 11.1.1.4 release.

solution:

Simplest way, fire a query and get the instance ID from the ci_indexes table. But wait, it is not that easy. Let us see what exactly happens there.

I have a composite TestCompsite1. My bpel process TestHelloWorld is part of the composite TestComposite1. During execution, the bpel process setting values in ci_indexes table, say index_1 = "OracleWorld".

You can search the ci_indexes table using below query

select * from ci_indexes where index_1 like 'OracleWorld' (Add more OR conditions as you may want to search on other index keys)

You will get bpel instance number as cikey column value. But you cannot search using that output on em console as it only allows you to search based on the compsoite instance number of composite instance index values. The solution is below query, join ci_indexes with cube_instance

select a.cmpst_id cube_composite_id, a.cikey cube_cikey, b.cikey index_cikey

from cube_instance a, ci_indexes b

where a.cikey = b.cikey

and index_1 like 'OracleWorld'

the column value of cube_composite_id is what exactly you will search on em console for. This will open composite. If you have multiple bpel execution, then enable "show Instance ID" option. Use the index_cikey column value to reach the correct bpel instance.

I thought this would be easy when I saw values in ci_indexes table, but it required to look further in soainfra db. For simplicity, one may like to wrap this query in report to make the life easier.

Hope this will help

Cheers

Nirav