Why does Dapper throw an OracleException when i run a query or command with parameters?

Question

I am evaluation dapper but i already running into some problems.

I am trying to do this

using (IDbConnection connection = GetConnection())
{
    connection.Open();
    var result = connection.Query(
        "select * from myTable where ID_PK = @a;", new { a = 1 });
}

It throws an ORA-00936: missing expression OracleException at line 393 in the SqlMapper.cs

using (var reader = cmd.ExecuteReader())

When i remove the parameter i get the whole table into the result variable.

The query works without problems in sqldeveloper. I am using the Oracle.DataAccess Assembly 2.112.2.0

Answer 1

I think oracle has a different schema for named parameter, did you try :a instead of @a?

Answer 2

Yes, it works with ":" if we trying to insert records in oracle database table.

Just try like this:

var count = connection.Execute(@"INSERT INTO COMPANY_USER(UserId , UserName) values (:UserId, :UserName)", new[] { new { UserId = 1, UserName = "Sam" }, new { UserId = 2, UserName = "Don" }, new { UserId = 3, UserName = "Mike" } }); 

Answer 3

It also works with IN list:

var partialList = new List();

var list = await db.QueryAsync("select bla1, blah2 FROM tablename WHERE stringcolumn1 IN :ListofValues", new { ListofValues = partialList });