如何在Oracle中将参数输出为varchar2

Question

我正在使用Oracle 10g

以下是我的包规范和主体：

CREATE OR REPLACE PACKAGE P_1 AS
    TYPE T_CURSOR IS REF CURSOR;
    PROCEDURE USP_1(SP_NUM OUT VARCHAR2);
END P_1;

CREATE OR REPLACE PACKAGE BODY P_1 AS
    PROCEDURE USP_1(SP_NUM OUT VARCHAR2) IS
    BEGIN
        SELECT to_char(concat(to_char(sysdate, 'yyyymm'),
                              to_char("ActionId".nextval, '000')))
            INTO SP_NUM FROM dual;
    END USP_1;
END P_1;

我的后端代码是

using (OracleConnection oracleConnection = new BaseRepository().Connection)
{
    oracleConnection.Open();

    OracleCommand command = new OracleCommand("P_1.USP_1", oracleConnection);

    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add("SP_NUM", OracleDbType.Varchar2, 50,
                           System.Data.ParameterDirection.Output);

    command.ExecuteNonQuery();  // exception arises at this line

    var Number = Convert.ToInt32(command.Parameters["SP_REQ_NUM"].Value.ToString());

这是我得到的异常。

ORA-06502: PL/SQL: numeric or value error

我不知道我在哪里做错了什么。

- Khurram Zulfiqar Ali

---

在 pl/sql 调试器中测试是否输出相同的错误，还是只有你的 C# 代码？因为我认为这是一个 C# 问题，而不是你的 plsql 问题。 - eric.itzhak

PL/SQL调试器显示了准确的结果，没有错误。但我仍然认为问题出在Oracle中的varchar2上。我以前从未使用过单个参数out作为varchar2。 - Khurram Zulfiqar Ali

Answer 1

OracleParameterCollection.Add 方法有不少于10个重载版本。看起来你误调用了一个错误的方法。

我认为你需要的是 Add(string, OracleDbType, int, object, ParameterDirection)，在这种情况下，你只是缺少一个 object 参数的值。该参数应包含你使用的 Oracle 参数的初始值。然而，在你的情况下，初始值并不重要，因为它是一个“out”参数。在 50 之后添加 null，你的存储过程调用就应该成功了。

你所调用的是 Add(string, OracleDbType, object, ParameterDirection)。大小为 50 的参数被解释为参数的初始值。我不确定如何解释 Oracle 返回的错误（“数值或值错误”）——这对我来说意味着 Oracle 尝试将字符串转换为数字并失败了。也许值 50 覆盖了类型 OracleDbType.Varchar2，因此 Oracle 期望一个数字而不是一个字符串？

我还发现了另外几个问题：

• command.Parameters["SP_REQ_NUM"] 应该改为 command.Parameters["SP_NUM"] 吗？
• 你的存储过程不返回数字；对如 201405 001 这样的字符串调用 Convert.ToInt32 将失败。

- Luke Woodward

---

请注意，代码不会到达您提到的另外两行代码。在执行附加错误行之前，它会捕获异常。另一方面，“Add”方法确实不少于10个，但是让我告诉您一种使用所需方法的简短方式：“只需向该方法提供正确的参数，它就会自动调用所需的方法”。 - Khurram Zulfiqar Ali

@KhurramZulfiqarAli：很高兴能帮忙。一旦我让存储过程调用起来，我就找到了另外两个问题。显然，如果存储过程调用失败，你永远不会遇到它们。 - Luke Woodward