Monday, March 1, 2010

BPEL Title WildCard Search

I probably would be blogging on setting title for BPEL processes in non-intrusive way, but have to get through couple of hurdles before I get there. It is quite common practice to set Titles for BPEL process so that you can search from BPELConsole.

e.g. setTitle("Title = " + title); //in embedded Java (you can also look at http://blogs.oracle.com/pt/2009/02/searching_bpel_process_instanc_1.html if you are interested on how to use title in intrusive way)

The problem people complain is that BPEL title search doesn't support wild card search. It only supports exact search. While looking through BPELConsole JSP, I found this was quite easy change it from exact search to wild card search. The query was getting generated as part of $ORACLE_HOME/j2ee/oc4j_soa/applications/orabpel/console/ngInstanceList.jsp. To change exact title search to wild card search, here is the change required:

- In $ORACLE_HOME/j2ee/oc4j_soa/applications/orabpel/console/ngInstanceList.jsp, search for keyword called instanceTitle, you should see something like:

String instanceTitleQ = request.getParameter( "instanceTitle" );
if ( instanceTitleQ != null && instanceTitleQ.length( ) != 0 )
{
buf.setLength( 0 );
tmpWhere.setClause( buf.append( n++ > 0 ? " AND " : "" )
.append( SQLDefs.AL_ci_title )
.append( " = ? " )
.toString() );
tmpWhere.setString( 1, instanceTitleQ );
where.append( tmpWhere );
}

You can change it to:

String instanceTitleQ = request.getParameter( "instanceTitle" );
if ( instanceTitleQ != null && instanceTitleQ.length( ) != 0 )
{
buf.setLength( 0 );
tmpWhere.setClause( buf.append( n++ > 0 ? " AND " : "" )
.append( SQLDefs.AL_ci_title )
.append( " LIKE ? " )
.toString() );
tmpWhere.setString( 1, instanceTitleQ );
where.append( tmpWhere );
}


Once it is changed, you can search for Title in BPELConsole using % as wild card character.

9 comments:

Manish and Friends.. said...

Awesome

Manish and Friends.. said...

Chintan,

I have observed that though this is an excellent way to help debug instances easily, it actually puts load on DB as in "Like" is considerably heavier than "=".
Just a thought... :)

Chintan Shah said...

I would certainly agree with that. In addition to that, I saw that in 10.1.3.5, oracle completely removed index on title column which ends up in Full table scan. So if you have index on this column and keep the stats current (nightly basis), it should be pretty fast.

Sanjay Banerjee said...

Chintan,

How do you redeploy the new console code?

Thank you for any pointers
Sanjay

Unknown said...

Hi Chintan,

I tried this wild card search option and its worked. But due to some reasons I rolled back the changes but now when I am again trying to make the same changes.
Its not working.
Is there any clue?

Carlos said...

Hi, Harish.

Your change was rolled back because you make a redeploy of orabpel.jar (maybe in a pacth apply).

There is a Oracle Support Note about this "feature": How to support wildcard searches for BPEL instance titles in BPEL console [ID 1425616.1] or Search By Instance Title with wildcard does not Work from BPEL Console(1390794.1)

Regards,
Carlos

Bramhendra said...

Hi Chintan,

I have followed above steps for searching debugging with wild card character in BPELConsole in title field but it is not working,still I have restarted server node by node.

Please let me know what step have I done wrong?

Bramhendra said...

Hi Chintan,

I have followed above steps for searching debugging with wild card character in BPELConsole in title field but it is not working,still I have restarted server node by node.

Please let me know what step have I done wrong?

Bramhendra said...
This comment has been removed by the author.