Getting an Error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"

I have a below Package which is giving error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

Please let me know what is going wrong here.

CREATE OR REPLACE PACKAGE BODY PKG_H
IS

    PROCEDURE PROC_SUBMIT_H
     (
        Pout_Rqst_Id                  OUT     NVARCHAR2,                    
        Pout_err_cd                   OUT     VARCHAR2,
        Pout_err_msg                  OUT     VARCHAR2,
        Pin_Rqst_Type_Id              IN      NUMBER,         
        Pin_Attachment                IN      NVARCHAR2,
        Pin_Brand_Id                  IN      NVARCHAR2,
        Pin_Prop_Id                   IN      NVARCHAR2,
    --    Pin_Htl_Stat_Rqst_Typ_ID      IN      NUMBER,
        Pin_Orcl_Acct_Num             IN      NVARCHAR2, -- NUMBER, /* Changed on 22.09.2011,as stated by FIS Team  */
        Pin_ORCL_User_Name            IN      NVARCHAR2,
        Pin_Rstn_Id                   IN      NUMBER,
        Pin_Rstn_Name                 IN      NVARCHAR2,
        Pin_Rstn_Start_Date           IN      DATE,
        Pin_Rstn_End_Date             IN      DATE,
      --  Pin_Change_Type_Ind           IN      NVARCHAR2,
        Pin_Trans_Time_Orcl           IN      TIMESTAMP,
        Pin_Fis_Acct_Stat_Prsnt_Id    IN      NUMBER,
        Pin_Fis_Acct_Future_Stat      IN      NUMBER,
        Pin_Auto_Ind                  IN      NVARCHAR2,
  --      Pin_Stat_Change_Resn_ID       IN      NUMBER,  /* changed due to ETL requirement as on 17.09.2011 */
        Pin_Stat_Change_Resn_Desc     IN      NVARCHAR2, /* changed due to ETL requirement as on 17.09.2011 */
        Pin_Brand_Dot_Com_Ind         IN      NVARCHAR2,
   --     Pin_Expdt_Ind                 IN      NVARCHAR2,
    --    Pin_Expdt_Dt                  IN      DATE,
        Pin_Rqstr_Id                  IN      NVARCHAR2,
        Pin_Impn_Id                   IN      NUMBER,
    --    Pin_Agent_Id                  IN      NVARCHAR2, /* Changed as on 22.09.2011 */
    --    Pin_Agent_Name                IN      NVARCHAR2, /* Changed as on 22.09.2011 */
        Pin_File_Name                 IN      NVARCHAR2,
        Pin_Prov_Date                 IN      DATE 
     --   Pin_Rqst_Stat_ID              IN      NUMBER     
    --    Pin_Prov_Time                 IN      DATE
       )
     IS
   --   lv_err_cd          VARCHAR2(10);
   --   lv_err_msg          VARCHAR2(4000);
      Ln_Cnt              NUMBER;
      DUP_VAL             EXCEPTION;

      lv_rqst_id          NVARCHAR2(20);
      Ln_rqst_stat_id_it NUMBER;
      Ln_rqst_stat_id_Q  NUMBER;
      Ln_rqst_category_id NUMBER;   
      Ln_Stat_Change_Resn_Id NUMBER;
   --   Ln_Htl_Stat_Rqst_Typ_Id NUMBER;
      lt_data_01   STRINGTABLETYPE := STRINGTABLETYPE();
      lt_data_02   STRINGTABLETYPE := STRINGTABLETYPE();

      BEGIN

        SELECT fnc_gen_request_id 
          INTO Lv_rqst_id 
          FROM dual;

        SELECT rqst_stat_id 
          INTO Ln_rqst_stat_id_it 
          FROM rqst_stat_mst 
         WHERE rqst_stat_desc = 'In Transmit';       

        SELECT rqst_stat_id 
          INTO Ln_rqst_stat_id_Q 
          FROM rqst_stat_mst 
         WHERE rqst_stat_desc = 'Pending';  

      SELECT COUNT(1)
        INTO Ln_Cnt 
        FROM HOTEL_STAT_RQST
       WHERE FILE_NAME=Pin_File_Name;   

      IF Ln_Cnt >0 then  
       RAISE DUP_VAL;
      END IF;


       IF Pin_Stat_Change_Resn_Desc IS NOT NULL THEN 

        SELECT STAT_CHANGE_RESN_ID 
          INTO Ln_Stat_Change_Resn_Id
          FROM STAT_CHANGE_RESN_MST
         WHERE UPPER(STAT_CHANGE_RESN_DESC)=UPPER(TRIM(Pin_Stat_Change_Resn_Desc));
        END IF;


        DELETE 
          FROM HOTEL_STAT_RQST
         WHERE RQST_ID=lv_rqst_id;

        INSERT INTO HOTEL_STAT_RQST
                 (RQST_ID
                 ,RQST_TYPE_ID
                 ,RQST_STAT_ID
                 ,BRAND_ID
                 ,PROPERTY_ID
                 ,STAT_CHANGE_RESN_ID
             --    ,HOTEL_STAT_RQST_TYPE_ID
                 ,ORCL_ACCT_NUM
                 ,ORCL_USER_NAME
                 ,TRANS_TIME_ORCL
                 ,FIS_ACCOUNT_STATUS_PRESENT_ID
                 ,FIS_ACCT_FUTURE_STAT
                 ,RSTCTN_ID
                 ,RSTCTN_NAME
                 ,RSTCTN_STRT_DT
                 ,RSTCTN_END_DT
              --   ,RSTCTN_PREV_STRT_DT  /* SCHEMA CHANGED  */
             --    ,RSTCTN_PREV_END_DT  /* SCHEMA CHANGED  */
              --   ,PREV_RSTN_ID    /* SCHEMA CHANGED  */
                 ,AUTO_IND
            --     ,CHANGE_TYPE_IND
                 ,BRAND_DOT_COM_IND
    开发者_StackOverflow社区             ,RQSTR_ID
                 ,IMPN_ID
                 ,EXPDT_IND
                 ,EXPDT_DT
                -- ,PROVSN_STAT /* SCHEMA CHANGED  */
                -- ,PROVSN_TIME /* SCHEMA CHANGED  */
                 ,CREATED_ON
                 ,UPDATED_BY 
                 ,UPDATED_ON
                 ,FILE_NAME 
                 ,PROV_DATE
                  )
           VALUES  
                 (
                  lv_rqst_id
                 ,Pin_Rqst_Type_Id
                 ,Ln_rqst_stat_id_it
                 ,Pin_Brand_Id
                 ,Pin_Prop_Id
                 ,Ln_Stat_Change_Resn_Id   /* changed due to ETL requirement as on 17.09.2011 */
            --     ,Pin_Htl_Stat_Rqst_Typ_ID
                 ,Pin_Orcl_Acct_Num
                 ,Pin_ORCL_User_Name
                 ,Pin_Trans_Time_Orcl
                 ,Pin_Fis_Acct_Stat_Prsnt_Id
                 ,Pin_Fis_Acct_Future_Stat
                 ,Pin_Rstn_Id
                 ,Pin_Rstn_Name
                 ,Pin_Rstn_Start_Date
                 ,Pin_Rstn_End_Date
            --     ,NULL  /* SCHEMA CHANGED  */
            --     ,NULL  /* SCHEMA CHANGED  */
            --     ,NULL  /* SCHEMA CHANGED  */
                 ,Pin_Auto_Ind
                -- ,Pin_Change_Type_Ind
                 ,Pin_Brand_Dot_Com_Ind
                 ,Pin_Rqstr_Id
                 ,Pin_Impn_Id
                 ,NULL
                 ,NULL
          --       ,NULL   /* SCHEMA CHANGED  */
           --      ,Pin_Prov_Time  /* SCHEMA CHANGED  */
                 ,SYSDATE
                 ,Pin_Rqstr_Id
                 ,SYSDATE 
                 ,Pin_File_Name
                 ,Pin_Prov_Date
                 ); 

      IF Pin_Attachment IS NOT NULL THEN

        DELETE
          FROM attach_ref
         WHERE rqst_id=lv_rqst_id;   

         SELECT CAST(SPLIT(Pin_Attachment,'|') AS STRINGTABLETYPE) INTO lt_data_01 FROM DUAL;

          FOR i_outer IN  1..lt_data_01.COUNT LOOP


            SELECT CAST(SPLIT(lt_data_01(i_outer),'~')AS STRINGTABLETYPE) INTO lt_data_02 FROM DUAL;

            INSERT INTO attach_ref 
                    ( 
                    rqst_id,
                    attach_id,  -- SEQUENCE
                    attach_ind,  
                    attach_file_name,                          
                    file_path,
                    ord_num
                    )
                VALUES(                  
                    lv_rqst_id,
                    attach_id_seq.NEXTVAL,  -- SEQUENCE
                    'REQUESTOR',
                    lt_data_02(1),
                    lt_data_02(2),
                    i_outer            
                    );

          END LOOP;
      END IF;  


       DELETE FROM rqst_queue WHERE rqst_id=lv_rqst_id;

        INSERT INTO rqst_queue             
                (
                rqst_id, 
                prnt_rqst_id, 
                queu_start_time, 
                queu_end_time, 
                agnt_id, 
                agnt_name, 
                property_id, 
                src_sys,
                tgt_sys, 
                queu_stat_ind    
                )            
          VALUES 
                (
                 lv_rqst_id,
                  NULL,
                  SYSDATE,
                  NULL,
                  NULL,
                  NULL,
                  Pin_Prop_Id,
                  'RQT',
                  'SFDC',
                  Ln_rqst_stat_id_Q          
                );    

         IF Pin_Rqst_Type_Id IS NOT NULL THEN  

            SELECT rqst_category_id 
              INTO ln_rqst_category_id 
              FROM rqst_type_mst 
             WHERE rqst_type_id = Pin_Rqst_Type_Id;

          END IF;

          DELETE
            FROM rqst_sumry
           WHERE rqst_id = lv_rqst_id;

          INSERT INTO rqst_sumry
                (
                  rqst_id, 
                  rqst_type_id,
                  prnt_rqst_id, 
                  brand_id, 
                  property_id, 
                  expdt_ind, 
                  expdt_dt,
                  rqstr_id,   
                  rqst_stat_id,
                  compln_dt, 
                  estm_compln_time, 
                  rqst_category_id,
                  submission_dt 
                )
           VALUES
                (
                  lv_rqst_id,
                  Pin_Rqst_Type_Id,  
                  NULL,
                  Pin_Brand_Id,
                  Pin_Prop_Id,
                  NULL,
                  NULL,
                  Pin_Rqstr_Id,
                  Ln_rqst_stat_id_it,       
                  NULL, 
                  NULL,
                  ln_rqst_category_id, 
                  SYSDATE   
                );

        COMMIT;    


        Pout_Rqst_Id := lv_rqst_id;

        EXCEPTION 
        WHEN NO_DATA_FOUND THEN

        ROLLBACK;
        Pout_err_cd  := SQLCODE;
        Pout_err_msg := 'STAT_CHANGE_RESN_MISMATCH';  

        WHEN DUP_VAL THEN
        Pout_err_cd  := SQLCODE;
        Pout_err_msg := 'DUPLICATE';  



        WHEN OTHERS THEN

         ROLLBACK;

         Pout_err_cd  := SQLCODE;
         Pout_err_msg := SUBSTR(SQLERRM, 1 , 4000);    

      END PROC_SUBMIT_H;


END PKG_H;
/

You are trying to assign a value to a PL/SQL variable which is not big enough for it. For example, you have

...
Lv_rqst_id  NVARCHAR2(20);
...

And then:

...
SELECT fnc_gen_request_id 
              INTO Lv_rqst_id 
              FROM dual;
....

I don't know what is fnc_gen_request_id or what is returning, but check it out this kind of assignment in your code and try to testing it or wrap in a BEGIN ... EXCEPTION .. END structure to get a clue.

As well as the potential fnc_gen_request_id return size problem that @Aitor mentioned, you could also be putting a value into one out of your OUT parameters that the caller can't cope with. For example, when you put up to 4000 chars into Pout_err_msg, though that would only be a problem when an exception is encountered anyway, so Pout_Rqst_Id or Lv_rqst_id might be more likely. You might also want to check that you're using VARCHAR and NVARCHAR consistently. (Incidentally, should Pout_err_cd be declared as a number?)

You're trying to insert too many characters into a string (buffer) variable.

Both blocks below will raise ORA-06502: PL/SQL: numeric or value error: character string buffer too small. Your problem is a variation of these simple cases.

declare
  str varchar2(2); /* Only room for two characters. */
begin
  select 'foo' into str from dual; /* Three is one too many. */
end;
/

declare
  str varchar2(2); /* Only room for two characters. */
begin
  str := 'foo'; /* Three is one too many. */
end;
/