BPEL: Purging Instances-2

INTRODUCTION:

This procedure purges instance for a particular domain,version and between specified period for any state other than state 0 and 1 from the dehydration store.
Note:This procedure is calling another procedure 'delete_ci'.Procedure 'delete_ci' deletes a cube instance and all rows in other Collaxa tables that reference the cube instance.

-- ** It is possible to purge the instances all at once, but by doing that it will remove all the instances. This is nice during development. But in a production environment you want to have a controlled way. This procedure will do the work.

Guidlines for using this code:
1. Don't call this procedure until you are too sure of deleting the instances.
2. First stop the BPEL instance via Enterprise manager then run this procedure.
3. For running the code, pass the date fields in oracle standard format i.e DD-MON-RR format.

CODE:
CREATE OR REPLACE PROCEDURE sp_purge_instances_per_process (
p_domain_name IN VARCHAR2,
p_process_name IN VARCHAR2,
p_version IN VARCHAR2,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2
)
/*

CODING BY: ABHISHEK SAURABH
CREATION DATE: 15-OCTOBER-2007
LAST MODIFIED: 15-OCTOBER-2007

*/
IS
CURSOR c (
v_domain_name IN VARCHAR2,
v_process_name IN VARCHAR2,
v_version IN VARCHAR2,
v_from_date IN VARCHAR2,
v_to_date IN VARCHAR2
)
IS
SELECT cie.cikey cikey, dmn.domain_id domain_id,
cie.process_id process_id, cie.revision_tag revision_tag,
cie.creation_date creation_date, cie.domain_ref domain_ref
FROM cube_instance cie, domain dmn
WHERE cie.domain_ref = dmn.domain_ref
---- the name of the domain
AND upper(dmn.domain_id)=upper(v_domain_name)
AND cie.creation_date between v_from_date and v_to_date
AND upper(cie.process_id)=upper(v_process_name)
AND cie.revision_tag LIKE v_version;
BEGIN
FOR r IN c (p_domain_name, p_process_name, p_version, p_from_date,p_to_date)
LOOP
DBMS_OUTPUT.put_line ( 'Purge '
|| r.process_id
|| '('
|| r.revision_tag
|| ')'
|| ' created on '
|| TO_CHAR (r.creation_date,
'YYYY-MM-DD HH24:MI:SS')
);
collaxa.delete_ci (r.cikey);

-- -- also workflow tasks --
DELETE FROM wftask wfn
WHERE wfn.instanceid = r.cikey;
END LOOP;
-- invoked messages

DELETE FROM invoke_message ime
WHERE ime.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND ime.state > 1
AND upper(ime.process_id)=upper(p_process_name)
AND ime.revision_tag LIKE p_version
AND ime.receive_date between p_from_date and p_to_date;

DBMS_OUTPUT.put_line ('-> #invoke msg ' || SQL%ROWCOUNT);

-- callback messages
DELETE FROM dlv_message dme
WHERE dme.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND dme.state > 1
AND upper(dme.process_id)=upper(p_process_name)
AND dme.revision_tag LIKE p_version
AND dme.receive_date between p_from_date and p_to_date;

DBMS_OUTPUT.put_line ('-> #callback msg ' || SQL%ROWCOUNT);
END sp_purge_instances_per_process;


It seems...coding has become my habit. :))