create or replace procedure drop_partition(v_schema_name varchar2,v_table_name varchar2,v_start_date varchar2,v_end_date varchar2)
as
schema_name varchar2(50):=upper(v_schema_name);
table_name varchar2(50):=upper(v_table_name);
start_date varchar2(10):=v_start_date;
end_date varchar2(10):=v_end_date;

v_sql varchar(200);
v_partition_name varchar(50);

type refcur_type is ref cursor;
cur refcur_type;

begin
    execute immediate ‘create table tmp_partition_name(partition varchar2(20),drop_date date)‘;
    execute immediate ‘insert into tmp_partition_name
                       select partition,to_date(substr(high_value,instr(high_value,‘‘‘‘‘‘‘‘)+2,10),‘‘yyyy-mm-dd‘‘)-1 from (
                       with xml as (
                       select dbms_xmlgen.getxmltype(‘‘select table_name, partition_name, high_value from dba_tab_partitions where table_owner=‘‘‘‘‘||schema_name||‘‘‘‘‘ and table_name=‘‘‘‘‘||table_name||‘‘‘‘‘‘‘) as x
                       from dual
                       )
                       select extractValue(rws.object_value, ‘‘/ROW/TABLE_NAME‘‘) table_name,
                       extractValue(rws.object_value, ‘‘/ROW/PARTITION_NAME‘‘) partition,
                       extractValue(rws.object_value, ‘‘/ROW/HIGH_VALUE‘‘) high_value
                       from xml x,
                       table(xmlsequence(extract(x.x, ‘‘/ROWSET/ROW‘‘))) rws ORDER BY extractValue(rws.object_value, ‘‘/ROW/TABLE_NAME‘‘),extractValue(rws.object_value, ‘‘/ROW/HIGH_VALUE‘‘)
                       ) a
                       where to_date(substr(high_value,instr(high_value,‘‘‘‘‘‘‘‘)+2,10),‘‘yyyy-mm-dd‘‘)-1>=to_date(‘‘‘||start_date||‘‘‘,‘‘yyyy-mm-dd‘‘)
                       and to_date(substr(high_value,instr(high_value,‘‘‘‘‘‘‘‘)+2,10),‘‘yyyy-mm-dd‘‘)-1<to_date(‘‘‘||end_date||‘‘‘,‘‘yyyy-mm-dd‘‘)
                       and partition !=‘‘PART_DFT‘‘‘;
    open cur for ‘select partition from tmp_partition_name order by drop_date asc‘;
    loop
      fetch cur into v_partition_name;
      exit when cur%notfound;
      v_sql:=‘alter table ‘||schema_name||‘.‘||table_name||‘ drop partition ‘||v_partition_name||‘ update global indexes‘;
      execute immediate v_sql;
      commit;
      end loop;
    close cur;
    execute immediate ‘drop table tmp_partition_name purge‘;
    
end drop_partition;