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

It doesn't matter what types you use for the parameter. There are many automatic conversion in place. The problem has to be with the target table itself. Your question contains contradicting information regarding the column names (requestID vs. REQUEST_ID, key vs. KEYWORD). Please show the full DDL (CREATE TABLE statement). — Codo
I would guess it is matching the parameters in order rather than by name ? — Kevin Burton
@KevinBurton Yep, that was it. Luckily there is a way (OracleCommand.BindByName = true) to get a more sensible behavior from the provider. Also lucky is that I'm using my little db library to build commands, so I only had to add this in one place to get all my commands as they should be. Still think Oracle has screwed up here; if this should be a feature at all, it should definitely be OFF by default! — The Dag

Codo Codo · Accepted Answer · 2011-11-14T11:53:25

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.

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

2 Answers