C# parameterized queries for Oracle - serious & dangerous bug!

Question

This is an absolute howler. I cannot believe my own eyes, and I cannot believe nobody before me would have discovered this if it was a genuine bug in C#, so I'm putting it out for the rest of the developer community to tell me what I am doing wrong. I'm sure this question is going to involve me saying "DOH!" and smacking my head very hard with the palm of my hand - but here goes, anyway...

For the sake of testing, I have created a table Test_1, with script as follows:

CREATE TABLE TEST_1 (
  COLUMN1 NUMBER(12) NOT NULL,
  COLUMN2 VARCHAR2(20),
  COLUMN3 NUMBER(12))
TABLESPACE USERS
STORAGE (
  INITIAL 64K
  MAXEXTENTS UNLIMITED
)
LOGGING;

Now I execute the following code:

var conn = new OracleConnection("connectionblahblah");
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = 
  "insert into Test_1(Column1, Column2, Column3) " +
  "values(:Column1, :Column2, :Column3)";
var p = cmd.Parameters;
p.Add("Column1", 1);
p.Add("Column3", null);
p.Add("Column2", "record 1");
cmd.ExecuteNonQuery();

Whoa! I get an ORA-01722 error - "invalid number"! What's wrong, though? Column1 is numeric, and has a value of 1, so that's fine; Column2 is a string, and Column3 is a nullable column, so that shouldn't cause any trouble...

Now sit down for this one... the problem here is that Column3 and Column2 are transposed in the order in which they are added to the OracleParameterCollection. Switch them around, and presto! It works!

This, of course, leads me to the next obvious experiment... let's change that block of code for adding parameters like so:

p.Add("Foo", 1);
p.Add("Bar", "record 1");
p.Add("hahahahahahaha", null);

You think that'll work? Well guess what - it does!

I am sitting here absolutely stunned. I cannot believe what I am seeing, and I likewise cannot believe that nobody before me has discovered this behavior (unless I don't know how to use Google properly).

This is not just an annoyance - it is seriously dangerous. What would have happened if I'd transposed two columns of the same data type? I wouldn't have even got an error - I would have simply inserted the wrong data into the wrong columns, and been none the wiser.

Does anyone have any ideas for a workaround - other than just being careful not to add parameters in the wrong order?

Answer 1

This is not a bug but explicitly mentioned in Oracle ODP.Net documentation. In a OracleCommand class the parameters are bound by position as default. If you want to bind by name then set the property cmd.BindByName = true; explicitly.

Reference to Oracle documentation. http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666

Answer 2

Is that a typo that you have column3 being added before column2?

Because the colon syntax signifies a bind variable--name doesn't matter to BIND variables in PLSQL, they're populated in order of submission. Which would mean you'd be attempting to set column2 value as "record 1", which would explain the invalid number error...

You currently have:

p.Add("Column1", 1);
p.Add("Column3", null);
p.Add("Column2", "record 1");

...see if this alteration fixes your issue:

p.Add("Column1", 1);
p.Add("Column2", "record 1");
p.Add("Column3", null);

Getting Named Parameters to Work?

I have to defer to someone with more C# experience to explain how to get named parameters working. But I'm glad we confirmed that the colon appears to be interpreting as an Oracle BIND variable.

Answer 3

p.Add(":Column1", 1);
p.Add(":Column2", "record 1");
p.Add(":Column3", null);

//NOTE i have added : to the parameter names to be recognised by oracle data client