Oracle Database Discussions

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