Invalid parameter binding calling Oracle package procedure using Dapper

Question

0.00/5 (No votes)

See more:

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:

C#

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:

SQL

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;

Posted 17-Apr-14 3:09am

littleGreenDude

Add a Solution

2 solutions

Solution 1

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

Posted 17-Apr-14 3:22am

DamithSL

Updated 17-Apr-14 3:28am

v2

Comments

littleGreenDude 17-Apr-14 11:44am

Thank you for the response. I saw the stack overflow question/response yesterday. I liked Dan Smith's response about creating OracleDynamicParmeters. And Vijay's blog takes it the rest of the way, so that was really helpful.

However, to get the dynamic parameters to add correctly, I had to switch from the Oracle.DataAccess.Client to the Oracle.ManagedDataAccess.Client. In doing this, it no longer connects to the database. I'm getting the following error:

ORA-12154: TNS:could not resolve the connect identifier specified

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

littleGreenDude · Accepted Answer · 2014-05-01T03:54:00

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:

C#

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

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

C#

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>

Invalid parameter binding calling Oracle package procedure using Dapper

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Invalid parameter binding calling Oracle package procedure using Dapper

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us