create or replace
Procedure          ReadingsPurge

As
   v_sql             varchar2(500);
   v_date            date;
   p_count NUMBER; 
   v_partition_name  user_tab_partitions.partition_name%TYPE;
   v_high_value      user_tab_partitions.high_value%TYPE;
   Cursor C1 Is Select Partition_Name, High_Value
               from user_tab_partitions where table_name='READING' and PARTITION_NAME!='P0'; --- P0 is the partition for the historical data
Begin
-- Code below drops partitions that are older than the NoOfDays Parameter
   OPEN c1;
  LOOP
    FETCH c1 INTO v_partition_name, v_high_value;
    EXIT WHEN c1%NOTFOUND;          
           
    v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
    
    IF v_date < (sysdate - 5) then
         dbms_output.put_line(to_char(v_date)||'==== v_date ====');
         v_Sql := 'SELECT COUNT(*)  FROM READING PARTITION(' || v_partition_name || ')';     
             
          execute immediate v_sql INTO p_count;      
          dbms_output.put_line(to_char(p_count)||'==== p_count ===='); 
       
       If ( p_count = 0) then 
           [color=red]  v_Sql := ' DELETE FROM READING PARTITION  ' || v_partition_name;
           execute immediate v_sql;                     
            Commit;[/color]       
        End if;
    END IF;
  End Loop;
  Close C1;
End ReadingsPurge;