How to clean up Instance data for a given BPEL Process Instance ID from Apache ODE/WSO2 BPS

This is a stored procedure that can be used to delete all the instance related data from the DB for a particular BPEL instance.

	create or replace
	PROCEDURE cleanInstance(instanceID NUMBER) AS
	BEGIN
	DELETE FROM ODE_EVENT WHERE INSTANCE_ID=instanceID;
	DELETE FROM ODE_CORSET_PROP WHERE CORRSET_ID IN (SELECT cs.CORRELATION_SET_ID FROM ODE_CORRELATION_SET cs WHERE cs.SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID=instanceID));
	DELETE FROM ODE_CORRELATION_SET WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID=instanceID);
	DELETE FROM ODE_PARTNER_LINK WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID=instanceID);
	DELETE FROM ODE_XML_DATA_PROP WHERE XML_DATA_ID IN (SELECT xd.XML_DATA_ID FROM ODE_XML_DATA xd WHERE xd.SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID=instanceID));
	DELETE FROM ODE_XML_DATA WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID=instanceID);
	DELETE FROM ODE_SCOPE WHERE PROCESS_INSTANCE_ID=instanceID;
	DELETE FROM ODE_MEX_PROP WHERE MEX_ID IN (SELECT mex.MESSAGE_EXCHANGE_ID FROM ODE_MESSAGE_EXCHANGE mex WHERE mex.PROCESS_INSTANCE_ID=instanceID);
	DELETE FROM ODE_MESSAGE WHERE MESSAGE_EXCHANGE_ID IN (SELECT mex.MESSAGE_EXCHANGE_ID FROM ODE_MESSAGE_EXCHANGE mex WHERE mex.PROCESS_INSTANCE_ID=instanceID);
	DELETE FROM ODE_MESSAGE_EXCHANGE WHERE PROCESS_INSTANCE_ID=instanceID;
	DELETE FROM ODE_MESSAGE_ROUTE where PROCESS_INSTANCE_ID=instanceID;
	DELETE from ODE_PROCESS_INSTANCE where ID=instanceID;
	end;
	/

view raw gistfile1.sql hosted with ❤ by GitHub