Using partition with interval and insert NULL

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Mon, 18 May 2015 21:43:12 -0300
Message-ID: <CAJdDhaNM4jKCqrMFyn3dnGsNM+b-_tEPQ+Po3ajor65ZneU54Q_at_mail.gmail.com>

Hello,

I have the code below and it is not working right, when I insert data. I got the error message: ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions the insert has a null in the column delete_date.

Is there a way to define the partition using interval with NULL ?

DROP TABLE DATA_LOAD_INT; CREATE TABLE DATA_LOAD_INT
( data_id NUMBER(6),

  source_id    NUMBER,
  insert_date  DATE,
  delete_date  DATE,
  data_size    NUMBER,
  state        VARCHAR2(20),
  sys_current_date    DATE DEFAULT SYSDATE,

  year_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,
  mm_delete_date NUMBER GENERATED ALWAYS AS
(TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL)
  PARTITION BY RANGE (delete_date)
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))       )
enable row movement
;

• Insert massivo declare seq1 NUMBER; seq2 NUMBER; BEGIN DELETE FROM DATA_LOAD_INT; seq1 := 0; seq2 := 0; while (seq1 < 11) loop seq2 := 30; while (seq2 < 3001) loop INSERT INTO DATA_LOAD_INT (DATA_ID, SOURCE_ID, INSERT_DATE, DELETE_DATE, DATA_SIZE, STATE, SYS_CURRENT_DATE) VALUES ( 1, 1, SYSDATE-seq2, null, 1, 'M:'||seq2, DEFAULT); seq2 := seq2 + 1; end loop; seq1 := seq1 + 1; end loop; end; /

Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 19 2015 - 02:43:12 CEST