StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's the solution that I ultimately went with. It's just an extension method for the OracleDataReader class. This method has a timeout value and a callback function as parameters. The callback function would typically (if not always) be OracleCommand.Cancel.</p> <pre><code>namespace ConsoleApplication1 { public static class OracleDataReaderExtensions { public static bool Read(this OracleDataReader reader, int timeout, Action cancellationAction) { Task<bool> task = Task<bool>.Factory.StartNew(() => { try { return reader.Read(); } catch (OracleException ex) { // When cancellationAction is called below, it will trigger // an ORA-01013 error in the Read call that is still executing. // This exception can be ignored as we're handling the situation // by throwing a TimeoutException. if (ex.Number == 1013) { return false; } else { throw; } } }); try { if (!task.Wait(timeout)) { // call the cancellation callback function (i.e. OracleCommand.Cancel()) cancellationAction(); // throw an exception to notify calling code that a timeout has occurred throw new TimeoutException("The OracleDataReader.Read operation has timed-out."); } return task.Result; } catch (AggregateException ae) { throw ae.Flatten(); } } } } </code></pre> <p>Here's an example of how it can be used.</p> <pre><code>namespace ConsoleApplication1 { class Program { static string constring = "User ID=xxxx; Password=xxxx; Data Source=xxxx;"; static void Main(string[] args) { using (OracleConnection con = new OracleConnection(constring)) using (OracleCommand cmd = new OracleCommand()) { cmd.Connection = con; con.Open(); Console.WriteLine("Executing Query..."); string sql = "<some long running sql>"; cmd.CommandText = "PROC_A"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new OracleParameter("i_sql", OracleDbType.Varchar2) { Direction = ParameterDirection.Input, Value = sql }); cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output }); try { // execute command and get reader for ref cursor OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // read first record; this is where the ref cursor SQL gets evaluated Console.WriteLine("Reading first record..."); if (reader.Read(3000, cmd.Cancel)) { } // read remaining records Console.WriteLine("Reading records 2 to N..."); while (reader.Read(3000, cmd.Cancel)) { } } catch (TimeoutException ex) { Console.WriteLine("Exception: {0}", ex.Message); } Console.WriteLine("Press any key to continue..."); Console.Read(); } } } } </code></pre> <p>And here's an example of the output.</p> <pre><code>Executing Query... Reading first record... Exception: The OracleDataReader.Read operation has timed-out. Press any key to continue... </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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