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;

/


19 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?

Unknown 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

Ram Ramky said...

Keep posting blogs like this and I am waiting for the next part of your blog.

iOS Training in Chennai
Big Data Training in Chennai
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
Hadoop Course in Chennai

zuan said...

Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
mobile application development training online
web designing and development course training institute in Chennai with placement
mobile app development course
mobile application development course
learn mobile application development
mobile app development training
app development training
mobile application development training
mobile app development course online
online mobile application development

easylearn said...

Hi,Very nice post.Thanks for the information provided.It is very interesting and very informative.I am always impressed with your post and helpful tips.Keep posting tips and relevant content as usual.
big data training in btm

mary Brown said...

As I read the blog I felt a tug on the heartstrings. it exhibits how much effort has been put into this.
Final Year Project Domains for CSE

Spring Training in Chennai

Project Centers in Chennai for CSE

Spring Framework Corporate TRaining

Nannie Co Pam said...

Great Article. Thank you for sharing! Really an awesome post for every one.

IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

shreekavi said...

Amazing Post. keep update more information.
German Classes in Chennai
German Classes in Bangalore
German Classes in Coimbatore
German Classes in Madurai
German Language Course in Hyderabad
German language classes in bangalore
German language course in bangalore
German courses in bangalore
Selenium Training in Bangalore
Software Testing Course in Bangalore

rinjuesther said...

valuable blog,Informative content...thanks for sharing, Waiting for the next update...
clinical sas training in chennai
clinical sas course
clinical sas Training in Porur
clinical sas Training in Velachery
clinical sas Training in Tambaram
SAS Training in Chennai
Spring Training in Chennai
LoadRunner Training in Chennai
QTP Training in Chennai
javascript training in chennai

sureshshetty said...

7 tips to start a career in digital marketing

“Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

we have offered to the advanced syllabus course digital marketing for available join now

more details click the link now

[url]https://www.webdschool.com/digital-marketing-course-in-chennai.html[/url]

sureshshetty said...

Web designing trends in 2020

When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, WhatsApp, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

we have offered to the advanced syllabus course web design and development for available join no

more details click the link now

[url]https://www.webdschool.com/web-development-course-in-chennai.html[/url]

deiva said...

"Good job and thanks for sharing such a good blog You’re doing a great job. Keep it up !!
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

"

saran said...

I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

nisharoshan said...

Thank you for the informative post. It was thoroughly helpful to me. Keep posting more such articles and enlighten us.

Web Designing Training Course in Chennai | Certification | Online Training Course | Web Designing Training Course in Bangalore | Certification | Online Training Course | Web Designing Training Course in Hyderabad | Certification | Online Training Course | Web Designing Training Course in Coimbatore | Certification | Online Training Course | Web Designing Training Course in Online | Certification | Online Training Course

vé máy bay từ canada về Việt Nam said...

Aivivu - đại lý chuyên vé máy bay trong nước và quốc tế

vé máy bay đi Mỹ bao nhiêu tiền

mua vé máy bay hà nội đi sài gòn

vé cần thơ đi hà nội

giá vé máy bay hà nội đà lạt khứ hồi

vé máy bay đi Huế

cho thuê xe đi sân bay nội bài

Mason Jacob said...

wonderful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.

Tony Jacob said...

Hello, this weekend is good for me, since this time i am reading this enormous informative article here at my home. Miraz Tony Denver Jacob dyna Vini Ramos Rahim Mina Taylor

INFYCLE TECHNOLOGIES said...

Don’t follow your role model. Be the Role model person for others. But it's so simple by getting Hadoop training in Chennai. Because it is an assurance course to bounce back from a double salary. For joining call 7502633633.

Post a Comment

 
Blogger Profile