title: How to create oracle procedure to export data into history tables?
body: <p>In an Oracle database, we have created 22 materialized views and we have created a procedure to refresh all these MV's. All 22 materialized views refresh successfully.</p>
  
  <p>On the last day of every month, we wanted to refresh the materialized views and copy the data to historical tables.  These historical tables have the same structure along with three additional columns that contain the date the data was copied along with the month and year that the data represents.</p>
  
  <ul>
  <li><code>copy_date</code> will be the <code>sysdate</code> when the data is copied to history table</li>
  <li><code>month</code> and <code>year</code> will be passed in to the stored procedure.  </li>
  </ul>
  
  <p>For example, if I forgot to refresh at the end of January and remembered on February 4</p>
  
  <ul>
  <li><code>copy_date</code>: 4th of feb</li>
  <li>'month' and 'year' are : FEB and 2012</li>
  </ul>
  
  <p>The reporting team will use these columns to filter the data in order to analyze it.</p>
  
  <p>example : Table A</p>
  
  <pre><code>study country state
  abc    india    ap
  bcd    china    xx
  </code></pre>
  
  <p>I need a procedure that copies the data to a history table like:</p>
  
  <pre><code>month year copy_date     study  country state
  jan   2013 01-01-2013    abc    india   ap
  </code></pre>
  
  <p>When I run this procedure, I want to enter values for the <code>month</code> and <code>year</code> parameters (jan and 2013 in this case).</p>