出现错误“ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小”

Question

我有一个下面的软件包出现错误，

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

请让我知道这里出了什么问题。

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
                 ,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;
/

Answer 1

您正在尝试为 PL/SQL 变量分配一个值，但该值对于它而言不够大。例如，您有

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

然后：

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

我不知道什么是 fnc_gen_request_id 或返回什么，但请检查代码中的此类分配并尝试测试它或包装在 BEGIN ... EXCEPTION .. END 结构中以获得线索。

Answer 2

除了 @Aitor 提到的潜在的 fnc_gen_request_id 返回大小问题之外，您还可以将一个值放入调用者无法处理的 OUT 参数之一中。例如，当您在 Pout_err_msg 中放入最多 4000 个字符时，尽管只有在遇到异常时才会出现问题，因此 Pout_Rqst_Id 或 Lv_rqst_id 可能更有可能。您可能还想检查您是否一致地使用 VARCHAR 和 NVARCHAR。 （顺便问一下，Pout_err_cd 应该声明为数字吗？）

Answer 3

您试图在字符串（缓冲区）变量中插入太多字符。

下面的两个块都会引发ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小。您的问题是这些简单情况的变体。

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;
/