Solved

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

Posted on 2006-11-27
8
4,278 Views
Last Modified: 2009-07-29
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


0
Comment
Question by:vish3210
8 Comments
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 18025664
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
 

Author Comment

by:vish3210
ID: 18025751
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
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18025772
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
 

Author Comment

by:vish3210
ID: 18025847
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Expert Comment

by:rbrooker
ID: 18025945
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
 

Author Comment

by:vish3210
ID: 18031966
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
 
LVL 18

Accepted Solution

by:
rbrooker earned 250 total points
ID: 18032920
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
 

Author Comment

by:vish3210
ID: 18032955
Please help me.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now