Querying the BPEL Process Manager instance table

When you have tens of thousands of instances logged and audited in your BPEL server, navigating the instances within BPEL Control (the BPEL console) becomes unrealistic.

You can use SQL to query the instance table in the ORABPEL schema in Oracle BPEL Process Manager 10g (10.1.3.4) to narrow down your search.

SELECT process_id, DECODE(TO_CHAR(state),'1','In-flight','5','Success','6','Warning','9','Stale','8','Error',TO_CHAR(state)) state, creation_date, revision_tag, modify_date-creation_date duration, SUBSTR(REGEXP_SUBSTR(title, '[^ ]+', 1, 2), 2) instance_id FROM cube_instance ORDER BY creation_date DESC;

Here is an example of the output generated by the query above:

PROCESS_ID STATE CREATION_DATE REVISION_TAG DURATION INSTANCE_ID ---------- ----- ---------------------------- ------------ -------------------------- ----------- HelloWorld Error 27-MAY-09 10.30.01.421000 PM 1.0 +000000000 00:00:00.016000 200003 HelloWorld Stale 27-MAY-09 10.27.20.437000 PM 1.0 +000000000 00:00:01.781000 200001 HelloWorld Success 26-MAY-09 10.46.08.593000 PM 1.0 +000000000 00:07:24.969000 190003

To explain a few things regarding the query:

• The STATE column stores values of 1 (In-flight), 5 (Success), 6 (Warning), 8 (Error), and 9 (Stale). Stale processes are those that have been undeployed.
• The DURATION column is calculated by substracting the CREATION_DATE from the MODIFY_DATE. The CREATION_DATE is when the process was instantiated, and the MODIFY_DATE is when the last activity in the process was executed.
• Per the SQL statement above, the INSTANCE_ID is actually parsed from the TITLE column.

With this query, you now have the power to easily access your BPEL instance information.