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;
/
