By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATe_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE "TABLE_NAME_A"(id, "TIMESTAMP_UPDATED") AS
SELECT LEVEL,
add_months(TRUNC(sysdate), -84) + LEVEL - 4
FROM DUAL
CONNECT BY LEVEL <= 10;
10 rows affected
create or replace procedure TRUNCATE_EXPIRED_ARCHIVE_DATA
(
p_retention_period in number := 84
)
as
TABLE_NOT_FOUND EXCEPTION;
RetentionDt date := add_months(TRUNC(sysdate), -p_retention_period);
ExpiredCount number := 0;
InsertedCount number := 0;
PRAGMA EXCEPTION_INIT(TABLE_NOT_FOUND, -942);
begin
for table_rec in (
select owner, table_name, column_name, data_type
from all_tab_columns
where table_name like '%!_A' escape '!'
and column_name like 'TIMESTAMP!_%' escape '!'
order by table_name, column_name
) loop
-- Check if there are any expired records...
execute immediate
'select count(*)'
|| ' from "' || table_rec.owner || '"."' || table_rec.table_name || '"'
|| ' where "' || table_rec.column_name || '" < :1'
INTO ExpiredCount
USING RetentionDt;
IF ExpiredCount = 0 THEN
DBMS_OUTPUT.PUT_LINE(
'"' || table_rec.owner || '"."' || table_rec.table_name || '" Not found'
);
CONTINUE;
END IF;
BEGIN
execute immediate 'drop table /*REODT_PROD.*/ARCHIVE_TMP';
EXCEPTION
WHEN TABLE_NOT_FOUND THEN
SELECT * FROM USER_ERRORS;
BEGIN
DBMS_OUTPUT.ENABLE();
TRUNCATE_EXPIRED_ARCHIVE_DATA();
END;
/
1 rows affected
SELECT * FROM "TABLE_NAME_A";
| ID | TIMESTAMP_UPDATED |
|---|---|
| 4 | 2017-10-03 00:00:00 |
| 5 | 2017-10-04 00:00:00 |
| 6 | 2017-10-05 00:00:00 |
| 7 | 2017-10-06 00:00:00 |
| 8 | 2017-10-07 00:00:00 |
| 9 | 2017-10-08 00:00:00 |
| 10 | 2017-10-09 00:00:00 |