Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It would appear that you are not the first to ask: <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2125208" rel="nofollow">https://forums.oracle.com/forums/thread.jspa?threadID=2125208</a></p> <p>You could monitor within the loop over reader.Read() the elapsed time and exit the loop. That's nice and simple but of course it's only going to be able to exit after a potentially long running call to Read finishes.</p> <p>Your best bet would probably be to do the loop within a Task on a separate thread, monitor it, and then call cmd.Cancel on the original thread:</p> <pre><code>[Test] public void TimeBasicSql() { using (OracleConnection con = new OracleConnection("User ID=id; Password=pass; Data Source=db;")) using (OracleCommand cmd = new OracleCommand()) { con.Open(); cmd.Connection = con; Console.WriteLine("Executing Query..."); try { cmd.CommandTimeout = 1; String sql = "begin open :o_cur1 for select count(*) from all_objects, all_objects; end;"; cmd.CommandText = sql; cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output }); var task = System.Threading.Tasks.Task.Factory.StartNew(() =&gt; { try { Stopwatch watch1 = Stopwatch.StartNew(); OracleDataReader reader = cmd.ExecuteReader(); watch1.Stop(); Console.WriteLine("Query complete. Execution time: {0} ms", watch1.ElapsedMilliseconds); int counter = 0; Stopwatch watch2 = Stopwatch.StartNew(); if (reader.Read()) counter++; watch2.Stop(); Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds); Stopwatch watch3 = Stopwatch.StartNew(); while (reader.Read()) { counter++; } watch3.Stop(); Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds); Console.WriteLine("Records read: {0}", counter); } catch (OracleException ex) { Console.WriteLine("Exception was thrown: {0}", ex); } }); if (!task.Wait(cmd.CommandTimeout * 1000)) { Console.WriteLine("Timeout exceeded. Cancelling..."); cmd.Cancel(); } } catch (OracleException ex) { Console.WriteLine("Exception was thrown: {0}", ex); } } </code></pre> <p>It's worth noting that the ORA-01013 exception is thrown on the worker thread and not on the thread calling OracleCommand.Cancel.</p>
    singulars
    1. This table or related slice is empty.
    1. POTimeout for OracleDataReader.Read Method
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USb_levitt
    1. USb_levitt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThanks for the feedback. I had thought about the multi-threaded approach; that approach will allow for the .NET side of the call to be terminated, but I *think* the query will continue to execute within the Oracle RDBMS. I'm looking to be able to cancel the query execution as well.
      singulars
      1. PO
      1. USdnickels
    2. COIt may for that last reader.Read call, but is this different that when you cancel a query in Sql Developer and have to wait a few seconds for the cancellation to complete?
      singulars
      1. PO
      1. USb_levitt
    3. COI need to clarify a couple of points from my last comment. When I thought about using a multi-threaded approach, it was using the Thread API and not TPL. I assumed that aborting a background thread that was executing a query would not cancel the query in the RDBMS. However, after doing some quick-and-dirty tests, that doesn't appear to be true. The RDBMS does terminate the session and thus cancel the query. Regarding using TPL Tasks and cancellation, the problem is that I need to be able to terminate the Read call itself (and not let it finish). The query could take 30+ min or more.
      singulars
      1. PO
      1. USdnickels
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload