ODP.Net / ora-01858: a non-numeric character was found where a numeric was expected

Oracle is full of gotchas. Most ADO.Net providers will allow you to access the parameters inside of a IDbCommand either by index or by the name of the parameter. For instance, if you create a parameter and add it with a name of username then it will refer to the @username or :username inside of the SQL and you can also access that parameter via that key.

Over the last few weeks, I’ve struggled to consistently get date’s and timestamp’s to work consistently with ODP.Net via Oracle. Very frequently I could take the SQL that failed via the ODP.Net ADO.Net provider, past it into my SQL editor connected directly to Oracle and run the SQL without a problem.

Here is the problem (in my case)! In ODP.Net the default behavior is that parameters have to be added into the command in the same order that they’re listed in the SQL (even, when you’re using key’s). No other provider I’ve seen works like this. The solution, even if it should be obvious that you’re binding by name (when you provide the, *dramatic music* names to bind by), you still have to tell ODP the obvious. On your OracleCommand there is a property called "BindByName`. Set this equal to true:

Here is a basic example (In VB.Net):

Using cmd As Oracle.DataAccess.Client.OracleCommand = Me.DbConnection.CreateCommand
    Dim sql As String = <string>
                            UPDATE users_t
                            SET
                            last_login = :last_login,
                            visits = visits + 1
                            WHERE username = :username
                        </string>
        cmd.CommandText = sql
        cmd.BindByName = True
        cmd.Parameters.Add("last_login", Now)
        cmd.Parameters.Add("username", Me.Username)
        cmd.ExecuteNonQuery()
End Using