使用Dapper调用Oracle包过程的参数绑定无效 [英] Invalid parameter binding calling Oracle package procedure using Dapper

问题描述

使用Dapper进行Oracle过程调用时出错：



参数绑定无效参数名称：o_resultset



我没有看到除DbType.Object之外的其他选项吗？有什么建议吗？



通话方式：

  public 列表<客户> SearchCustomers（ string  searchCriteria， string  showDetailsFlag）
 {
 尝试 
 {
 使用（ var  conn =  new  OracleConnection（ConnectionString））
 {
 conn.Open（）; 
  var  p =  new  DynamicParameters（）; 
 p.Add（  pSearchCriteria，searchCriteria，dbType：DbType。 String ，direction：ParameterDirection.Input）; 
 p.Add（  pShowDetails，showDetailsFlag，dbType：DbType。 String ，direction：ParameterDirection.Input）; 
 p.Add（  o_resultset，dbType：DbType。 Object ，direction：ParameterDirection.Output）; 
  var  searchResults = conn.Query（sql：CommonConstants.ProcedureConstants.PKG_SEARCH_CUSTOMERS，param：p，commandType：CommandType.StoredProcedure）
 .Select（ t = >   new 客户
 {
客户名= t.CUSTOMER_NAME，
 CompanyName = t.COMPANY_NAME，
 PhoneNumber = t.PHONE_NUMBER 
}）。ToList（）; 
 conn.Close（）; 
  return  searchResults; 
} 
} 
  catch （例外情况）
 {
 抛出 ex; 
} 
} 
 

Oracle程序：

 过程 Search_Customers（pSearchCriteria  IN  Varchar2，pShowDetails  IN  Varchar2，o_resultset OUT SYS_REFCURSOR） IS  
 开始 
  DECLARE  
 sql_stmt VARCHAR2（ 1000 ）; 
 开始 
 如果 pShowDetails =  '  Y' 然后 
 sql_stmt：= '  SELECT US.CID，FIRST_NAME || ''''|| LAST_NAME AS CUSTOMER_NAME，COMPANY_NAME，PHONE_NUMBER来自TBL_USER US，TBL_PORTFOLIO UP在哪里US.CID = UP.CID（+）'; 
 其他 
 sql_stmt：= ' 选择DISTINCT US.CID，FIRST_NAME || ''''|| LAST_NAME AS CUSTOMER_NAME，COMPANY_NAME，PHONE_NUMBER来自TBL_FIRST_USER US，TBL_PORTFOLIO UP在哪里US.CID = UP.CID（+）'; 
 结束 如果; 
 
 如果 LENGTH（pSearchCriteria）>  0  然后 
 sql_stmt：= sql_stmt || ' ' || pSearchCriteria; 
  open  o_resultset  for  sql_stmt; 
 结束 如果; 
 结束; 
 结束 Search_Customers; 
 

解决方案

您可能需要自定义实现 Dapper.SqlMapper.IDynamicParameters 支持 Oracle.DataAccess.Client.OracleDbTypes 和ref游标一样。



查看以下链接



- http://blog.vijay.name/2012/07/dapper-micro-orm-for-oracle-and-microsoft -net / [ ^ ]

- http://stackoverflow.com/questions/7390015/using-dapper-with-oracle-stored-procedures-which-return-cursors

它适用于DynamicParameters（不需要OracleDynamicParameters）。使用dbType：DbType.Object作为输出光标。



我还需要使结果对象具体化：

  var  searchResults =  new  List< customer>（）; < /  客户 >  

没有改变Oracle sproc，最终调用代码如下：

  public 列表< customer> SearchCustomers（ string  searchCriteria， string  showDetailsFlag）
 {
 尝试 
 {
  var  searchResults =  new  List< customer>（）; 
 使用（ var  conn =  new  OracleConnection（DatabaseConnectionString））
 {
 conn.Open（）; 
  var  p =  new  DynamicParameters（）; 
 p.Add（  pSearchCriteria，searchCriteria，DbType。 String ，ParameterDirection.Input）; 
 p.Add（  pShowDetails，showDetailsFlag，DbType。 String ，direction：ParameterDirection.Input）; 
 p.Add（  o_resultset，dbType：DbType。 Object ，direction：ParameterDirection.Output）; 
 searchResults = conn.Query（sql：CommonConstants.ProcedureConstants.PKG_SEARCH_CUSTOMERS，param：p，commandType：CommandType.StoredProcedure）
 .Select（c = >   new 客户
 {
 CustomerId = c.CWID，
 CompanyName = c.COMPANY_NAME，
 PhoneNumber = c.PHONE_NUMBER 
}）。ToList（）; 
 conn.Close（）; 
  return  searchResults; 
} 
} 
  catch （例外情况）
 {
 抛出 ex; 
} 
} < /   customer  >  < /  客户  >  

Getting an error making an Oracle procedure call using Dapper:

Invalid parameter binding Parameter name: o_resultset

I don't see another option that will work other than DbType.Object? Any advice?

Calling method:

public List<Customer> SearchCustomers(string searchCriteria, string showDetailsFlag)
 {
     try
     {
         using (var conn = new OracleConnection(ConnectionString))
         {
             conn.Open();
             var p = new DynamicParameters();
             p.Add("pSearchCriteria", searchCriteria, dbType: DbType.String, direction: ParameterDirection.Input);
             p.Add("pShowDetails", showDetailsFlag, dbType: DbType.String, direction: ParameterDirection.Input);
             p.Add("o_resultset", dbType: DbType.Object, direction: ParameterDirection.Output);
             var searchResults = conn.Query(sql: CommonConstants.ProcedureConstants.PKG_SEARCH_CUSTOMERS, param: p, commandType: CommandType.StoredProcedure)
                 .Select(t => new Customer
                 {
                     CustomerName = t.CUSTOMER_NAME,
                     CompanyName = t.COMPANY_NAME,
                     PhoneNumber = t.PHONE_NUMBER
                 }).ToList();
             conn.Close();
             return searchResults;
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

Oracle procedure:

Procedure Search_Customers(pSearchCriteria IN Varchar2, pShowDetails IN Varchar2, o_resultset OUT SYS_REFCURSOR) IS
Begin
    DECLARE
    sql_stmt VARCHAR2(1000);
    Begin
        If pShowDetails = 'Y' Then
            sql_stmt := 'SELECT US.CID, FIRST_NAME || '' '' || LAST_NAME AS CUSTOMER_NAME, COMPANY_NAME,PHONE_NUMBER FROM TBL_USER US, TBL_PORTFOLIO UP WHERE US.CID = UP.CID(+)';
        Else
            sql_stmt := 'SELECT DISTINCT US.CID, FIRST_NAME || '' '' || LAST_NAME AS CUSTOMER_NAME,COMPANY_NAME, PHONE_NUMBER FROM TBL_FIRST_USER US, TBL_PORTFOLIO UP WHERE US.CID = UP.CID(+)';
        End If;

        If LENGTH(pSearchCriteria) > 0 Then
            sql_stmt := sql_stmt || ' ' || pSearchCriteria;
            open o_resultset for sql_stmt;
        End If;
    End;
End Search_Customers;

解决方案

you may need to have custom implementation of Dapper.SqlMapper.IDynamicParameters which supports Oracle.DataAccess.Client.OracleDbTypes like ref cursors.

check below links

- http://blog.vijay.name/2012/07/dapper-micro-orm-for-oracle-and-microsoft-net/[^]
- http://stackoverflow.com/questions/7390015/using-dapper-with-oracle-stored-procedures-which-return-cursors

It works with DynamicParameters (no need for OracleDynamicParameters). Using dbType: DbType.Object for the output cursor.

I also needed to make the results object specific:

var searchResults = new List<customer>();</customer>

No change to the Oracle sproc, final calling code looks like this:

public List<customer> SearchCustomers(string searchCriteria, string showDetailsFlag)
        {
            try
            {
                var searchResults = new List<customer>();
                using (var conn = new OracleConnection(DatabaseConnectionString))
                {
                    conn.Open();
                    var p = new DynamicParameters();
                    p.Add("pSearchCriteria", searchCriteria, DbType.String, ParameterDirection.Input);
                    p.Add("pShowDetails", showDetailsFlag, DbType.String, direction: ParameterDirection.Input);
                    p.Add("o_resultset", dbType: DbType.Object, direction: ParameterDirection.Output);
                    searchResults = conn.Query(sql: CommonConstants.ProcedureConstants.PKG_SEARCH_CUSTOMERS, param: p, commandType: CommandType.StoredProcedure)
                        .Select(c => new Customer
                        {
                            CustomerId = c.CWID,
                            CompanyName = c.COMPANY_NAME,
                            PhoneNumber = c.PHONE_NUMBER
                        }).ToList();
                    conn.Close();
                    return searchResults;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }</customer></customer>

这篇关于使用Dapper调用Oracle包过程的参数绑定无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！