Tuesday, April 20, 2010

Purge SOA composite instances in 11.1.1.2

You want to get rid of ALL SOA composite instances of any state?

Here is the SQL you should run in the SOAINFRA schema.

DECLARE
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES NUMBER;
DELETED_INSTANCES NUMBER;
SUM_DELETED_INSTANCES NUMBER:=0;
BEGIN

FILTER.COMPOSITE_NAME := 'YourCompositeName';
FILTER.COMPOSITE_REVISION := '1.0';

FILTER.MIN_CREATED_DATE := to_timestamp('1980-01-01','YYYY-MM-DD');
FILTER.MAX_CREATED_DATE := to_timestamp('2020-01-01','YYYY-MM-DD');

FILTER.STATE := 0;
MAX_INSTANCES := 100;

LOOP
LOOP
DBMS_OUTPUT.PUT_LINE('*** STATE: '||FILTER.STATE);
DELETED_INSTANCES := FABRIC.DELETE_ALL(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES
);
DBMS_OUTPUT.PUT_LINE('*** PURGED INSTANCES: '||DELETED_INSTANCES);
SUM_DELETED_INSTANCES:=DELETED_INSTANCES+SUM_DELETED_INSTANCES;
EXIT WHEN DELETED_INSTANCES=0;
END LOOP;
FILTER.STATE:=FILTER.STATE+1;
EXIT WHEN FILTER.STATE>6;
END LOOP;

DBMS_OUTPUT.PUT_LINE('*** SUM PURGED INSTANCES: '||SUM_DELETED_INSTANCES);

END;
/

No comments:

Post a Comment