title: Getting an Error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
body: <p>I have a below Package which is giving error </p>
  
  <pre><code>ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
  </code></pre>
  
  <p>Please let me know what is going wrong here.</p>
  
  <pre><code>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 &gt;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
                   ,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;
  /
  </code></pre>