Data base operation on BPEL task

some times we need to perform some operation on bpel task from dataabase,

below are some useful queries for same

I want to delete BPEL process instances along with any related Human Workflow data from the dehydration store.

I took the first part from the collaxa package in the ORABPEL schema, then added the rest for human workflow cleanup.

procedure delete_ci_wf( p_cikey in integer )
as
cursor c_wftask (v_cikey in cube_scope.CIKEY%TYPE)is
select taskid
from wftask
where instanceid = v_cikey;

v_taskid wftask.TASKID%TYPE;

begin
-- Delete the cube instance first
--
delete from cube_instance where cikey = p_cikey;

-- Then cascade the delete to other tables with references
--
delete from cube_scope where cikey = p_cikey;
delete from work_item where cikey = p_cikey;
delete from wi_exception where cikey = p_cikey;
delete from scope_activation where cikey = p_cikey;
delete from dlv_subscription where cikey = p_cikey;
delete from audit_trail where cikey = p_cikey;
delete from audit_details where cikey = p_cikey;
delete from sync_trail where cikey = p_cikey;
delete from sync_store where cikey = p_cikey;
delete from test_details where cikey = p_cikey;
delete from document_ci_ref where cikey = p_cikey;

-- Then cascade the delete to the human workflow tables
-- with references to this instance
--
-- cube_instance cikey = wftask.instanceid

OPEN c_wftask (p_cikey);
LOOP
FETCH c_wftask into v_taskid;
EXIT WHEN c_wftask%NOTFOUND;
delete from wftaskhistory where taskid = v_taskid;
delete from wfassignee where taskid = v_taskid;
delete from wfattachment where taskid = v_taskid;
delete from wfcomments where taskid = v_taskid;
delete from wfmessageattribute where taskid = v_taskid;
delete from wfnotification where taskid = v_taskid;
delete from wfnotificationmessages where taskid = v_taskid;
delete from wfroutingslip where taskid = v_taskid;
delete from wftasktimer where taskid = v_taskid;
END LOOP;

delete from wftask where instanceid = p_cikey;
commit;
end delete_ci_wf;

Naturally for a comprehensive purge I will have to delete the stale instances from invoke_message, dlv_message , xml_document etc.