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;

/


4 comments:

Anonymous said...

I know this website gives quality based articles and additional stuff, is
there any other web page which gives such stuff in quality?

Werner Ebner said...

Hello There. I found your blog using msn. This is a really well written article. I will make sure to bookmark it and return to read more of your useful information. Thanks for the post. I'll definitely comeback. itunes login account

kanchipuram silk sarees said...


After going over a handful of the articles on your web page, I truly appreciate your
technique of writing a blog. I added it to my bookmark site
list and will be checking back in the near future. Please visit my website too and tell
me your opinion.


kanchipuram sarees

viswanath said...

AWS Training in Bangalore - Live Online & Classroom
myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


IOT Training in Bangalore - Live Online & Classroom
IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

Post a Comment

 
Blogger Profile