.NET Core中用Dapper调用Oracle存储过程报PLS-00306错误求助

存储过程定义 #

PROCEDURE get_freeboxlocations_cust (
      bundleid     IN       VARCHAR2,
      cityid       IN       VARCHAR2,
      customerid   IN       VARCHAR2,
      statusid     IN       VARCHAR,
      outcursor    OUT      sys_refcursor
   )
   AS
      invalid_parameter   EXCEPTION;
      v_isrange           VARCHAR2 (2);
   BEGIN
      IF (bundleid = '' OR cityid = '' OR customerid = '')
      THEN
         RAISE invalid_parameter;
      END IF;
...

.NET Core调用代码 #

using (var dbConn = new OracleConnection(_configuration.GetConnectionString("OraConnection")))
{
    dbConn.Open();
    var parameters = new { bundleid = "LI", cityid = "DXB", statusid = '5', customerid = "101" };
    var query = dbConn.Query<BoxLocation>("POBOXSERVICE_PACK.GET_FREEBOXLOCATIONS_CUST", parameters, commandType: System.Data.CommandType.StoredProcedure);
}

报错信息 #

Oracle.ManagedDataAccess.Client.OracleException (0x80004005):
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'GET_FREEBOXLOCATIONS_CUST' ORA-06550: line 1,
column 7: PL/SQL: Statement ignored at
OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32&
cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType
sqlStatementType, Int32 arrayBindCount, OracleException&
exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean
bFirstIterationDone) at
OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String
commandText, OracleParameterCollection paramColl, CommandType
commandType, OracleConnectionImpl connectionImpl,
OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64
clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]
scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection&
bindByPositionParamColl, Boolean& bBindParamPresent, Int64&
internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException&
exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1
adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

错误原因 #

• 缺少输出参数：存储过程定义了5个参数（4个输入、1个输出游标），但.NET代码仅传递了4个输入参数，未声明outcursor输出参数，导致参数数量不匹配。
• 参数类型不匹配：存储过程中statusid为VARCHAR类型，.NET代码中使用字符字面量'5'（C#的char类型），对应Oracle的CHAR类型，与存储过程要求的VARCHAR不兼容。

解决方法 #

显式声明所有参数，包括输出游标，并确保参数类型、方向与存储过程定义完全匹配：

using (var dbConn = new OracleConnection(_configuration.GetConnectionString("OraConnection")))
{
    dbConn.Open();

    var parameters = new OracleParameter[]
    {
        new OracleParameter("bundleid", OracleDbType.Varchar2, "LI", System.Data.ParameterDirection.Input),
        new OracleParameter("cityid", OracleDbType.Varchar2, "DXB", System.Data.ParameterDirection.Input),
        new OracleParameter("customerid", OracleDbType.Varchar2, "101", System.Data.ParameterDirection.Input),
        new OracleParameter("statusid", OracleDbType.Varchar2, "5", System.Data.ParameterDirection.Input),
        new OracleParameter("outcursor", OracleDbType.RefCursor, System.Data.ParameterDirection.Output)
    };

    var query = dbConn.Query<BoxLocation>("POBOXSERVICE_PACK.GET_FREEBOXLOCATIONS_CUST", parameters, commandType: System.Data.CommandType.StoredProcedure);
}

说明：

1. 新增outcursor参数，指定类型为OracleDbType.RefCursor，方向为Output，匹配存储过程的输出游标定义。
2. 将statusid的'5'改为字符串"5"，对应Oracle的VARCHAR类型。
3. 使用OracleParameter数组替代匿名对象，确保参数的类型、方向、名称完全符合存储过程要求。

内容的提问来源于stack exchange，提问作者Yener