create or replace procedure drop_partition_prc as

  cursor tab_cur is
    select a.table_owner,
           a.table_name,
           a.partition_name,
           a.high_value,
           a.partition_position
      from dba_tab_partitions a, list_drop_part b
     where a.table_owner = b.tab_own
       and a.table_name = b.tab_name
       and a.partition_position between 1 and
           (select max(c.partition_position) - b.INTERVAL
              from dba_tab_partitions c
             where upper(c.table_owner) = upper(a.table_owner)
               and upper(c.table_name) = upper(a.table_name));

begin

  for tab_rec in tab_cur loop
  
    execute immediate 'alter table ' || tab_rec.table_owner || '.' ||
                      tab_rec.table_name || ' drop partition ' ||
                      tab_rec.partition_name || ' update global indexes ';
    insert into c##fanoa.result_drop_part
    values
      (tab_rec.table_owner,
       tab_rec.table_name,
       tab_rec.partition_name,
       tab_rec.high_value,
       sysdate);
    commit;

    dbms_output.put_line('alter table ' || tab_rec.table_owner || '.' ||
                         tab_rec.table_name || ' drop partition ' ||
                         tab_rec.partition_name ||
                         ' update global indexes ');
  
  end loop;

end drop_partition_prc;