title: Interval partitioning on varchar2 column
body: <p>I have a huge table that holds a lot of data - history, and current.</p>
  
  <p>I Have an automatic job that move old data from some tables to historic tables </p>
  
  <p>(and then delete from the source).</p>
  
  <p>So I want to build an </p>
  
  <blockquote>
    <p>interval-partition</p>
  </blockquote>
  
  <p>table which get the old data from that table.</p>
  
  <p>The problem is that the base-date-column in the source is varchar2 in this pattern:</p>
  
  <blockquote>
    <p>2017/07</p>
  </blockquote>
  
  <p>And the new table columns datatypes need to be identical to the source.</p>
  
  <p>So when I'm trying to create the interval-partitioning table, I can't do a range on it. </p>
  
  <p>You get an error if you try to use </p>
  
  <blockquote>
    <p>to_date</p>
  </blockquote>
  
  <p>.</p>
  
  <p>How can I do that???</p>
  
  <p>This is the source table:</p>
  
  <pre><code>    CREATE TABLE 
  DATA_01( 
  APPLICATION   VARCHAR2(10 BYTE),
  PROCESS       VARCHAR2(100 BYTE),
  SNAPSHOT_MONTH VARCHAR2(7 BYTE)
  );
  </code></pre>
  
  <p>That what I wanted to do but getting error:</p>
  
  <pre><code>CREATE TABLE 
  HISTORY_01 ( 
      APPLICATION   VARCHAR2(10 BYTE) NOT NULL ENABLE,
      PROCESS       VARCHAR2(100 BYTE),
      SNAPSHOT_MONTH VARCHAR2(7 BYTE)
  ) 
  PARTITION BY RANGE (to_date(snapshot_month, 'yyyy/mm')) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
  (  
     PARTITION p_until_01_1900 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))
  ); 
  </code></pre>
  
  <p>Thanks.</p>