A Procedure to drop partitions by date or by partition name

CREATE OR REPLACE procedure SYS.drop_partitions
(
   p_schema_owner             varchar2,
   p_table_name               varchar2,
   p_partition_name           varchar2,
   p_partition_date           date
)
is
   l_current_partition_date   date;
begin

   --DEAL WITH PARTITION'S BY DATE
   if p_partition_date is not null
   then

      for all_parts in
      (
         select
            high_value,
            partition_name
         from
            dba_tab_partitions
         where
            table_owner = p_schema_owner
         and
            table_name  = p_table_name
         order by
            partition_position asc
      )
      loop

         execute immediate 'select ' || all_parts.high_value || ' from dual' into l_current_partition_date;

         if l_current_partition_date <= p_partition_date
         then
            execute immediate 'alter table ' || p_schema_owner || '.' || p_table_name || ' drop partition ' || all_parts.partition_name  || ' update global indexes';
         end if;

      end loop;

   end if;

   --DEAL WITH PARTITION'S BY NAME
   if p_partition_name is not null
   then

      for all_parts in
      (
         select
            d.high_value,
            d.partition_name
         from
            dba_tab_partitions d
         where
            d.table_owner        = p_schema_owner
         and
            d.table_name         = p_table_name
         and
            d.partition_position <= (
                                       select
                                          d2.partition_position
                                       from
                                          dba_tab_partitions d2
                                       where
                                          d2.table_owner    = d.table_owner
                                       and
                                          d2.table_name     = d.table_name
                                       and
                                          d2.partition_name = p_partition_name
                                    )
         order by
            d.partition_position asc
      )
      loop

         execute immediate 'alter table ' || p_schema_owner || '.' || p_table_name || ' drop partition ' || all_parts.partition_name  || ' update global indexes';

      end loop;

   end if;

end;
/



HOW TO GIVE VALUE IN THE RELATIVE FIELDS;
========================================

p_schema_owner   ::   SIEMENS_BSS

p_table_name     ::   SCANBTS_DY

p_partition_name ::   P20100429

p_partition_date ::   TO_DATE(' 2010-04-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')