企业库ODP.NET调用返回ORA-06502:PL/SQL:数字或值错误 [英] Enterprise Library ODP.NET call returns ORA-06502: PL/SQL: numeric or value error

问题描述

我有以下Oracle存储过程:

I have the following Oracle Stored Procedure:

PROCEDURE SP_ITEMEXISTS(pID IN NUMBER, pExists OUT CHAR) IS
BEGIN
        select CASE count(*) WHEN 0 THEN 'N' ELSE 'Y' END into pExists from items where id = pID;
END SP_ITEMEXISTS;

我使用以下代码从带有ODP.NET的Enterprise Library 6.0中调用它:

I'm calling it from Enterprise Library 6.0 with ODP.NET with the following code:

public bool ItemExists(int itemID)
{
    string procedureName = "SP_ItemExists";
    var database = new DatabaseProviderFactory().CreateDefault();
    bool returnValue = false;

    using (OracleCommand command = (OracleCommand)database.GetStoredProcCommand(procedureName))
        {
            command.Parameters.Add("pID", OracleDbType.Int32, itemID, ParameterDirection.Input);
            OracleParameter pExists = command.Parameters.Add("pExists", OracleDbType.Char, ParameterDirection.Output);

            database.ExecuteNonQuery(command);

            if (pExists.Value.ToString().ToUpper() == "Y")
                returnValue = true;
            else
                returnValue = false;
        }

    return returnValue;

}

我收到以下错误: ORA-06502:PL/SQL:数字或值错误

I receive the following error: ORA-06502: PL/SQL: numeric or value error

从Oracle SQL Developer调用存储过程有效.

Calling the Stored Procedure from Oracle SQL Developer works.

推荐答案

似乎是相同的问题，例如:

Seems to be the same problem like here: How to Convert Datetime to OracleTimeStamp

您必须指定返回值的大小，即

You must specify size of return value, i.e.

OracleParameter pExists = command.Parameters.Add("pExists", OracleDbType.Char, 1, null, ParameterDirection.Output);

这篇关于企业库ODP.NET调用返回ORA-06502:PL/SQL:数字或值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！