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

I think oracle has a different schema for named parameter, did you try :a instead of @a ? — Bernhard Kircher, Aug 10 '11 at 14:50
This is the schema for named parameters in dapper. Dapper should take care of different rdbms schemas — mrt181, Aug 10 '11 at 16:01
Ok I thought Dapper just uses Ado.Net and since the query is a string, the format of the parameters is relevant. Since I do not seem to have enough knowledge, just a last tip: http://stackoverflow.com/questions/6212992/using-dapper-with-oracle mentions problems with the oracle parameters (which sould be fixed now) - maybe you can check it. Good luck. — Bernhard Kircher, Aug 11 '11 at 06:39
Ok, I am stupid. Bernhard, you are absolutly right, it has to be :a Please enter your first comment as the accepted answer. m( — mrt181, Aug 11 '11 at 06:53
An upvote on my comment would have been nice, since it seemed to help... — Bernhard Kircher, Aug 11 '11 at 15:02
@Bernard just post your comment as answer so we can close the loop here. — Sam Saffron, Aug 15 '11 at 04:19

score 54 · Accepted Answer · edited Mar 28 '17 at 11:08

54

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

edited Mar 28 '17 at 11:08

Tolga Evcimen

7,112
11
58
91

answered Aug 18 '11 at 13:49

mrt181

5,080
8
66
86

For me, this is not an acceptable answer. I need Dapper/Dapper.Contrib to work universally with Sql Server and Oracle. Contrib is the worst offender here, as I don't control the bind parameter in the statement. The @/: character should be managed by Dapper. It's a good answer when supporting Oracle only, and for that I up voted the post. – Mark Longmire Apr 01 '21 at 00:37

score 7 · Answer 2 · answered Dec 19 '13 at 17:59

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" } });

score 3 · Answer 3 · answered Mar 15 '18 at 14:49

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 });

answered Mar 15 '18 at 14:49

Boguslaw Buczek

93
1
1
6

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

3 Answers3