BPEL process states

Why Identify states?

To identify the states of the BPEL processes from the database can be quite handy while for housekeeping jobs in production and building some bespoke application to monitor BPEL servers.

Where to find?

Well the the states of the BPEL processes can be found from the database.
This is what I do and I prefer as it gives a lot more control of what you like to see and customize.

Core table and columns

Well the core table to look at is "ORABPEL"."CUBE_INSTANCE"

The states of the BPEL processes are stores in "STATE" column.
This is defined as INTEGER in the database and does not help in first glance, unless it is know what each state means.

What do the states mean?

Well this is what each integer in the "STATE" column mean they mean:
0 - INITIATED in BPEL
1 - OPEN and RUNNING in BPEL
2 - OPEN and SUSPENDED in BPEL
3 - OPEN and FAULTED in BPEL
4 - CLOSED and PENDING_CANCEL in BPEL
5 - CLOSED and COMPLETED in BPEL
6 - CLOSED and FAULTED in BPEL
7 - CLOSED and CANCELLED in BPEL
8 - CLOSED and ABORTED in BPEL
9 - CLOSED and STALE in BPEL
ELSE UNKNOWN in BPEL

Sample Query

A sample query to find count of BPEL process in various states with in start and end date
=================== Query START ======================
SELECT
CASE state
WHEN 0 THEN 'initiated'
WHEN 1 THEN 'open.running'
WHEN 2 THEN 'open.suspended'
WHEN 3 THEN 'open.faulted'
WHEN 4 THEN 'closed.pending_cancel'
WHEN 5 THEN 'closed.completed'
WHEN 6 THEN 'closed.faulted'
WHEN 7 THEN 'closed.cancelled'
WHEN 8 THEN 'closed.aborted'
WHEN 9 THEN 'closed.stale'
ELSE 'unknown'
END state_text,
COUNT (1)
FROM orabpel.cube_instance
WHERE creation_date < (:startdate) AND creation_date > (:enddate)
GROUP BY state;
=================== Query END ======================