OracleCommand command, ExecuteNonQuery issue

Question

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)
                {
                    string 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!

any final solution with full source code sample working about it ? — Kiquenet, Oct 28 '13 at 08:29

score 10 · Accepted Answer · answered Mar 21 '11 at 16:41

10

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

answered Mar 21 '11 at 16:41

Mike Atlas

8,193
4
46
62

score 3 · Answer 2 · answered Mar 21 '11 at 16:40

3

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.

answered Mar 21 '11 at 16:40

Daniel Hilgarth

171,043
40
335
443

You can't be sure the OP actually needs undo capability that isn't available when `trunc` is used to clear the table. – Mike Atlas Mar 21 '11 at 16:42
@Mike: How is it possible to undo a `delete` after a `commit`? – Daniel Hilgarth Mar 21 '11 at 16:43
It's possible to go through recovery scenarios through the UNDO functionality. http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/storage003.htm – Mike Atlas Mar 21 '11 at 16:48
@Mike: Thanks for the link. Unfortunately, I think I fail to understand how this relates to undoing committed deletes. – Daniel Hilgarth Mar 21 '11 at 16:51
In a production environment, it is highly likely that the data being deleted needs to be available for manual recovery (flasback in the doc) up to a certain point. Truncating the tables eliminates this possibility. You didn't provide any reason why you suggested TRUNCATE over DELETE. – Mike Atlas Mar 21 '11 at 16:54
@Mike: Thanks for the explanation. I don't share your POV that it is "highly likely" that flashback is activated in a production environment. Several big companies I have worked for, haven't had it activated. The reason for my suggestion: Basically, because it is faster. – Daniel Hilgarth Mar 21 '11 at 16:59
I guess they weren't in highly regulated industries? Anyway, this whole thread is now off topic... – Mike Atlas Mar 21 '11 at 17:03

score 3 · Answer 3 · answered Mar 21 '11 at 16:46

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.

Tim · Answer 4 · 2011-03-21T17:50:40.670

2

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.

edited Mar 21 '11 at 17:50

answered Mar 21 '11 at 16:52

Tim

5,371
3
32
41

OracleCommand command, ExecuteNonQuery issue

4 Answers4

Linked