在存储过程中使用可选参数时出错"PL/SQL: numeric or value error: character string buffer too small"

Question

我使用以下存储过程为特定关键字返回递增的键值。它采用可选参数 Y 或 N 来确定结果是否应以另一个值为前缀。

create or replace
PROCEDURE                        "ASGETPRIMARYKEY" 
(
  p_ObjectName IN LASTPRIMARYKEY.OBJECTNAME%type ,
  p_PrimaryKey OUT LASTPRIMARYKEY.NEXTKEY%type,
  useSitePrefix IN NVARCHAR2 Default 'Y'
)
AS
   v_transcount NUMBER := 1;
   v_sys_error NUMBER := 0;
   v_SitePrefix NVARCHAR2(4000);
   v_LastPrimaryKey NUMBER(10,0);
   -- Error Handling Variables
   v_LocalTran NUMBER(1,0);
   v_Error NUMBER(10,0);
BEGIN
   NULL/*TODO:SET XACT_ABORT ON*/;
   NULL/*TODO:SET NOCOUNT ON*/;
   -- STEP 0: Start the transaction
   -- If procedure doesn't need to begin a transaction this can be set to 0
   IF v_transcount > 0 THEN
      v_LocalTran := 0;
   ELSE
      v_LocalTran := 1;
   END IF;
   IF v_LocalTran = 1 THEN
      SET TRANSACTION READ WRITE;
      v_transcount := v_transcount + 1;
   END IF;
   -- STEP 1: Get @SitePrefix
   SELECT settingtext
     INTO v_SitePrefix
     FROM systemsetting
     WHERE settingdescription = 'Site Id Prefix';
   -- Check if there were any errors
   v_Error := v_sys_error;
   IF v_Error != 0 THEN
   BEGIN
      raise_application_error( -20002, 'Error whilst processing GetPrimaryKey.(1a)' || cast(v_error as NVARCHAR2));
      GOTO ErrExit;
   END;
   END IF;
   -- STEP 1b: Check SitePrefix exists
   -- Rollback the transaction if SitePrefix doesn't exist in systemsetting
   IF v_SitePrefix IS NULL THEN
   BEGIN
      raise_application_error( -20002, 'Error whilst processing GetPrimaryKey.(1b)' || cast(v_error as NVARCHAR2));
      GOTO ErrExit;
   END;
   END IF;
   BEGIN
      -- STEP 2: Set NextKey for requested ObjectName
      UPDATE LastPrimaryKey
         SET NextKey = (NextKey + 1)
         WHERE objectname = p_ObjectName;
   EXCEPTION
      WHEN OTHERS THEN
         v_sys_error := SQLCODE;
   END;
   BEGIN
      -- Check if there were any errors
      v_Error := v_sys_error;
   EXCEPTION
      WHEN OTHERS THEN
         v_sys_error := SQLCODE;
   END;
   IF v_Error != 0 THEN
   BEGIN
      raise_application_error( -20002, 'Error whilst processing GetPrimaryKey.(2)' || cast(v_error as NVARCHAR2));
      GOTO ErrExit;
   END;
   END IF;
   -- STEP 3: Set NextKey for for requested ObjectName
   SELECT NextKey - 1
     INTO v_LastPrimaryKey
     FROM LastPrimaryKey
     WHERE objectname = p_ObjectName;
   BEGIN
      -- Check if there were any errors
      v_Error := v_sys_error;
   EXCEPTION
      WHEN OTHERS THEN
         v_sys_error := SQLCODE;
   END;
   IF v_Error != 0 THEN
   BEGIN
      raise_application_error( -20002, 'Error whilst processing GetPrimaryKey.(3)' || cast(v_error as NVARCHAR2));
      GOTO ErrExit;
   END;
   END IF;
   -- STEP 4: Check SitePrefix exists
   -- Rollback the transaction if SitePrefix doesn't exist in systemsetting
   IF v_LastPrimaryKey IS NULL THEN
   BEGIN
      raise_application_error( -20002, 'Error whilst processing GetPrimaryKey.(4)' || cast(v_error as NVARCHAR2));
      GOTO ErrExit;
   END;
   END IF;
   -- STEP 5: Set @p_PrimaryKey by adding prefix
   IF useSitePrefix = 'y' THEN
      p_PrimaryKey := (CAST(v_SitePrefix || CAST(v_LastPrimaryKey AS NVARCHAR2) AS NUMBER));
   ELSE
      p_PrimaryKey := v_lastprimarykey;
   END IF;
   -- Check if there were any errors
   v_Error := v_sys_error;
   IF v_Error != 0 THEN
   BEGIN
      raise_application_error( -20002, 'Error whilst processing GetPrimaryKey.(5)' || cast(v_error as NVARCHAR2));
      GOTO ErrExit;
   END;
   END IF;
   -- STEP 6: If we reach this point, the commands completed successfully
   --         Commit the transaction....
   -- Normal exit
   IF v_LocalTran = 1 THEN
      COMMIT;
      v_transcount := v_transcount - 1;
   END IF;
   -- Error Exit
   <<ErrExit>>
   IF v_LocalTran = 1 THEN
      ROLLBACK;
      v_transcount := v_transcount - 1;
   END IF;
END;

使用以下 vb 代码从企业库调用存储的过程：

        Dim sqlCommand As String = "asGetPrimaryKey"
        Dim _db As Database = EnterpriseLibraryContainer.Current.GetInstance(Of Database)(ASDBMSS.clsDBMSS.ConnectionStringName)
        Dim DbCommand As System.Data.Common.DbCommand = _db.GetStoredProcCommand(sqlCommand)
        ' Add primary keys to command wrapper.
        _db.AddInParameter(DbCommand, "p_objectName", DbType.String, ObjectName)
        If pbIgnoreSiteIndex Then
            _db.AddInParameter(DbCommand, "useSitePrefix", DbType.String, "Y")
        Else
            _db.AddInParameter(DbCommand, "useSitePrefix", DbType.String, "N")
        End If
        _db.AddOutParameter(DbCommand, "p_PrimaryKey", DbType.Int32, 8)
        _db.ExecuteNonQuery(DbCommand)
        Dim _result As String = _db.GetParameterValue(DbCommand, "p_PrimaryKey").ToString
        lcl_NextKey = CInt(_result)
        result = 1

当我传入可选参数时，问题就出现了。如果我跳过带有可选参数的行，它可以正常工作。如果我使用SQL服务器而不是Oracle，它可以正常工作。如果我传递参数并使用 Oracle，我会收到以下错误消息：

ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小ORA-06512：在第 1 行

有什么建议吗？

Answer 1

将v_LastPrimaryKey从 number（10,0） 更改为与 LAST PRIMARY 键p_PrimaryKey相同。下一个键%类型，

其次，您传递的是 Y 或 N，但在代码中比较 y。所以流总是在"其他"中，你没有使用演员。希望有帮助。干杯五

Answer 2

我发现了问题！

只要名称匹配，SQL 服务器就不关心参数的传递顺序。甲骨文似乎确实如此。

我将调用它们的顺序更改为：

        _db.AddInParameter(DbCommand, "p_objectName", DbType.String, ObjectName)
        _db.AddOutParameter(DbCommand, "p_PrimaryKey", DbType.Int32, 8)
        If pbIgnoreSiteIndex Then
            _db.AddInParameter(DbCommand, "useSitePrefix", DbType.String, "Y")
        Else
            _db.AddInParameter(DbCommand, "useSitePrefix", DbType.String, "N")
        End If
        _db.ExecuteNonQuery(DbCommand)

它现在可以正常工作