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

Posted by Blake on 6/21/2012

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
                                last_login = :last_login,
                                visits = visits + 1
                                WHERE username = :username
            cmd.CommandText = sql
            cmd.BindByName = True
            cmd.Parameters.Add("last_login", Now)
            cmd.Parameters.Add("username", Me.Username)
    End Using