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 next message
revathitirun
Messages: 16
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

Interval Partition table on Date column with SYSDATE as value [message #677887 is a reply to message #677885] Mon, 21 October 2019 01:33 Go to previous messageGo to next message
revathitirun
Messages: 16
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



Re: Interval Partition table on Date column with SYSDATE as value [message #677888 is a reply to message #677885] Mon, 21 October 2019 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You want in the last partition all the rows that don't go to the other specified partitions then use MAXVALUE instead of SYSDATE.

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

Report message to a moderator

Re: Interval Partition table on Date column with SYSDATE as value [message #677889 is a reply to message #677888] Mon, 21 October 2019 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can also use INTERVAL partitioning and let Oracle create the partitions.

Re: Interval Partition table on Date column with SYSDATE as value [message #677890 is a reply to message #677889] Mon, 21 October 2019 02:36 Go to previous messageGo to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Thanks Michel for helping me to resolve the issue.

Here my intention is not to hard code any date as part of one TABLE Creation script :

Its not allowing to create the table with MAXVALUE Also.


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 default_partition VALUES LESS THAN (MAXVALUE)
); 

Error Message :


MAXVALUE partition cannot be specified for Interval partitioned objects
14761. 00000 -  "MAXVALUE partition cannot be specified for Interval partitioned objects"
*Cause:    An attempt was made to create a partition with a MAXVALUE highbound
           on an Interval partitioned object.
*Action:   Do not create a partition with a MAXVALUE highbound.


Thanks
Revathi.T
Re: Interval Partition table on Date column with SYSDATE as value [message #677891 is a reply to message #677890] Mon, 21 October 2019 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you use INTERVAL partitioning you define the FIRST partition only which can't have MAXVALUE as limit.
My posts were a strict "either", either you choose the first option either the second one.


So this can be:
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 first_partition VALUES LESS THAN (to_date('01/01/2000','DD/MM/YYYY'))
)
/
There is no last partition in an INTERVAL partitioning table, Oracle creates a new partition as soon as a new row that can't be in any existing partition is inserted.
Re: Interval Partition table on Date column with SYSDATE as value [message #677898 is a reply to message #677891] Mon, 21 October 2019 04:31 Go to previous message
revathitirun
Messages: 16
Registered: May 2011
Junior Member

Thanks for update.Going with the same kind of hard coded value.

Thanks
Revathi.T
Previous Topic: Calling an API from PL/SQL
Next Topic: Help with questions i'm tying to validate from interview
Goto Forum:
  


Current Time: Thu Mar 28 18:48:03 CDT 2024