Dapper and Oracle parametrized query - ORA-01036: illegal variable name/number

c# dapper oracle

Question

I'm currently trying my hand on Dapper. The following code works flawlessly:

using (var conn = 
    new OracleConnection(
        "Uid=dbusr;Pwd=dbusrpwd;Server=oraserver;"))
{
    var col = 
        conn.Query<User>(
        "SELECT * FROM Users WHERE UserName = 'uid01'"
        , null)
        .ToList();
}

But if instead of using a hardcoded parameter I try to pass it through a parametrized query plus an anonymous class, like this:

using (var conn = 
    new OracleConnection(
        "Uid=dbusr;Pwd=dbusrpwd;Server=oraserver;"))
{
    var col = 
        conn.Query<User>(
        "SELECT * FROM Users WHERE UserName = @Id"
        , new { Id = "uid01" })
        .ToList();
}

I receive the following error:

ORA-01036: illegal variable name/number

I searched around SO, but found no similar error reports; it seems I'm missing something terribly obvious but alas - I ran out of coffee. I would appreciate any hints.

Accepted Answer

In oracle's flavour of SQL, named parameters are prefixed with :. Try :Id instead of @Id.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why