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