How to purge BPEL process instances manually?

Oracle(R) BPEL Process Manager - Version: 10.1.2.0.2
Information in this document applies to any platform.
Goal
How to purge BPEL process instances manually?


Solution
Please follow the steps below to purge BPEL instances.

1. Make a back up of your dehydration store.

2. Create the script purge_instances.sql :

Create or Replace procedure
purge_instances( p_older_than in timestamp)
as
cursor c_cube_instance(p_date timestamp) is
select cikey
from cube_instance
where state >= 5 and modify_date < p_date;

cursor c_invoke_message(p_date timestamp) is
select message_guid
from invoke_message
where state > 1 and receive_date < p_date;

cursor c_dlv_message(p_date timestamp) is
select message_guid
from dlv_message
where state > 1 and receive_date < p_date;
begin
-- Delete all closed instances older than specified date
--
for r_cube_instance in c_cube_instance(p_older_than)
loop
collaxa.delete_ci( r_cube_instance.cikey );
end loop;

-- Purge all handled invoke_messages older than specified date
--
for r_invoke_message in c_invoke_message(p_older_than)
loop
delete from invoke_message_bin
where message_guid = r_invoke_message.message_guid;

delete from invoke_message
where message_guid = r_invoke_message.message_guid;
end loop;

-- Purge all handled callback messages older than specified date
--
for r_dlv_message in c_dlv_message(p_older_than)
loop
delete from dlv_message_bin
where message_guid = r_dlv_message.message_guid;

delete from dlv_message
where message_guid = r_dlv_message.message_guid;
end loop;

end purge_instances;
/

3. Connect to SQLPlus as user orabpel and run the script.
For Example:
Declare
P_OLDER_THAN Timestamp;
Begin
P_OLDER_THAN := '05/SEP/06 09:05:00 PM';

PURGE_INSTANCES(P_OLDER_THAN => P_OLDER_THAN);
End;