Re: range partition on char column

On Fri, 07 Jul 2006 10:49:15 +0200, "mario" <xnazsco_at_tiscali.it> wrote:

>Hi all,
>I'm using 9.2.0.1 on Solaris machine.
>I need to create a range partition table
>usign a CTAS syntax. The columns I have to
>use for range partition is a char(10) storing
>a date in the format yyyy-mm-dd (please don't
>ask me WHY!!! :) )
>
>I know that when we use a function to casting a
>datatype to another one, the order results can be
>different from the one aspected.
>But I've made some test, and the cast should work
>fine..but obviously it _does_ not work.
>
>following the scenario:
>
>original table:
>
>CCID NOT NULL NUMBER(10)
>CDATE NOT NULL CHAR(10)
>............ other columns here ................
>
>new table (the one I should create partitioned)
>
>CREATE TABLE NEW
> TABLESPACE TABLE_AREA
> PARALLEL
> NOLOGGING
> 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')),
>....... other partitions here ......................................
> partition extra values less than (maxvalue))
> AS SELECT * FROM OLD WHERE 1=2;
>
>
>When I try to create the table I receive the error:
>
>partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),
> *
>ERROR at line 7:
>ORA-14037: partition bound of partition "Q2_2003" is too high
>

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. Received on Fri Jul 07 2006 - 14:50:40 CDT