OraFAQ Forum: SQL & PL/SQL » Interval Partition table on Date column with SYSDATE as value (merged)

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Interval Partition table on Date column with SYSDATE as value (merged) [message #677885]

Mon, 21 October 2019 01:19

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

revathitirun
Messages: 16
Registered: May 2011

Junior Member

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

Thanks
Revathi.T

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 15 23:33:08 CDT 2025