This discussion is archived
2 Replies Latest reply: Sep 19, 2012 8:29 AM by Mark Williams-Oracle RSS

How to do a bulk update

920267 Newbie
Currently Being Moderated
I am trying to do a bulk update and as a test, I am using a table with two columns each of type varchar2(100).

When this chuck of code gets executed, no exceptions are raised, but my row values are not set as I expected.

string sql = "update TEST set NAME=:newName where NAME=:name";

connection.Open();
OracleCommand command = connection.CreateCommand();
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.BindByName = true;

command.ArrayBindCount = 5;

string[] originalName = { "Test1", "Test2", "Test3", "Test4", "Test5" };
string[] newName = { "New Test1", "New Test2", "New Test3", "New Test4", "New Test5" };

command.Parameters.Add(":newName", OracleDbType.Varchar2, originalName, System.Data.ParameterDirection.Input);
command.Parameters.Add(":name", OracleDbType.Varchar2, newName, System.Data.ParameterDirection.Input);

command.ExecuteNonQuery();

-----

I also added the following to see if it would help and it did not:

command.Parameters[0].ArrayBindSize = new int[5];
for (int i = 0; i < 5; i++)
{
command.Parameters[0].ArrayBindSize[i] = 100;
}

command.Parameters[1].ArrayBindSize = new int[5];
for (int i = 0; i < 5; i++)
{
command.Parameters[1].ArrayBindSize[i] = 100;
}


Can someone point out what I am doing wrong?
  • 1. Re: How to do a bulk update
    Tridus Journeyer
    Currently Being Moderated
    Don't know, that looks fine to me.

    Out of curiousity, have you tried using a stored procedure instead? I've got code doing that and I know it works.
  • 2. Re: How to do a bulk update
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    I'm not sure if this is intentional or not or perhaps just a result of testing, etc...

    You are using the following OracleParameter constructor:
    public OracleParameter(string parameterName, OracleDbType type, object obj, ParameterDirection direction);
    In this case obj is the value for this OracleParameter.

    However, in your code I see the following:
    command.Parameters.Add(":newName", OracleDbType.Varchar2, originalName, System.Data.ParameterDirection.Input);
    command.Parameters.Add(":name", OracleDbType.Varchar2, newName, System.Data.ParameterDirection.Input);
    So, for the "newName" parameter you are using the "originalName" array as the source and for the "name" parameter
    you are using the "newName" array as the source.

    This will result in SQL such as the following:
    update TEST set NAME=<originalName value> where NAME=<newName value>
    I think you want the following:
    update TEST set NAME=<newName value> where NAME=<originalName value>
    Though, perhaps you have it this way to "undo" a previous execution of the update statement?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points