Enable logging in purge process

I guess out of the box 11g SOA instance purge process kinda sucks, and majority of the time we end up customizing it for faster execution or just so that it is easy to read. However, time to time, I end up using OOTB purge process. We were trying to delete it from EM console and somehow just got hung and faced below error in logs.

Caused By: java.lang.NullPointerException
        at sym.productext.ui.bean.CreateDerivationHeaderBean.doDeleteRow(CreateDerivationHeaderBean.java:148)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.sun.el.parser.AstValue.invoke(AstValue.java:187)
        at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
        at org.apache.myfaces.trinidadinternal.taglib.util.MethodExpressionMethodBinding.invoke(MethodExpressionMethodBinding.java:53)
        at org.apache.myfaces.trinidad.component.UIXComponentBase.broadcastToMethodBinding(UIXComponentBase.java:1256)

So I had to try it from database. We ran following standard routine with standard parameters to delete all instances of specific composite, and the process completed in a few seconds - nothing got deleted and no error.

DECLARE
  MIN_CREATION_DATE TIMESTAMP;
  MAX_CREATION_DATE TIMESTAMP;
  BATCH_SIZE NUMBER;
  MAX_RUNTIME NUMBER;
  RETENTION_PERIOD TIMESTAMP;
  PURGE_PARTITIONED_COMPONENT BOOLEAN;
  COMPOSITE_NAME VARCHAR2(200);
  COMPOSITE_REVISION VARCHAR2(200);
  SOA_PARTITION_NAME VARCHAR2(200);
BEGIN
  MIN_CREATION_DATE := systimestamp - 10000;
  MAX_CREATION_DATE := systimestamp;
  BATCH_SIZE := 20000;
  MAX_RUNTIME := 60;
  RETENTION_PERIOD := null;
  PURGE_PARTITIONED_COMPONENT := true;
  COMPOSITE_NAME := 'MyProcess';
  COMPOSITE_REVISION := '1.0';
  SOA_PARTITION_NAME := 'MyPartition';

  SOA.DELETE_INSTANCES(
    MIN_CREATION_DATE => MIN_CREATION_DATE,
    MAX_CREATION_DATE => MAX_CREATION_DATE,
    BATCH_SIZE => BATCH_SIZE,
    MAX_RUNTIME => MAX_RUNTIME,
    RETENTION_PERIOD => RETENTION_PERIOD,
    PURGE_PARTITIONED_COMPONENT => PURGE_PARTITIONED_COMPONENT,
    COMPOSITE_NAME => COMPOSITE_NAME,
    COMPOSITE_REVISION => COMPOSITE_REVISION,
    SOA_PARTITION_NAME => SOA_PARTITION_NAME
  );
--rollback; 
END;                   

Upon looking further in PLSQL, found that it is using log_info and debug_purge to log the messages, but it will only be enabled if $$debug_on is there.

How to enable purge logging

ALTER PROCEDURE debug_purge  COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE' REUSE SETTINGS;

ALTER PROCEDURE log_info COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE' REUSE SETTINGS;

How to disable purge logging

ALTER PROCEDURE debug_purge COMPILE PLSQL_CCFLAGS = 'debug_on:false' REUSE SETTINGS;

ALTER PROCEDURE log_info COMPILE PLSQL_CCFLAGS = 'debug_on:false' REUSE SETTINGS;

The same can be found under $SOA_HOME/rcu/integration/soainfra/oracle/soa_purge/common/debug_on.sql and debug_off.sql.

It gives quite a bit of information on purge routine once it is enabled.