Oracle BPEL 12c Data model changes and FAQ

What is Oracle BPEL dehydration?
Ans: Persisting the state of BPEL process to Database (SOA_INFRA) is called dehydration.


Whats new in Oracle 12c BPEL Dehydration data model ?
Ans: Oracle BPEL 11g holds (dehydrates) composite instance details (master data of composite) in composite_instance table of SOAINFRA, where as in 12c, Oracle merged/integrated OSB (Oracle Service Bus) to the FMW middleware technology stack and introduced some new features in BPEL (like subprocess and templates).  So, to accommodate these changes,  Composite_instance table is defused in 12c and added one more layer (starts with SCA_) of tables.  These new tables holds all OSB, BPM, BPEL, OEP and some more FMW composites and their relations ships.

In BPEL perspective, what are those tables (in 12c) ?
Ans:  sca_entity, sca_flow_instance, sca_flow_to_cmpst

Find composite Names
Ans (Query1):
select a.name, b.id, b.composite, b.revision
from sca_partition a, sca_entity b
where a.id = b.sca_partition_id and
b.type = 'composite' and
b.sub_type is null and
b.state = 'active';

Find composite Instances
Ans (Query2): 
select distinct c.composite_sca_entity_id, count(*)
from sca_flow_instance c
where c.composite_sca_entity_id in (select b.id 
from sca_partition a, sca_entity b
where a.id = b.sca_partition_id and
b.type = 'composite' and
b.sub_type is null and
b.state = 'active') and
trunc(c.created_time) = trunc(sysdate)
group by c.composite_sca_entity_id;
-- In the where condition add a condition c.composite_sca_entity_id = sca_entity.id
-- sca_entity.id is the output of query1

Find Open and Running composite Instances
Ans (Query3):
select distinct c.id, count(*)
from cube_instance a, sca_flow_instance b, sca_entity c, sca_partition d
where d.name = 'lab' and
d.id = c.sca_partition_id and
c.state = 'active' and
c.sub_type is  null and
c.type = 'composite' and
c.id = b.composite_sca_entity_id and
a.flow_id = b.flow_id and
a.state in (0,1) and
trunc(a.creation_date) = trunc(sysdate)
group by c.id;

Find closed Composite Instances
Ans (Query4):
select distinct c.id, count(*)
from cube_instance a, sca_flow_instance b, sca_entity c, sca_partition d
where d.name = 'lab' and
d.id = c.sca_partition_id and
c.state = 'active' and
c.sub_type is  null and
c.type = 'composite' and
c.id = b.composite_sca_entity_id and
a.flow_id = b.flow_id and
a.state in (5,9) and
trunc(a.creation_date) = trunc(sysdate)
group by c.id;

Find faults/suspended/aborted composite invocation count
Ans (Query5):
select distinct c.id, count(*)
from cube_instance a, sca_flow_instance b, sca_entity c, sca_partition d
where d.name = 'lab' and
d.id = c.sca_partition_id and
c.state = 'active' and
c.sub_type is  null and
c.type = 'composite' and
c.id = b.composite_sca_entity_id and
a.flow_id = b.flow_id and
a.state in (2,3,4,6,7,8) and
trunc(a.creation_date) = trunc(sysdate)
group by c.id;

Find unknown composite instances
Ans (Query6):
select distinct c.id, count(*)
from cube_instance a, sca_flow_instance b, sca_entity c, sca_partition d
where d.name = 'lab' and
d.id = c.sca_partition_id and
c.state = 'active' and
c.sub_type is  null and
c.type = 'composite' and
c.id = b.composite_sca_entity_id and
a.flow_id = b.flow_id and
a.state > 9 and
trunc(a.creation_date) = trunc(sysdate)
group by c.id;
--  Get the Ids and veiry whether it is having any entry in sca_common_fault
-- If no entries found in common_fault then, check in cube_instance.

Happy programming ..