Delete single or multiple esb instance

From time to time you may need to delete stale esb instances. I had difficulty in finding a script that does it. But actually you can use oracle provided purge scripts and modify to be used only for single or multiple instance deletion. See scripts below it will delete single esb instance

DELETE FROM ESB_ACTIVITY
WHERE ID IN
(SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));



DELETE ESB_TRACKING_FIELD_VALUE
WHERE ACTIVITY_ID IN
(SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

--

DELETE FROM ESB_FAULTED_INSTANCE
WHERE ACTIVITY_ID IN
(SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

--

DELETE FROM ESB_TRANSACTION_STATUS
where Flow_id = 'instance Id'

--

DELETE FROM ESB_INSTANCE_RELATION_XML
WHERE FLOW_ID IN
(SELECT FLOW_ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE Flow_id = 'instance Id'
AND A.FLOW_ID = B.FLOW_ID));

--

DELETE FROM ESB_RELATION_XML A
WHERE NOT EXISTS
(SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML);

--

DELETE ESB_SERVICE_RELATION
WHERE RELATION_XML_ID IN
(SELECT ID
FROM ESB_RELATION_XML
WHERE NOT EXISTS
(SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML
WHERE ID = RELATION_XML_ID));
commit;