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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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>