4

I am trying to run a non query using a Oracle connection in ASP C# with CLR 4.5. Here is my code:

    string connectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString1"].ConnectionString;
    OracleConnection conn = new OracleConnection(connectionString);
    conn.Open();
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "update SALES_ADVENTUREWORKS2012.SALESORDERDETAIL set UNITPRICEDISCOUNT=0 where ROWGUID='4A399178-C0A0-447E-9973-6AB903B4AECD'";
    cmd.CommandType = CommandType.Text;
    cmd.CommandTimeout = QUERY_TIMEOUT;
    int row_affected = cmd.ExecuteNonQuery();
    HttpContext.Current.Response.Write("Rows affected:" + row_affected + "<br/>");
    conn.Close();

when I run the query in oracle development tool, it works fine. when I use the asp code above, it freezes when performing the query. It freezes forever even though I used a 5 second timeout. I've tried using the managed and unmanaged oracle libraries; both behave the same. Note that using the fill or scalar query work perfectly fine so there is nothing wrong with my connection string. Also the fact that oracle development can perform this update query proves that this is not a permission problem.

Any ideas?

max
  • 9,708
  • 15
  • 89
  • 144

2 Answers2

13

Most likely your query is waiting to get access to the record. You probably have modified that row in "oracle development tool" and have not committed or rolled back that transaction.

Just commit/rollback in your tool or close open session. You can check for open transactions in v$transaction view.

More on automatic locks in Oracle: http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm

vav
  • 4,584
  • 2
  • 19
  • 39
  • 1
    You're right. The oracle development tool keeps the connection open and the timeout does not work leading to this problem. Thanks – max May 17 '14 at 06:42
  • 2
    I had uncommited transactions in SQLDeveloper - commiting them worked for me. Thanks for the tip. – ben Oct 21 '15 at 19:21
0

Are you certain you are using the 4.5 library? The 3.5 documentation states that the CommandTimeout property has no effect.

The 4.5 documentation suggests it should work, but the Remarks section doesn't mention the change, which warrants suspicion.

Otherwise, the code you posted doesn't seem to show where you actually set the value of QUERY_TIMEOUT to 5 seconds. If QUERY_TIMEOUT has a value of zero, then any other provider (SQLCommand, for example) would wait indefinitely. As vav suggested, locks from other sources could cause an indefinite wait.

Mat
  • 470
  • 7
  • 12
  • yes, I have set the timeout to 5. The documentation says it is in seconds and the default which did not work was 15 seconds. Oracle bug! – max May 17 '14 at 06:43