Home » SQL & PL/SQL » SQL & PL/SQL » Interval Partition table on Date column with SYSDATE as value (merged) (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Interval Partition table on Date column with SYSDATE as value (merged) [message #677885] Mon, 21 October 2019 01:19 Go to previous message
revathitirun
Messages: 12
Registered: May 2011
Junior Member
Hi All

As per our requirement we want to create INTERVAL PARTITION table on DATE column. If it is hard coded value it accepting .

drop table  interval_partition;
create table
interval_partition(
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

But Instead of hard coded values we want to consider the SYSDATE.

drop table interval_partition;
create table
interval_partition(
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (sysdate))
); 

Getting the following error message :


ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
14019. 00000 -  "partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE"
*Cause:    Partition bound list contained an element of invalid type
           (i.e. not a number, non-empty string, datetime or interval literal,
           or MAXVALUE)
*Action:   Ensure that all elements of partition bound list are of valid type


Please help me to resolve the issue.

Thanks
Revathi.T



[Updated on: Mon, 21 October 2019 01:50] by Moderator

Report message to a moderator

 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Calling an API from PL/SQL
Next Topic: Help with questions i'm tying to validate from interview
Goto Forum:
  


Current Time: Wed Feb 19 17:58:06 CST 2020