| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: range partition on char column
On Fri, 07 Jul 2006 19:50:40 GMT, j.w.vandijk.removethis_at_hetnet.nl
(Jaap W. van Dijk) wrote:
>>
>If you would look up the error in the error manual you would see that
>this has got nothing to do with the data, but with the specification
>of the partition boundaries.
>
>The column on which you have defined the partition key is varchar2,
>but you specify the boundary as DATE. So my guess is that Oracle
>converts your specification back to VARCHAR2, using the default date
>format, which usually is 'DD-MON-YY'.
>
>First you specify q2_2003, with a boundary value that converts back as
>'01-SEP-03', then you specify q3_2003 with a boundary that converts
>back as '01-JAN-04', which is lower, and so not allowed, hence the
>error.
>
>Oracle doesn't know and doesn't care if CDATE is to be interpreted as
>a date. So leave out any reference to date and specify your boundaries
>simply as
>
>partition q1_2003 values less than '2003-05-01'
>etc.
>
>Jaap.
Hi Jaap,
You are quite right.
Mario : You can do as Jaap says or consider converting CDATE from CHAR type to the more appropriate DATE type with
alter session set nls_date_format = 'yyyy-mm-dd';
create table old (CCID NUMBER(10),CDATE CHAR(10)); CREATE TABLE new
PARTITION BY RANGE (cdate)
(partition q1_2003 values less than
(to_Date('2003-05-01','yyyy-mm-dd')),
partition q2_2003 values less than
(to_Date('2003-09-01','yyyy-mm-dd')),
partition q3_2003 values less than
(to_Date('2004-01-01','yyyy-mm-dd')),
partition q1_2004 values less than
(to_Date('2004-05-01','yyyy-mm-dd')),
partition q2_2004 values less than
(to_Date('2004-09-01','yyyy-mm-dd')),
partition q3_2004 values less than
(to_Date('2005-01-01','yyyy-mm-dd')),
partition extra values less than (maxvalue))
AS SELECT ccid,to_date(cdate,'yyyy-mm-dd') as cdate FROM old WHERE 1=2;
 |
 |