rss
SOATUBE
Oracle
Custom Search SOABYTE here

Wednesday, March 15, 2017

Purge AIA Message Resubmission Fault Reference Data

During AIA message re-submission, the tables AIA_ERROR_MONITOR & AIA_ERROR_FLEX_FIELD stores the faulted instances referenced date. After the re-submission, these data can be safely purged.
First the records in child table 'AIA_ERROR_FLEX_FIELD' need to be deleted, followed by records in 'AIA_ERROR_MONITOR' table.

CREATE OR REPLACE PACKAGE XX_AIA_PURGE_ERRORS AS

  /* TODO enter package declarations (types, exceptions, methods etc) here */
  procedure delete_instances ( min_creation_date in timestamp,
                   max_creation_date in timestamp,
                   batch_size in integer default 20000,
                   retention_period in integer default 30
                   );

END XX_AIA_PURGE_ERRORS;
/


CREATE OR REPLACE PACKAGE BODY XX_AIA_PURGE_ERRORS
AS
PROCEDURE delete_instances(
    min_creation_date IN TIMESTAMP,
    max_creation_date IN TIMESTAMP,
    batch_size        IN INTEGER DEFAULT 20000,
    retention_period  IN INTEGER DEFAULT 30 )
IS
  v_total_rows        INTEGER;
  v_min_creation_date TIMESTAMP := NVL(min_creation_date,TO_TIMESTAMP('2000/01/01 01:00:00', 'YYYY/MM/DD HH:MI:SS'));
  v_max_creation_date TIMESTAMP := NVL(max_creation_date,SYSTIMESTAMP);
  v_retention_period  INTEGER   := NVL(retention_period,30);
  v_batch_size        INTEGER   := NVL(batch_size,20000);
  /*
  Parameters:
  min_creation_date    = hardcoded to an old value in the past if no value is passed
  max_creation_date    = set to current datetime if no value is passed
  batch_size           = determines the max no. of records to be deleted in each iteration. Set to 20000 if no value is passed.
  retention_period     = determines the no.of days for which the data must be retained without deleting. Set to 30 days if no value is passed.
  */
BEGIN
  /*
  Capture the total records that needs to be purged in total_rows variable.
  */
  SELECT COUNT(*)
  INTO v_total_rows
  FROM AIA_ERROR_MONITOR
  WHERE rownum <= v_batch_size
  AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND (v_max_creation_date - (interval '1' DAY * v_retention_period));
  /*
  Loop through multiple batches if the total records to delete exceeds batch_size
  */
  WHILE v_total_rows>0
  LOOP
    /*
    Delete records from AIA_ERROR_FLEX_FIELD table
    */
    DELETE
    FROM aia_error_flex_field
    WHERE parent_oid IN
      (SELECT oid
      FROM AIA_ERROR_MONITOR
      WHERE rownum <= v_batch_size
      AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND (v_max_creation_date - (interval '1' DAY * v_retention_period))
      );
    /*
    Delete records from AIA_ERROR_MONITOR table
    */
    DELETE
    FROM AIA_ERROR_MONITOR
    WHERE rownum <= v_batch_size
    AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND (v_max_creation_date - (interval '1' DAY * v_retention_period));
    /*
    Commit transactions
    */
    COMMIT;
    /*
    Capture the total records that needs to be purged in total_rows variable after the delete.
    */
    SELECT COUNT(*)
    INTO v_total_rows
    FROM AIA_ERROR_MONITOR
    WHERE rownum <= v_batch_size
    AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND (v_max_creation_date - (interval '1' DAY * v_retention_period));
  END LOOP;
END delete_instances;
END XX_AIA_PURGE_ERRORS;

/


0 comments:

Post a Comment

 
Blogger Profile