Odd partitions using last_day

Question

Hi lads I'm trying to automatize a partitioning process here, and well ren into something odd. Why wouldnt'the following work?

create table flp_aa (cdate date, value varchar2(255))
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64M
           )
LOGGING
PARTITION BY RANGE (cdate)
(  
  PARTITION flp_aa_1010 VALUES LESS THAN (last_day(to_date('20101101', 'YYYYMMDD')))
    NOLOGGING
    NOCOMPRESS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64M
                NEXT             512K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                )
)

The idea is to use last_day(to_date('20101101', 'YYYYMMDD')) so each pretty partition ends on the last day of that month and everybody is happy. However I get an ORA-14019 when trying to execute that code...

[UPDATE]

Darn odd:

This work:

  (...)
  PARTITION flp_aa_1010 VALUES LESS THAN (to_date('20101101', 'YYYYMMDD'))
  (...)

This doesn't work:

  (...)
  PARTITION flp_aa_1010 VALUES LESS THAN (last_day(to_date('20101101', 'YYYYMMDD')))
  (...)

And this does not again:

  (...)
  PPARTITION flp_aa_1010 VALUES LESS THAN to_date(last_day(to_date('201010','YYYYMM'))))
  (...)

What the duck? which class did I miss here? Isn't the output of all these functions the same?

could anyone enlighten me?

cheers

f.

Tony Andrews · Accepted Answer · 2010-11-26T08:00:05.933

1

As the link you posted says:

partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE

A function call (LAST_DAY) is not a string, datetime or interval literal, number, or MAXVALUE. True, TO_DATE is a function call, but this is allowed as an exception because traditionally it was the only way to generate a DATE literal (you can now use the ANSI format DATE '2010-11-26' but not a lot of people know that). See documentation.

However, at the point where you generate the value '20101101', you could apply LAST_DAY and use the result instead of that - something like:

'... VALUES LESS THAN (' || TO_CHAR (LAST_DAY (...), 'YYYYMMDD') || ')...'

edited Nov 26 '10 at 08:00

answered Nov 25 '10 at 12:42

Tony Andrews

129,880
21
220
259

Well.. not quite. I had tried that before. Dosen't seem to be the case. You see, the field `cdate` is of date type. if you simply do `... VALUES LESS THAN (to_date('20101101', 'YYYYMMDD')) ...` it works! Still, I have tried also to convert my cdate into string and use the to_char and it would not work when placing the "last_day" thing... odd... – filippo Nov 25 '10 at 14:20
I have updated my answer. TO_DATE('20101101', 'YYYYMMDD') is considered to be a date literal, whereas LAST_DAY(TO_DATE('20101101', 'YYYYMMDD')) is not. – Tony Andrews Nov 26 '10 at 08:01

Odd partitions using last_day

1 Answers1