Hello I need to insert a row in a partitioned table but I am getting error , I have 2 known column values and third value of the column I am generating by a sequence.
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
I am a doing below
Step 1:
CREATE TABLE "SALES"."ACC1_SID"
( "ACC1_ID" CHAR(15 BYTE) NOT NULL ENABLE,
"SCAL_ID" VARCHAR2(15) ,
"DELETED" CHAR(1 BYTE) DEFAULT '0' NOT NULL ENABLE
) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "D01"
PARTITION BY HASH ("ACC1_ID")
SUBPARTITION BY LIST ("SCAL_ID")
(PARTITION "ACC101"
TABLESPACE "D01"
( SUBPARTITION "SP_P1X1" VALUES ('1')
TABLESPACE "D01"
NOCOMPRESS) ,
PARTITION "ACC102"
TABLESPACE "D02"
( SUBPARTITION "SP_P2X2" VALUES ('2')
TABLESPACE "D02"
NOCOMPRESS) ,
PARTITION "ACC103"
TABLESPACE "D03"
( SUBPARTITION "SP_P3X3" VALUES ('3')
TABLESPACE "D03"
NOCOMPRESS ),
PARTITION "ACC104"
TABLESPACE "D04"
( SUBPARTITION "SP_P4X4" VALUES ('4')
TABLESPACE "D04"
NOCOMPRESS) ,
PARTITION "ACC105"
TABLESPACE "D05"
( SUBPARTITION "SP_P5X5" VALUES ('5')
TABLESPACE "D05"
NOCOMPRESS) ,
PARTITION "ACC106"
TABLESPACE "D06"
( SUBPARTITION "SP_P6X6" VALUES ('6')
TABLESPACE "D06"
NOCOMPRESS ),
PARTITION "ACC107"
TABLESPACE "D07"
( SUBPARTITION "SP_P7X7" VALUES ('7')
TABLESPACE "D07"
NOCOMPRESS) ,
PARTITION "ACC108"
TABLESPACE "D08"
( SUBPARTITION "SP_P8X8" VALUES ('8')
TABLESPACE "D08"
NOCOMPRESS) ,
PARTITION "ACC109"
TABLESPACE "D09"
( SUBPARTITION "SP_P9X9" VALUES ('9')
TABLESPACE "D09"
NOCOMPRESS ),
PARTITION "ACC110"
TABLESPACE "D10"
( SUBPARTITION "SP_P10X10" VALUES ('10')
TABLESPACE "D10"
NOCOMPRESS ),
PARTITION "ACC111"
TABLESPACE "D11"
( SUBPARTITION "SP_P11X11" VALUES ('11')
TABLESPACE "D11"
NOCOMPRESS) ,
PARTITION "ACC112"
TABLESPACE "D12"
( SUBPARTITION "SP_P12X12" VALUES ('12')
TABLESPACE "D12"
NOCOMPRESS) ,
PARTITION "ACC113"
TABLESPACE "D13"
( SUBPARTITION "SP_P13X13" VALUES ('13')
TABLESPACE "D13"
NOCOMPRESS ),
PARTITION "ACC114"
TABLESPACE "D14"
( SUBPARTITION "SP_P14X14" VALUES ('14')
TABLESPACE "D14"
NOCOMPRESS) ,
PARTITION "ACC115"
TABLESPACE "D15"
( SUBPARTITION "SP_P15X15" VALUES ('15')
TABLESPACE "D15"
NOCOMPRESS) ,
PARTITION "ACC116"
TABLESPACE "D16"
( SUBPARTITION "SP_P16X16" VALUES ('16')
TABLESPACE "D16"
NOCOMPRESS ),
PARTITION "ACC117"
TABLESPACE "D17"
( SUBPARTITION "SP_P17X17" VALUES ('17')
TABLESPACE "D17"
NOCOMPRESS) ,
PARTITION "ACC118"
TABLESPACE "D18"
( SUBPARTITION "SP_P18X18" VALUES ('18')
TABLESPACE "D18"
NOCOMPRESS) ,
PARTITION "ACC119"
TABLESPACE "D19"
( SUBPARTITION "SP_P19X19" VALUES ('19')
TABLESPACE "D19"
NOCOMPRESS ),
PARTITION "ACC120"
TABLESPACE "D20"
( SUBPARTITION "SP_P20X20" VALUES ('20')
TABLESPACE "D20"
NOCOMPRESS ),
PARTITION "ACC121"
TABLESPACE "D21"
( SUBPARTITION "SP_P21X21" VALUES ('21')
TABLESPACE "D21"
NOCOMPRESS) ,
PARTITION "ACC122"
TABLESPACE "D22"
( SUBPARTITION "SP_P22X22" VALUES ('22')
TABLESPACE "D22"
NOCOMPRESS) ,
PARTITION "ACC123"
TABLESPACE "D23"
( SUBPARTITION "SP_P23X23" VALUES ('23')
TABLESPACE "D23"
NOCOMPRESS ),
PARTITION "ACC124"
TABLESPACE "D24"
( SUBPARTITION "SP_P24X24" VALUES ('24')
TABLESPACE "D24"
NOCOMPRESS) ,
PARTITION "ACC125"
TABLESPACE "D25"
( SUBPARTITION "SP_P25X25" VALUES ('25')
TABLESPACE "D25"
NOCOMPRESS) ,
PARTITION "ACC126"
TABLESPACE "D26"
( SUBPARTITION "SP_P26X26" VALUES ('26')
TABLESPACE "D26"
NOCOMPRESS ),
PARTITION "ACC127"
TABLESPACE "D27"
( SUBPARTITION "SP_P27X27" VALUES ('27')
TABLESPACE "D27"
NOCOMPRESS) ,
PARTITION "ACC128"
TABLESPACE "D28"
( SUBPARTITION "SP_P28X28" VALUES ('28')
TABLESPACE "D28"
NOCOMPRESS) ,
PARTITION "ACC129"
TABLESPACE "D29"
( SUBPARTITION "SP_P29X29" VALUES ('29')
TABLESPACE "D29"
NOCOMPRESS ),
PARTITION "ACC130"
TABLESPACE "D30"
( SUBPARTITION "SP_P30X30" VALUES ('30')
TABLESPACE "D30"
NOCOMPRESS ),
PARTITION "ACC131"
TABLESPACE "D31"
( SUBPARTITION "SP_P31X31" VALUES ('31')
TABLESPACE "D31"
NOCOMPRESS ),
PARTITION "ACC132"
TABLESPACE "D32"
( SUBPARTITION "SP_P32X32" VALUES ('32')
TABLESPACE "D32"
NOCOMPRESS )
);
Step 2: Creating a sequnce and sequnce generation function where in the value of column scal_id is generated by step 2
CREATE SEQUENCE ScalIdSeq
START WITH 1
INCREMENT BY 1;
create or replace function calc_scal_id (seq_name in VARCHAR2) RETURN NUMBER IS
next_seq number;
BEGIN
execute immediate 'select ' || seq_name || '.nextval from dual' into next_seq;
return next_seq;
END calc_scal_id;
/
Step 3: calling this function from step 2 in a trigger
CREATE or REPLACE trigger gen_scal_id
BEFORE INSERT ON sales.acc1_sid
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
:NEW.scal_id := calc_scal_id('ScalIdSeq');
END IF;
END;
/
Step 4: trying to insert
insert into sales.acc1_sid(ORGANIZATION_ID,DELETED) values ('0d123,'0');
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Ideally it shall insert the first row in SUBPARTITION "SP_P1X1" ,since the value of the sequence is 1.