ORA-01722 with ODP and Command Parameters

This week I was working on a section of code using C#, the Oracle .NET Provider (ODP) and some good old parameters in a SQL statement.  The code was pretty straight forward (this is from memory without the assistance of intellisense– so apologies for any syntax errors):

string sql = ”INSERT INTO MyTable (TextField1, NumberField2) :param1 :param2″;

OracleCommand cmd = new OracleCommand(sql, this.conn);

cmd.Parameters.Add(”param2″, OracleDbType.Int32);
cmd.Parameters.Add(”param1″, OracleDbType.nVarChar2);

cmd.Parameters["param2"].value = 12;
cmd.Parameters["param1"].value = “My Text”;

cmd.ExecuteNonQuery();

Everything seemed good to go, but when I executed the query, I got ORA-01722: Invalid number. 

When you check out the appropriate page on  http://www.ora-code.com it reads, “The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.” 

Well, my parameter value was obviously a number, so I thought I was picking the wrong field type for the parameter.  I tried all sorts of things– Int64, Int16, Long, etc.  I always got the same error.   Then I learned a helpful little tidbit about the OracleCommand object in ODP:

Named parameters are turned off by default.

So even though I’m referring to my parameters by name and setting their values by name– when the SQL command was running, it was plugging the parameters in by the order in which you add them to the command object.  So in the above example, my value for param2 was what was getting plugged in for param1 and vice versa.  As a result, my SQL was trying to insert “My Text” into a number field.  Yup, I’d say that’s an invalid number!

Luckily, ODP does support named parameters (don’t believe news group postings from 2003 that say otherwise)– you have to turn it on with a cmd.BindByName = true; call.