OracleCommand team, problem with ExecuteNonQuery

Question

I need to clear certain tables in oracle database, however, when I have problems 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 call this method with this list

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

It starts the first two tables perfectly, however on the third it gets stuck, and the application starts forever ...

It's funny when I switch "GROUP_REPORT_EMAIL_LIST" and "GROUP_EQUIPMENT_GROUP_STN_XREF", the application starts forever after it gets into the middle name of the table.

So in conclusion, the function runs forever when it falls into "GROUP_EQUIPMENT_GROUP_STN_XREF". I checked that the generated SQL works by checking it on the toad.

Has anyone else encountered this problem?

EDIT - The first two tables are really cleared when they are started.

Decision

 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) { string sql = String.Format("DELETE FROM TOA_REPORTING.{0}", table); command.CommandText = sql; command.ExecuteNonQuery(); } trans.Commit(); } 

TRUNCATE would be a very pleasant solution, however I do not have privileges for this!

Answer 1

Have you forgotten to make changes to Toad (or any other client)? An open transaction will make her wait forever.

Answer 2

Does this table have a lot of data? This explains why it takes so long to delete data.
Anyway, I suggest using TRUNC to clear tables.

Answer 3

A large number of deletions can be very slow, especially if you run them in a single transaction. If you don’t need a transaction at all, use:

 truncate table YourTable 

If you do, split delete into small transactions. Basically performed:

 delete from YourTable where rownum < 100 

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

Answer 4

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

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