OracleCommand command, ExecuteNonQuery issue

I have to clear certain tables in the oracle database however when I'm having issues with running the following code

public static void ClearDataTables(IList<string> tableNames)
        {
            string connectionString = "CONNECTIONSTRING";
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                foreach (string table in tableNames)
                {
                    OracleCommand command = connection.CreateCommand();
                    string sql = String.Format("DELETE FROM TOA_REPORTING.{0}", table);
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
        }

I am calling this method with this list

ClearDataTables(new List<string> { "GROUP_DEFINITION", "GROUP_REPORT_EMAIL_LIST", "GROUP_EQUIPMENT_GROUP_STN_XREF"});

It runs the first two tables fine, however on the third one, it gets stuck and the application runs forever...

Funny thing is, when I switch "GROUP_REPORT_EMAIL_LIST" and "GROUP_EQUIPMENT_GROUP_STN_XREF" The application runs forever after the it hits the second table name.

So in conclusion, the function runs forever when it hits "GROUP_EQUIPMENT_GROUP_STN_XREF". I've verified that the SQL generated works by testing it out on toad.

Anyone else ran into this issue?

EDIT - The first two tables does indeed get cleared when it runs.

Solution

string connectionString = "CONNECTIONSTRING";
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                OracleTransaction trans = connection.BeginTransaction();
                command.Transaction = trans;
                foreach (string table in tableNames)
                {
                    st开发者_如何学JAVAring sql = String.Format("DELETE FROM TOA_REPORTING.{0}", table);
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                trans.Commit();
            }

TRUNCATE would have been a very nice solution, however I do not have the privileges to do so!

Have you forgotten to commit your changes in Toad (or any other client)? An open transaction will cause it to wait indefinitely.

Is there a lot of data in that table? This would explain, why it takes so long to delete the data.
Anyway, I suggest to use TRUNC for clearing tables.

Large number of deletes can be very slow, especially if you run them in one transaction. If you don't need the transaction at all, use:

truncate table YourTable

If you do, split the delete over small-sized transactions. Basically run:

delete from YourTable where rownum < 100

until the table is empty. See for example this blog post.

I would probably write a stored procedure that does all of the deletions or truncations and invoke the SP once, rather than have a loop client-side.

EDIT: It would also be better not to create the command object inside the loop. Create it once outside the loop with a table-name parameter, and then invoke it feeding it a different parameter value with each iteration. But the SP is to be preferred.