title: Need to drop tables older than three days
body: <p>I am very new to the development of Oracle PL/SQL db apps...I have a customer requirement of which we have created an ETL for them that drops that previous days table. That table is created by using a synonym and it take a time stamp to create the table as for example: TEST_TEST_todaysdate</p>
  
  <p>Now they would like to alter the ETL and do not want to drop the previous days table, rather they would like to keep three days worth of data and on the fourth day...the procedure should delete the last days as a loop so that the table retains the last three days! I hope you guys get me but its like this - from Monday to Wednesday, on Thursday then delete Monday, On Friday then delete Tuesday, etc.</p>
  
  <p>Here is a code snippet:</p>
  
  <pre><code>   -- check &amp;&amp;1..&amp;&amp;3._&amp;&amp;2
     select count(*)
     into v_exist
     from all_tables
     where owner = upper('&amp;&amp;1')
     and   table_name = upper('&amp;&amp;3._&amp;&amp;2');
  
     if v_exist &gt; 0 then
        for r in (select owner,table_name
                  from all_tables
                  where  owner = upper('&amp;&amp;1')
                  and    table_name like upper('&amp;&amp;3%')  --- exclude MASTER_* tables
                  and    table_name &lt;&gt;  upper('&amp;&amp;3')    --- just for precaution
  </code></pre>
  
  <p>This is my code: </p>
  
  <pre><code>and    table_name = start_day DATE as ((SELECT extract(hour from current_timestamp)) - 4 FROM dual
    --and    table name =' || previous_days_table ||' -- saving previous days table
                  and    table_name &lt; upper('&amp;&amp;3._&amp;&amp;2')) loop
            v_sql := 'DROP TABLE ' || r.owner || '.' || r.table_name || ' CASCADE CONSTRAINTS PURGE';
            dbms_output.put_line(v_sql);
            execute immediate v_sql;
        end loop;
     end if;
  EXCEPTION
          WHEN OTHERS THEN
                  RAISE;
  END;
  </code></pre>