B2B Purge Script (B2B_PURGE_INSTANCE_MSGS) is not Deleting Instances after SOA Upgrade to 11.1.1.7 (Doc ID 1903590.1)

Last updated on MAY 15, 2023

Applies to:

Symptoms

On : 11.1.1.7.0 version, Product Installation

ACTUAL BEHAVIOR
---------------
B2B purge script developed with anonymous block that calls purge procedure "B2B_PURGE_INSTANCE_MSGS" is not deleting instances after upgrading from 11.1.1.6 to 11.1.1.7.3.

Following is the anonymous block

~~~~~~~~~
declare
p_bfr_purge number := NULL;
p_aftr_purge number := NULL;

begin
ECTH_B2B_PURGE_PRC(p_bfr_purge,p_aftr_purge);

DBMS_OUTPUT.PUT_LINE ('p_bfr_purge: '||p_bfr_purge);
DBMS_OUTPUT.PUT_LINE ('p_aftr_purge: '||p_aftr_purge);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('SQLCODE: '||SQLCODE);

end;
~~~~~~~~~~~

And following is the customer's procedure that calls out of box procedure 'B2B_PURGE_INSTANCE_MSGS'

~~~~~~~~~~~~~~~~~~~~~~~~~
 create or replace
PROCEDURE ECTH_B2B_PURGE_PRC (p_bfr_purge OUT NUMBER,
  p_aftr_purge OUT NUMBER)
  
  
  IS
  MAX_DATE varchar2(20);
  MIN_DATE varchar2(20);
  PURGE_DATE DATE:= sysdate - 8;
  ARCHFILENAME varchar2(20);
  l_count_purge NUMBER :=0;

BEGIN
DBMS_OUTPUT.ENABLE(20000);
 min_date := to_char(purge_date-1, 'YYYY/MM/DD') ||' 00:00:00';
 max_date := to_char(purge_date, 'YYYY/MM/DD') ||' 23:59:59';
 archfilename := to_char(purge_date, 'MMDDYYYY-HH24MMSS');


  SELECT COUNT(*)
  into L_COUNT_PURGE
  FROM MISC_SOAINFRA.b2b_business_message
  where to_timestamp (to_char(CREATED,'YYYY-MM-DD'),'YYYY-MM-DD' ) < to_timestamp(to_char(sysdate-7,'YYYY-MM-DD'),'YYYY-MM-DD' ) ;
 
 
  p_bfr_purge := l_count_purge;

 MISC_SOAINFRA.B2B_PURGE_INSTANCE_MSGS(min_date,max_date,null,null,null,null,null,null,null,'true','true',archfilename,null);


  SELECT COUNT(*)
  into L_COUNT_PURGE
  FROM MISC_SOAINFRA.b2b_business_message
  where to_timestamp (to_char(CREATED,'YYYY-MM-DD'),'YYYY-MM-DD' ) < to_timestamp(to_char(sysdate-7,'YYYY-MM-DD'),'YYYY-MM-DD' ) ;
 
 
  p_aftr_purge := l_count_purge;
 
 COMMIT;
 
 EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE ('Error while purging B2B on : ' ||sysdate||' '||SQLCODE||' '||SQLERRM);
  
  end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

b2bpurge tool provided with product does delete the instances properly.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.