3 Replies Latest reply: Aug 23, 2010 12:54 PM by 733414 RSS

    using sysdate with interval partitions

    733414
      I have a table that I want to create in multiple databases. Dev, test, production. They will go into each database on different dates. I want the first partitions to be less than that date. I know I can hard code a date for each database as follows:
      create table test
                 (msg varchar2(4000),
                 msg_long clob,
                 created_date date
               )
          partition by range (created_date)
          interval (numtodsinterval(1,'DAY'))
          ( partition part1 values less than (to_date('20100823',YYYYMMDD')) )
      /
      I would like to replace: part1 values less than (to_date('20100823',YYYYMMDD')

      with something more dynamic. Namely using the SYSDATE function. however, I can't get it to work.
        1      select to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')
        2*     from dual
      
      TO_DATE(T
      ---------
      23-AUG-10
      now I try to use it in the interval statement.
      create table test
                 (msg varchar2(4000),
                 msg_long clob,
                 created_date date
               )
          partition by range (created_date)
          interval (numtodsinterval(1,'DAY'))
          ( partition part1 values less than (to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')) )
      /
      
      ERROR at line 9:
      ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or
      MAXVALUE
      Edited by: user11990507 on Aug 23, 2010 8:07 AM

      Edited by: user11990507 on Aug 23, 2010 8:07 AM