Error -14400: ORA-14400: inserted partition key does not map to any partition

Hello Experts,

When I try to run one of the procedure it blew following error. Please help me out to resolve this. Thanks in advance.

21:44:27  Start Executing PL/SQL block ...
21:44:27  Starting execution of PL/SQL block...
Error -14400: ORA-14400: inserted partition key does not map to any partition
21:44:28  Execution failed: ORA-14400: inserted partition key does not map to any partition
21:44:28  ORA-06512: at line 10
21:44:28  End Executing PL/SQL block


vish3210Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rbrookerConnect With a Mentor Commented:
what are you inserting into the column PARTITION_KEY?  any value other than AP10 will cause this error...

i do have a question, why have a partitioned table with one partition that accepts one value?  thats just a table with extra complications...

this would do the same thing :

create table blah as select * from dpr_ap.aprl_whlsl_price where 1 = 2;
alter table blah add check constraint chk_blah PARTITION_KEY = 'AP10';

the syntax will be wrong, but you will end up with the same result as a partitioned table, partitioned by a list of one value.

good luck :)
0
 
ravindran_eeeCommented:
ORA-14400inserted partition key is beyond highest legal partition key

Cause:The concatenated partition key of an inserted record was found to be beyond the concatenated partition bound list of the last partition.

Action:Either do not insert the key or add a partition capable of accepting the key

This is what Oracle documentation states about the error message..
Can u provide us the below information also (in case the above information is not useful)
-> Operation performed while the error occurred
-> The partitions and the type for the table in which data is being attempted to be imported.
0
 
vish3210Author Commented:
Please provide your e-mail Id I can attach some data and procedure I'm running so that  you can read clearly.

Tons of thanks
Vimal Kumar
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Daniel StanleyDatabase engineerCommented:
i see this a lot and it's often the result of a null value being inserted. check to make sure there is a value to insert into the partion table.

good luck,
daniels
0
 
vish3210Author Commented:
Experts,

I'm running below procedure & input parameter values are:

   p_plan_ctry:='MALA'  
   p_division:='10'      
   p_mkt_typ_id:='1'
   p_sty_dsp_nbr:=103721
   p_exp_sty_ind:=NULL
   p_effect_dt:=NULL

In the APRL_WHLSL_PRICE table (PLAN_CTRY, DIVISION,STY_DSP_NBR,MKT_TYP_ID, EXP_STY_IND, EFFECT_DT) are composite primary keys.

APRL_WHLSL_PRICE Data is below

PLAN_CTRY      DIVISION      STY_DSP_NBR      MKT_TYP_ID      EXP_STY_IND      EFFECT_DT      WHLSL_PRC      WHLSL_PRC_USD      PARTITION_KEY
MALA      10      103721      1    NULL             02/01/2006      57.85   NULL            AP10

*****************************************************************

PROCEDURE WRITE_APRL_PRICE (
   p_plan_ctry     VARCHAR2,
   p_division      VARCHAR2,
   p_mkt_typ_id    VARCHAR2,
   p_sty_dsp_nbr   VARCHAR2,
   p_exp_sty_ind   VARCHAR2,
   p_effect_dt     DATE
)
IS
   g_plan_ctry             aprl_whlsl_price.plan_ctry%TYPE;
   g_division              aprl_whlsl_price.division%TYPE;
   g_mkt_typ_id            aprl_whlsl_price.mkt_typ_id%TYPE;
   g_sty_dsp_nbr           aprl_whlsl_price.sty_dsp_nbr%TYPE;
   g_exp_sty_ind           aprl_whlsl_price.exp_sty_ind%TYPE;
   g_effect_dt             aprl_whlsl_price.effect_dt%TYPE;
   g_prc                   aprl_whlsl_price.whlsl_prc%TYPE;
   g_prc_usd               aprl_whlsl_price.whlsl_prc_usd%TYPE;
   g_partition_key         aprl_whlsl_price.partition_key%TYPE;
   g_total_inserted_aprl   PLS_INTEGER                           := 0;
   g_total_updated_aprl    PLS_INTEGER                           := 0;
   g_milepost              PLS_INTEGER                           := 0;
   v_insert                BOOLEAN                               := FALSE;
   v_number_of_errors      NUMBER                                DEFAULT 0;

   -- Price cursor
   CURSOR c_price (
      p_plan_ctry     VARCHAR2,
      p_division      VARCHAR2,
      p_mkt_typ_id    VARCHAR2,
      p_sty_dsp_nbr   VARCHAR2,
      p_exp_sty_ind   VARCHAR2,
      p_effect_dt     DATE
   )
   IS
      SELECT plan_ctry, division, sty_dsp_nbr, mkt_typ_id, exp_sty_ind,
             effect_dt, whlsl_prc, whlsl_prc_usd, partition_key,
             zz_setup_tmst, zz_chng_tmst, zz_chng_cnt, xxx_chng_usr_id
        FROM aprl_whlsl_price
       WHERE plan_ctry = p_plan_ctry
         AND division = p_division
         AND mkt_typ_id = p_mkt_typ_id
         AND sty_dsp_nbr = p_sty_dsp_nbr
         AND exp_sty_ind = p_exp_sty_ind
         AND effect_dt = p_effect_dt;

   r_price                 c_price%ROWTYPE;
BEGIN
--------------------------------------
-- Process PRICE record
--------------------------------------
   g_milepost := 3000;

   -- See if PRICE record exists
   OPEN c_price (p_plan_ctry        => g_plan_ctry,
                 p_division         => g_division,
                 p_mkt_typ_id       => g_mkt_typ_id,
                 p_sty_dsp_nbr      => g_sty_dsp_nbr,
                 p_exp_sty_ind      => g_exp_sty_ind,
                 p_effect_dt        => g_effect_dt
                );

   FETCH c_price
    INTO r_price;

   IF c_price%NOTFOUND
   THEN
      v_insert := TRUE;
   END IF;

   CLOSE c_price;

   -- Insert PRICE if record doesn't exist
   IF v_insert
   THEN
      g_milepost := 3300;

--    IF g_debug THEN
--      DBMS_OUTPUT.PUT_LINE('WAP3300..Inserting record');
--    END IF;
      INSERT INTO aprl_whlsl_price
                  (plan_ctry, division, sty_dsp_nbr, mkt_typ_id,
                   exp_sty_ind, effect_dt, whlsl_prc, whlsl_prc_usd,
                   partition_key, zz_setup_tmst,
                   zz_chng_tmst, zz_chng_cnt, xxx_chng_usr_id
                  )
           VALUES (g_plan_ctry, g_division, g_sty_dsp_nbr, g_mkt_typ_id,
                   g_exp_sty_ind, g_effect_dt, g_prc, g_prc_usd,
                   g_partition_key, nsc_v.g_processing_date,
                   nsc_v.g_processing_date, 0, USER
                  );

      nsc_v.g_control_total_inserted :=
                                    NVL (nsc_v.g_control_total_inserted, 0)
                                    + 1;
      nsc_v.g_control_total_written :=
                                     NVL (nsc_v.g_control_total_written, 0)
                                     + 1;
      g_total_inserted_aprl := NVL (g_total_inserted_aprl, 0) + 1;
   ELSE                                                        -- NOT v_insert
      -- Update non PK columns if any different from existing record
      IF    NVL (r_price.whlsl_prc, 0) <> NVL (g_prc, 0)
         OR NVL (r_price.whlsl_prc_usd, 0) <> NVL (g_prc_usd, 0)
         OR NVL (r_price.partition_key, 'ZZ') <> NVL (g_partition_key, 'ZZ')
      THEN
         g_milepost := 3500;

--      IF g_debug THEN
--        DBMS_OUTPUT.PUT_LINE('WAP3500..Updating changed price');
--      END IF;
         UPDATE aprl_whlsl_price
            SET whlsl_prc = g_prc,
                whlsl_prc_usd = g_prc_usd,
                partition_key = g_partition_key,
                zz_chng_tmst = nsc_v.g_processing_date,
                zz_chng_cnt = NVL (zz_chng_cnt, 0) + 1,
                xxx_chng_usr_id = USER
          WHERE plan_ctry = g_plan_ctry
            AND division = g_division
            AND mkt_typ_id = g_mkt_typ_id
            AND sty_dsp_nbr = g_sty_dsp_nbr
            AND exp_sty_ind = g_exp_sty_ind
            AND effect_dt = g_effect_dt;

         g_milepost := 3600;

         IF SQL%ROWCOUNT > 0
         THEN
            nsc_v.g_control_total_updated :=
                                    NVL (nsc_v.g_control_total_updated, 0)
                                    + 1;
            nsc_v.g_control_total_written :=
                                    NVL (nsc_v.g_control_total_written, 0)
                                    + 1;
            g_total_updated_aprl := NVL (g_total_updated_aprl, 0) + 1;
         END IF;
      ELSE                       -- No PRICE column differences so skip record
--      IF g_debug THEN
--        DBMS_OUTPUT.put_line ('WAP3700..skipped material # '
--            || g_material || ' date ' || to_char(g_effect_dt));
--      END IF;
         nsc_v.g_control_total_skipped :=
                                    NVL (nsc_v.g_control_total_skipped, 0)
                                    + 1;
         g_milepost := 3900;
      END IF;                                    -- r_price column differences
   END IF;                                                         -- v_insert

   nsc_v.g_control_total_processed :=
                                   NVL (nsc_v.g_control_total_processed, 0)
                                   + 1;
   nsc_cmt.increment_and_commit;
END write_aprl_price;

*****************************************************************
0
 
rbrookerCommented:
check the table.
if it is a list partition, check that the values you are inserting into the partition columns map to a partition.
eg: partition by list, values ( a, b, c, d, e, f ), inserting a value g will cause this error.
eg: partition by range, a date maybe, if you have values < 2 months ago and vlaues < 1 month ago, and not a values less than maxvalue, inserting sysdate will cause this error...

in otherwords, the value you are trying to insert into the column used for partitioning, does not match to a partition.

good luck :)
0
 
vish3210Author Commented:
Hi rbrooker,

Here is the create table statement::::

CREATE TABLE dpr_ap.aprl_whlsl_price
    (plan_ctry                      VARCHAR2(4) NOT NULL,
    division                       VARCHAR2(2) NOT NULL,
    sty_dsp_nbr                    VARCHAR2(6) NOT NULL,
    mkt_typ_id                     VARCHAR2(4) NOT NULL,
    exp_sty_ind                    VARCHAR2(1) NOT NULL,
    effect_dt                      DATE NOT NULL,
    whlsl_prc                      NUMBER(9,2) NOT NULL,
    whlsl_prc_usd                  NUMBER(9,2),
    partition_key                  VARCHAR2(4) NOT NULL,
    zz_setup_tmst                  DATE NOT NULL,
    zz_chng_tmst                   DATE NOT NULL,
    zz_chng_cnt                    NUMBER(10,0) NOT NULL,
    xxx_chng_usr_id                VARCHAR2(30) NOT NULL)
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  dprap_ds
  PARTITION BY LIST (PARTITION_KEY)
  (
  PARTITION ap10 VALUES ('AP10')
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  dprap_dm
  STORAGE   (
    INITIAL     4194304
    NEXT        4194304
    PCTINCREASE 0
    MINEXTENTS  1
    MAXEXTENTS  2147483645
    BUFFER_POOL DEFAULT
  )
  )
/


Still I didn't convinced. Please help!!!. Sorry to be late reply.

--Vimal
0
 
vish3210Author Commented:
Please help me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.