Using "purge_esb_instances.sql" to purge ESB 10g instances from the dehydration store

I can't find my own purge script that I created to purge Oracle ESB 10g instances from the dehydration store, so I'll be referencing a script that can be found on http://orasoa.blogspot.com/2008/03/purging-esb-instances.html.

I have not gone through and tested this myself, but it looks pretty valid. So proceed at your own risk (but Marc is top-notch, so you'll be fine). The script below is of a slightly different format from what you'll find on Marc's blog, but the content is identical.

REM
REM AUTHOR:         http://orasoa.blogspot.com/2008/03/purging-esb-instances.html
REM
REM SCRIPT NAME:    purge_esb_instances.sql
REM
REM DESCRIPTION:    This script purges ESB instance data from the dehydration store
REM
REM EXAMPLE:        sqlplus oraesb/pwd@database
REM                 EXEC purge_esb_instances(10);   -- to purge 10 days worth of data
REM                 COMMIT;
REM

CREATE OR REPLACE PROCEDURE purge_esb_instances (p_older_than IN NUMBER DEFAULT '999') IS

  diff NUMBER;

BEGIN

  diff := ((TRUNC(SYSDATE)-p_older_than) - TO_DATE('01/01/1970','MM/DD/YYYY'))*24*60*60*1000;

  DELETE esb_activity WHERE id IN (
    SELECT id
    FROM   esb_activity a
    WHERE EXISTS (
      SELECT flow_id
      FROM   esb_activity b
      WHERE  timestamp < diff
      AND    a.flow_id = b.flow_id
    )
  );

  DELETE esb_tracking_field_value WHERE activity_id IN (
    SELECT id
    FROM   esb_activity a
    WHERE EXISTS (
      SELECT flow_id
      FROM   esb_activity b
      WHERE  timestamp < diff
      AND    a.flow_id = b.flow_id
    )
  );

  DELETE FROM esb_faulted_instance
  WHERE ACTIVITY_ID IN (
    SELECT id
    FROM esb_activity a
    WHERE EXISTS (
      SELECT flow_id
      FROM   esb_activity b
      WHERE  timestamp < diff
      AND    a.flow_id = b.flow_id
    )
  );

  DELETE FROM esb_transaction_status
  WHERE TIMESTAMP < diff;

  DELETE FROM esb_instance_relation_xml
  WHERE flow_id IN (
    SELECT flow_id
    FROM   esb_activity a
    WHERE  EXISTS (
      SELECT flow_id
      FROM   esb_activity b
      WHERE  timestamp < diff
      AND    a.flow_id = b.flow_id
    )
  );

  IF (SQL%ROWCOUNT >0) THEN

    DELETE FROM esb_relation_xml a
    WHERE NOT EXISTS (
      SELECT relation_xml_id
      FROM   esb_instance_relation_xml
    );

    DELETE esb_service_relation
    WHERE relation_xml_id IN (
      SELECT id
      FROM   esb_relation_xml
      WHERE  NOT EXISTS (
        SELECT relation_xml_id
        FROM   esb_instance_relation_xml
        WHERE  id = relation_xml_id
      )
    );

  END IF;
END;
/