Partition by range

[hpaille]

Hello,

I want to create a partition on a VARCHAR2(6) field (year + month), but our DBAs only want partition based on dates.
I tried the following:
CREATE table ...(
ACCPER VARCHAR2(6),
....
)
PARTITION by range (ACCPER)
(partition P200611 values less than (to_date('200612','YYYYMM')))
but I have a message ORA-14036 partition bound value too large for column.

Any ideas on how to do that ?

 

[Dagon]

I don't think you can do a function-based partition bound. You will have to create a DATE column which is a copy of the varchar2 column and partition on that.

 

[hpaille]

It is possible to do that. I found the problem, the field ACCPER is too small to be compared with a date. hen defined with a VARCHAR2(10), it works.

 

[Dagon]

You have created a partitioned table which is comparing a VARCHAR2(6) field to a partition bound which is a DATE. This is utter rubbish and I do not believe it will work correctly.