Why am I getting an ORA-01722 (invalid number)?

Question

I've been using a parameterized query to insert values into an Oracle table, like so:

var q = "insert into MyTable(Field1, Field2...) values(:Field1, :Field2...)";
var cmd = new OracleCommand(q, conn); // conn is a pre-existing connection
cmd.Parameters.Add("Field1", field1Val); 
cmd.Parameters.Add("Field2", field2Val);
// etc...
cmd.ExecuteNonQuery();

This has been working fine, but suddenly this has stopped working, and I am getting Oracle error ORA-01722 (invalid number). I have checked the parameters, and all numbers are unquestionably valid numbers. I even substituted dummy values for any nulls, and I still get the error. I tried the same query in direct sql (using OraDeveloper Studio), and it works, even with the identical parameters.

How do I track this one down?

EDIT: per request in the comments, here's the create table statement:

CREATE TABLE ALPHA.VISITFINDINGS (
  ID NUMBER(12),
  VISITID NUMBER(12) NOT NULL,
  DESCRIPTION VARCHAR2(100),
  CUSTOMIMAGE CLOB,
  VISUALFINDINGSSECTIONMAPID NUMBER(12),
  FINDINGSID NUMBER(12),
  CONSTRAINT FK_VISITFINDINGS_AREA FOREIGN KEY (VISUALFINDINGSSECTIONMAPID)
    REFERENCES ALPHA.VISUALFINDINGSSECTIONMAP(VISUALFINDINGSSECTIONMAPID),
  CONSTRAINT FK_VISITFINDINGS_FINDINGS FOREIGN KEY (FINDINGSID)
    REFERENCES ALPHA.FINDINGS(FINDINGSID),
  CONSTRAINT FK_VISITFINDINGS_VISIT FOREIGN KEY (VISITID)
    REFERENCES ALPHA.VISITS(VISITID),
  CONSTRAINT PK_VISITFINDINGS PRIMARY KEY (ID))
TABLESPACE USERS
STORAGE (
  INITIAL 64K
  MAXEXTENTS UNLIMITED
)
LOGGING;

Answer 1

I have given answer credit already, but I think it's worth mentioning here exactly what the root of my problems was, in case anyone else finds this item while looking for an answer to their own problem.

The problem is that the C# implementation of parameterized queries for Oracle contains a serious and potentially dangerous bug - a real "pit in the public domain":

It doesn't matter what you name your parameters; they have to be added in the order in which they appear in the query.

See more here.

Answer 2

When you say you checked the parameters do you mean the Parameters collection on the SqlCommand class? You might be falling foul of this note on the SqlParameter page:

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates. Copy

Parameter = new SqlParameter("@pname", Convert.ToInt32(0));

If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter (string, SqlDbType) constructor overload.

I'd suggest you use something like

cmd.Parameters.Add(
   new SqlParameter("Field1", SqlDbType.Int32) { Value = field1Val });

instead to explicitly set the type.

Answer 3

command.BindByName = true;

Please see this explination.