Hi Eriovaldo,
If you refer to oracle documentation, it clearly says.conveys the restrictions
that "NULL" value are not allowedFor INTERVAL Clause
"You cannot specify NULL values for the partitioning key column."
Try to use some default value for that column, it may resolve issue.
- ThanksPavan Kumar N
From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, 19 May 2015 6:13 AM
Subject: Using partition with interval and insert NULL
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 partitionsthe 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 massivodeclareseq1 NUMBER;seq2 NUMBER;BEGINDELETE 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; /
RegardsEriovaldo
