Automatically Drop Partitions

/* ---------------------------------------------------------------------
Name : partition_drop_schedule
Purpose : To store the retention period of partitions of
: indivisual tables. Thisdata is used by partition_drop
: procedure to automatically drop partitions.
----------------------------------------------------------------------*/
create table partition_drop_schedule(
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
PART_SCHEME VARCHAR2(10),
RETENTION_DURATION NUMBER(5),
RETENTION_UNIT VARCHAR2(10)
)
/
comment on table partition_drop_scheduleis 'To store the retention period of partitions of individual tables. This data is used by partition_drop procedure to automatically drop partitions.'
/
comment on columnpartition_drop_schedule.owner is 'the owner of the table'
/
comment on column partition_drop_schedule.table_name is 'the name of the table'
/
comment on column partition_drop_schedule.part_scheme is 'the partitioning timescheme (DAILY/WEEKLY/MONTHLY/QUARTERLY/HALFYEARLY/YEARLY) of the table'
/
comment on column partition_drop_schedule.RETENTION_DURATION is 'the duration of retention of the partition in number'
/comment on column partition_drop_schedule.RETENTION_UNIT is 'the unit of duration of retention (DAY/WEEK/MONTH/QUARTER/HALFYEAR/YEAR of the table'
/

Here are some sample inserts into the table:insert into partition_drop_schedule values
(
'ARUP','TEST','MONTHLY',12,'MONTH'
)
/
commit
/

Next comes the stored procedure:

CREATE OR REPLACE procedure SYS.partition_drop
ascursor sched_cur is
select *
from partition_drop_schedule
;
l_sched_rec sched_cur%rowtype;
l_cutoff_date date;
l_hv date;
l_sql varchar2(2000);
l_dis_cons_sqlvarchar2(2000);
l_ena_cons_sql varchar2(2000);
l_drop_sql varchar2(2000);
errm varchar2(2000);
l_out varchar2(2000);
l_full varchar2(32000);
l_recipients varchar2(2000) :=...