title: Oracle procedure or job to copy automatically data from one schema to another based on timeline
body: <p>i have a table in my schema (<code>Schema1</code>) in my DB which has 8.5 mio records in a table. Most of the records older than 360 days. </p>
  
  <p>I have a second schema (<code>Schema2</code>) with the same table structure and want to copy all records from <code>Schema1.table</code> older than <strong>360 days</strong> into this <code>Schema2.table</code>.</p>
  
  <p><strong>As well, every week passed it should gather the next records in line to get copied.</strong></p>
  
  <p>My below one doesnt seem to work as I expected it to work.</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE ARCHIVE_DATA
  AS
     CURSOR Schema1_extract
     IS
        SELECT column1, column2, column3 FROM Schema1.table;
  BEGIN
     FOR Schema2_insert IN Schema1_extract
     LOOP
        BEGIN
           INSERT INTO Schema2.table (column1, column2, column3)
                VALUES (column1, column2, column3);
  
           COMMIT;
        END;
     END LOOP;
  END;
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>Additional there is another action to be executed after the first above, because i want to purge the already merged data from the src table with below statement:</p>
  
  <pre><code>DELETE FROM src
        WHERE     table_date &lt;= SYSDATE - 360
              AND table_id IN (SELECT table_id FROM trg.column);
  </code></pre>