Parameterized query in Oracle trouble

Question

I'm using Oracle.DataAccess rather than the obsolete System.Data.OracleClient and I seem to be having trouble passing multiple parameters to my update query

This works

OracleCommand.CommandText = "UPDATE db SET column1 = :param1 WHERE column2 = 'Y'"
OracleCommand.Parameters.Add(New OracleParameter("param1", "1234"))

But I want to be able to pass multiple parameters

Here's my full code

    OracleConn.Open()
    OracleCommand = OracleConn.CreateCommand()
    OracleCommand.CommandText = "UPDATE db SET column1 = :param1 WHERE column2 = :param2"
    OracleCommand.CommandType = CommandType.Text
    OracleCommand.Parameters.Add(New OracleParameter("param1", "1234"))
    OracleCommand.Parameters.Add(New OracleParameter("param2", "Y"))
    OracleCommand.ExecuteNonQuery()

My SELECT query seems to work when passing multiple parameters but not the update one

Answer 1

To emulate the default behavior of the System.Data.OracleClient, you should set the OracleCommand to bind by name.

OracleCommand.BindByName = True

Answer 2

Try this, hope it works. It does compile.
Not sure if you also have to send a commit...
I always do this sort of thing through a stored procedure, so I have a commit after the update statement in the stored procedure.

Harvey Sather

        OracleConnection ora_conn = new OracleConnection("connection string");

        OracleCommand ora_cmd = new OracleCommand("UPDATE db SET column1 = :param1 WHERE column2 = :param2", ora_conn);
        ora_cmd.CommandType = CommandType.Text;
        ora_cmd.BindByName = true;

        ora_cmd.Parameters.Add(":param1", OracleDbType.Varchar2, "1234", ParameterDirection.Input);
        ora_cmd.Parameters.Add(":param2", OracleDbType.Varchar2, "Y", ParameterDirection.Input);

        ora_cmd.ExecuteNonQuery();                

Answer 3

Try newing up your OracleParameter with a the type specified. Set the value of the object before adding it to the parameters list.

var param1 = new OracleParameter( "param1", OracleType.Int32 );
param1.Value = "1234";

OracleCommand.Parameters.Add( param1 );

Answer 4

The first code block is correct: use a colon in front of the parameter name, but not in the first argument to OracleParameter.

If no errors are thrown, it could be that the UPDATE runs successfully, it just doesn't update any records based on the WHERE clause and its substituted parameter value. Try doing it on a test table with no WHERE clause in the UPDATE to make sure it does something.

Answer 5

Although I can't see anything wrong with your example, I wonder if you're being hit by the old BindByName problem. By default, ODP.NET binds parameters to the query in the order in which they are added to the collection, rather than based on their name as you'd like. Try setting BindByName to true on your OracleCommand object and see if that fixes the problem.

I've had this problem so many times that I use my own factory method to create commands which automatically sets this property to true for me.

Classic useless Oracle documentation here

Answer 6

Here's the type of structure I usually use (sorry, this is from memory) :

int rows = 0;
using ( OracleConnection conn = new OracleConnection(connectionString) ) {
  using ( OracleCommand cmd = conn.CreateCommand() ) {
    cmd.CommandText = "UPDATE table SET column1 = ':p1 WHERE column2 = :p2";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue(":p1", p1Val);
    cmd.Parameters.AddWithValue(":p2", p2Val);
    rows = cmd.ExecuteNonQuery();
  }
}

The key difference is the use of the AddWithValue - I don`t remember why I ended up using that, but do remember having problems with some of the other ways of doing it.