C# Oracle 9i - Updating double value using parameter not working

Question

I'm using .net 4.5.1, windows command line application using Oracle.DataAccess (ODP.NET) x86 2.122.1.0.

where tran is a Oracle database transaction.

OracleCommand updCmd = 
       new OracleCommand("update mytable set price =0.5 where anseq = :p_id", tran.Connection);
updCmd.Transaction = tran;
updCmd.Parameters.Add("p_id", itemId);
var count= updCmd.ExecuteNonQuery();

count==1

whereas:

OracleCommand updCmd = 
       new OracleCommand("update mytable set price =:p_price where anseq = :p_id", tran.Connection);
updCmd.Transaction = tran;
updCmd.Parameters.Add("p_id", itemId);
updCmd.Parameters.Add("p_price", 0.5);
var count= updCmd.ExecuteNonQuery();

count==0

Even explicitly setting the parameter type:

updCmd.Parameters.Add(
     new OracleParameter("p_price", 
          OracleDbType.Double, 
          22, System.Data.ParameterDirection.Input, 
          false, 16, 2, "myprice", 
          System.Data.DataRowVersion.Default, 0.5));

Results in no records updated.

Has anyone experienced this problem before or has idea what might be causing it?

TIA

have you tried to change the order of Parameters.Add? to add first price, then itemId? — Florin Ghita, Oct 22 '15 at 08:49
no, why would i do that!!!! but of course it works. if you know why, please post as an answer — sambomartin, Oct 22 '15 at 09:19
glad it helped you. AFAIK, in pl/sql the order or binds is very important. In other words, if you don't bind by name you bind by order. — Florin Ghita, Oct 22 '15 at 13:13
Are you using version 2.112.1.0 or 2.122.1.0 of Oracle.DataAccess (ODP.NET) x86? While using Oracle.DataAccess (ODP.NET) x86 2.112.1.0 to connect to Oracle 9i, it throws exception stating that oracle 9i version is no longer supported. Can you let me know ho did you get it to work? — rageit, Jul 11 '16 at 16:34
Still the same using Oracle.ManagedDatataAccess.Client 19.16.0... — MensSana, Aug 12 '22 at 18:09

score 1 · Answer 1 · edited May 23 '17 at 12:08

Ok, well credit goes to Florin for highlighting the problem.

I've done some more digging and found the problem was caused by design!

The OracleCommand object has a BindByName property which is false by default.

As seen in this SO post C# parameterized queries for Oracle - serious & dangerous bug! you can see by default the parameter names are completely ignored by the ODP.NET driver unless the BindByName property is true.

The answer to my question, as Florin identified was to change the order in which the parameters were added - OR - as I've subsequently found out to set the BindByName=true.

C# Oracle 9i - Updating double value using parameter not working

1 Answers1