Oracle db gives ORA-01722 for seemingly NO REASON AT ALL

Question

I'm trying to use an Oracle database with ado.net, and it is proving a painful experience. I use Oracle Client (Oracle.Data namespaces).

The following query runs fine from a query window:

UPDATE PRINT_ARGUMENT SET VALUE = 'Started' WHERE REQUEST_ID = 1 AND KEYWORD = '{7D066C95-D4D8-441b-AC26-0F4C292A2BE3}'

When I create an OracleCommand however the same thing blows up with ORA-01722. I can't figure out why.

var cmd = cnx.CreateCommand();
cmd.CommandText = @"
UPDATE PRINT_ARGUMENT
SET VALUE = :value 
WHERE REQUEST_ID = :requestID AND KEYWORD = :key";


cmd.Parameters.Add(new OracleParameter("requestID", (long)1);
cmd.Parameters.Add(new OracleParameter("key", "{7D066C95-D4D8-441b-AC26-0F4C292A2BE3}");
cmd.Parameters.Add(new OracleParameter("value", "Started");

cnx.Open();
try     { int affected = cnx.ExecuteNonQuery(); }
finally { cnx.Close(); }

When I inspect the command in the debugger, the parameters appear to have mapped to the correct types: requestID has OracleDbType.Int64, key and value are both OracleDbType.Varchar2. The values of the parameters are also correct.

This gets even stranger when you consider that I have other queries that operate on the exact same columns (requestID, keyword, value) using the same approach - and they work without a hiccup.

For the record, the column types are requestID NUMBER(10,0); key VARCHAR2(30); value VARCHAR2(2000).

According to Oracle, ORA-01722 'invalid number' means a string failed to convert to a number. Neither of my string values are numbers, neither of the OracleParameters created for them are numeric, and neither

Answer 1

Since you are using named parameters, you have to tell the Oracle client about it. Otherwise your parameters are mixed up (key is assigned to :value):

OracleParameter parameter = new OracleParameter("requestID", (long)1);
parameter.BindByName = true;
cmd.Parameters.Add(parameter);

It's a strange and unexpected behavior, but that's how it is.

Answer 2

By default, ODP.NET binds parameters by position, not by name, even if they have actual names in the SQL (instead of just ?). So, you are actually binding requestID to :value, key to :requestID and value to :key.

Correct the order of cmd.Parameters.Add in your code, or use BindByName to tell ODP.NET to use the parameter names.