SQL & PL/SQL

Hey everyone, mucking on an Oracle 11.2 database, simple range partitioning issue. Seems using a "complex" formula inside the AT clause annoys it? Or am I doing something wrong?

I create the table with RANGE partition just fine:

CREATE TABLE my_part_tab
   ( id        number,
     sdate     date
     )
 PARTITION BY RANGE ( sdate )
      (  PARTITION P2013Q1 VALUES LESS THAN ( TO_DATE('01-jan-2013','dd-mon-yyyy') ),
         PARTITION P2013Q2 VALUES LESS THAN ( TO_DATE('01-jul-2013','dd-mon-yyyy') ),
         PARTITION P2013Q3 VALUES LESS THAN ( TO_DATE('01-oct-2013','dd-mon-yyyy') ),
         PARTITION P2013Q4 VALUES LESS THAN ( TO_DATE('01-jan-2014','dd-mon-yyyy') ),
         PARTITION P2014Q1 VALUES LESS THAN ( TO_DATE('01-apr-2014','dd-mon-yyyy') ),
         PARTITION P2014Q2 VALUES LESS THAN ( TO_DATE('01-jul-2014','dd-mon-yyyy') ),
         PARTITION P2014Q3 VALUES LESS THAN ( TO_DATE('01-oct-2014','dd-mon-yyyy') ),
         PARTITION P2014Q4 VALUES LESS THAN ( TO_DATE('01-jan-2015','dd-mon-yyyy') ),
         PARTITION PMAX    VALUES LESS THAN ( MAXVALUE )
      );
      
Table created.

(and yes, I'm aware of INTERVAL option that'll do this next part "automagically", however, INTERVAL and REFERENCE partitioning are incompatible, and the child table is using REFERENCE partition).

So anyway, to make things easier on DBA for future, I'm trying to create a script that makes added a year's worth of partitions less manual.

So far, I have the following working:

ACCEPT lYear PROMPT "Add Paritions for which calendar year?"

ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( TO_DATE('01-oct-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q3,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( (TO_DATE('31-dec-&lYear','dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );

SQL >  ACCEPT lYear PROMPT "Add Paritions for which calendar year?"
Add Paritions for which calendar year?2015
SQL >  
SQL >  ALTER TABLE my_part_tab SPLIT PARTITION PMAX
  2     AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX );
old   2:    AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX )
new   2:    AT ( TO_DATE('01-apr-2015','dd-mon-yyyy') ) INTO ( PARTITION P2015Q1,PARTITION PMAX )

Table altered.

SQL >  ALTER TABLE my_part_tab SPLIT PARTITION PMAX
  2     AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );
old   2:    AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX )
new   2:    AT ( TO_DATE('01-jul-2015','dd-mon-yyyy') ) INTO ( PARTITION P2015Q2,PARTITION PMAX )

Table altered.

SQL >  ALTER TABLE my_part_tab SPLIT PARTITION PMAX
  2     AT ( TO_DATE('01-oct-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q3,PARTITION PMAX );
old   2:    AT ( TO_DATE('01-oct-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q3,PARTITION PMAX )
new   2:    AT ( TO_DATE('01-oct-2015','dd-mon-yyyy') ) INTO ( PARTITION P2015Q3,PARTITION PMAX )

Table altered.

SQL >  ALTER TABLE my_part_tab SPLIT PARTITION PMAX
  2     AT ( (TO_DATE('31-dec-&lYear','dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
old   2:    AT ( (TO_DATE('31-dec-&lYear','dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX )
new   2:    AT ( (TO_DATE('31-dec-2015','dd-mon-yyyy')) ) INTO ( PARTITION P2015Q4,PARTITION PMAX )

Table altered.

If you look at that final, partition, however, I set the AT clause to Dec 31, 2015. It should be Jan 1, 2016.
Any thoughts on how to pull that off?

I tried the following:

ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( (TO_DATE('31-dec-&lYear','dd-mon-yyyy')+1) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );

SQL >  ACCEPT lYear PROMPT "Add Paritions for which calendar year?"
Add Paritions for which calendar year?2016
SQL >  ALTER TABLE my_part_tab SPLIT PARTITION PMAX
  2     AT ( (TO_DATE('31-dec-&lYear','dd-mon-yyyy')+1) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
old   2:    AT ( (TO_DATE('31-dec-&lYear','dd-mon-yyyy')+1) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX )
new   2:    AT ( (TO_DATE('31-dec-2016','dd-mon-yyyy')+1) ) INTO ( PARTITION P2016Q4,PARTITION PMAX )
   AT ( (TO_DATE('31-dec-2016','dd-mon-yyyy')+1) ) INTO ( PARTITION P2016Q4,PARTITION PMAX )
                                                 *
ERROR at line 2:
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE

(yes, this time I passed it 2016, because the 2015 partitions are there now ... I did confirm it by removing the final Q4 partition in the previous trial and tried this one with 2015 year, same thing).

But no luck ...
Same issue with other variations:

ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( (ADD_MONTHS(TO_DATE('01-jan-&lYear','dd-mon-yyyy'),12)) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );

ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( (TO_DATE('01-jan-'||TO_CHAR(TO_NUMBER('&lYear')+1,'fm9999'),'dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );

Anyone have any ideas?