I found out that ODP.Net for Oracle 10g behaves in strange way for Oracle Parameter that are passed on to oracle command. It always expects parameters to be added in the same order as they are originally available on stored proc in db.

So, if a sp (stored proc) expects two parameters, first being “pId” and second being “pName” and you use the following code:

oraCommand.Parameters.Add(new OracleParameter(“pId”, OracleDbType.Int64, “10001”,

ParameterDirection.Input));

oraCommand.Parameters.Add(new OracleParameter(“pName”, OracleDbType.Varchar2, “John Doe”,

ParameterDirection.Input));

It works fine since the first parameter is of type Int64 (as expected) and the second parameter is of type Varchar2. However if you reverse their orders ODP wouldn’t work.

oraCommand.Parameters.Add(new OracleParameter(“pName”, OracleDbType.Varchar2, “John Doe”,

ParameterDirection.Input));

oraCommand.Parameters.Add(new OracleParameter(“pId”, OracleDbType.Int64, “10001”,

ParameterDirection.Input));

This will raise an exception that would say “Invalid number or type of arguments passed ….etc etc”. Now this is a strange behavior because we always (at least in terms of ADO.Net ) expect that parameter names will be binded against the stored procs parameters. But this doesn’t happen in the world of ODP.Net. And if you pass parameters in right order, parameter names doesn’t even matter.

I don’t know if its just my bad assumption or bad ODP design or a bug for that matter. It definitely caused me a whole lot of annoyance and wasted my time. I actually ended up adding a unit test and passing each and every parameter manually to sp and then running it against new provider. Since I was adding those parameters manually I added them in the same order as that of sp and hence a successful insert. But as and when I resorted to xml file containing field definitions, it always failed. I even ended up creating excel sheets with formulas to compare parameter types and values because that’s what I thought were most likely to go wrong. Bad day I guess!

I really think its a bad design atleast. API should be intuitive and inline with general design of framework (in our case ADO.Net).