declare
-- Script Parameters:
-- Composite Name
cmpstName VARCHAR2(200) := 'TestZComposite';
-- Composite Version
cmpstVersion VARCHAR(20) := '1.2';
-- Composite old distinguished name
old_composite_dn VARCHAR2(500) := 'default/TestZComposite!1.2*soa_87ad6db9-5a3e-4e9a-8940-f9b2885121b4';
-- Composite new distinguished name (new mds label => soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97)
new_composite_dn VARCHAR2(500) := 'default/TestZComposite!1.2*soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97';
-- Composite new MDS label
new_mds_label VARCHAR2(200) := 'soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97';
begin
-- Step 1
-- update compositedn (new mds label) and reactivate stale composites
-- (state 64 = stale, state 0 = running)
update composite_instance ci
set ci.composite_dn = new_composite_dn,
ci.state = CASE
WHEN (ci.state is not null and ci.state = 64)
THEN 0
ELSE ci.state
END
where ci.composite_dn = old_composite_dn;
-- Step 2
-- update mds label and compositedn in triggered messages
update dlv_message dlvm
set dlvm.composite_label = new_mds_label,
dlvm.res_subscriber = CASE
WHEN dlvm.res_subscriber IS NOT NULL
THEN replace(dlvm.res_subscriber,
old_composite_dn,
new_composite_dn)
ELSE dlvm.res_subscriber
END
where dlvm.composite_name = cmpstName and
dlvm.composite_revision = cmpstVersion;
-- Step 3
-- reactivate callbacks and update mds label and compositedn
-- (state 3 = stale/cancelled, state 0 = unresolved)
update dlv_subscription dlv
set dlv.composite_label = new_mds_label,
dlv.subscriber_id = replace(dlv.subscriber_id, old_composite_dn, new_composite_dn),
dlv.state = CASE
WHEN (dlv.state IS NOT NULL AND dlv.state = 3)
THEN 0
ELSE dlv.state
END
where dlv.composite_name = cmpstName and
dlv.composite_revision = cmpstVersion;
-- Step 4
-- reactivate references and update compositedn
-- (state 0 = successful, state 6 = stale)
update reference_instance t
set t.state = 0,
t.composite_dn = new_composite_dn
where t.composite_dn = old_composite_dn and t.state = 6;
-- Step 5
-- reactivate bpel/bpmn processes and update compositedn
-- (state 1 = open running, state 9 = closed stale)
update cube_instance cui
set cui.composite_label = new_mds_label,
cui.state = CASE
WHEN (cui.state IS NOT NULL AND cui.state = 9)
THEN 1
ELSE cui.state
END
where cui.composite_name = cmpstName and cui.composite_revision = cmpstVersion;
-- Step 6
-- reactivate human tasks and update compositedn
UPDATE WFTASK TBL_NOTFS
SET TBL_NOTFS.STATE = CASE
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_INITIATED')
THEN 'ASSIGNED'
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_WITHDRAWN')
THEN 'WITHDRAWN'
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_REASSIGNED')
THEN 'ASSIGNED'
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_ALERTED')
THEN 'ALERTED'
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_SUSPENDED')
THEN 'SUSPENDED'
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_ERROR')
THEN 'ERRORED'
WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_COMPLETED')
THEN NULL
ELSE TBL_NOTFS.STATE
END,
TBL_NOTFS.COMPOSITEDN = new_composite_dn,
TBL_NOTFS.MDSLABEL = new_mds_label
WHERE TBL_NOTFS.TASKDEFINITIONNAME is not null
AND TBL_NOTFS.STATE = 'STALE'
AND TBL_NOTFS.COMPOSITENAME= cmpstName
and TBL_NOTFS.COMPOSITEVERSION = cmpstVersion;
end
Ref:
http://middlewarebylink.wordpress.com/2012/10/29/how-to-recover-stale-composite-instances-and-their-components/
https://blogs.oracle.com/ateamsoab2b/entry/list_of_all_states_from