在ODP.NET的OracleCommand类具有可用于强制超时的命令执行一个CommandTimeout属性。这个属性似乎情况下，在CommandText是一个SQL语句的工作。这个例子code被用来说明该物业在行动。在的code中的初始版本，将CommandTimeout设置为零 - 告诉ODP.NET不执行超时

The ODP.NET OracleCommand class has a CommandTimeout property that can be used to enforce a timeout for the execution of a command. This property seems to work in situations where the CommandText is a SQL statement. The example code is used to illustrate this property in action. In the initial version of the code, the CommandTimeout is set to zero - telling ODP.NET not to enforce a timeout.

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;"))
            using (OracleCommand cmd = new OracleCommand())
            {
                con.Open();                
                cmd.Connection = con;

                Console.WriteLine("Executing Query...");

                try
                {
                    cmd.CommandTimeout = 0;

                    // Data set SQL:
                    cmd.CommandText = "<some long running SQL statement>";
                    cmd.CommandType = System.Data.CommandType.Text;

                    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.Message);
                }

                Console.WriteLine("Press any key to continue...");
                Console.Read();
            }
        }
    }
}

示例输出上面的code如下所示：

Example output for the above code is shown below:

Executing Query...
Query complete.  Execution time: 8372 ms
First record read: 3 ms
Records 2..n read: 1222 ms
Records read: 20564
Press any key to continue...

如果我将CommandTimeout更改为类似3 ...

If I change the CommandTimeout to something like 3...

cmd.CommandTimeout = 3;

...然后运行相同的code产生以下输出：

...then running the same code produces the following output:

Executing Query...
Exception was thrown: ORA-01013: user requested cancel of current operation
Press any key to continue...

调用存储过程返回一个REF CURSOR是另一回事，但。考虑下面的测试PROC（纯粹是为了测试目的）：

Calling a stored procedure that returns a ref cursor is another matter though. Consider the test proc below (purely for test purposes):

PROCEDURE PROC_A(i_sql VARCHAR2, o_cur1 OUT SYS_REFCURSOR)
is
begin

    open o_cur1
    for
    i_sql;

END PROC_A;

下面的例子code可以用来调用存储过程。需要注意的是它设置将CommandTimeout至3的值。

The example code below can be used to call the stored proc. Note that it sets the CommandTimeout to a value of 3.

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;"))
            using (OracleCommand cmd = new OracleCommand())
            {
                con.Open();                
                cmd.Connection = con;

                Console.WriteLine("Executing Query...");

                try
                {
                    cmd.CommandTimeout = 3;

                    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 });

                    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.Message);
                }

                Console.WriteLine("Press any key to continue...");
                Console.Read();
            }
        }
    }
}

从code上面的输出示例如下所示：

Example output from the code above is shown below:

Executing Query...
Query complete.  Execution time: 34 ms
First record read: 8521 ms
Records 2..n read: 1014 ms
Records read: 20564
Press any key to continue...

请注意，执行时间是非常快（34毫秒）和超时异常没有抛出。我们在这里看到的表现是因为不执行对于ref游标SQL语句，直到到OracleDataReader.Read方法的第一个电话。当第一个read（）调用是由从REFCURSOR读取第一个记录，然后表现在长时间运行的查询命中的发生。

Note that the execution time is very quick (34 ms) and that a timeout exception was not thrown. The performance that we see here is because the SQL statement for a ref cursor is not executed until the first call to the OracleDataReader.Read method. When the first Read() call is made to read the first record from the refcursor, then the performance hit from the long running query is incurred.

这是我出的行为意味着OracleCommand.CommandTimeout财产不能用于取消与裁判光标相关联的长时间运行的查询。我不知道在ODP.NET任何财产可以用来限制裁判SQL游标在这种情况下的执行时间。任何人有关于如何长时间运行的参考游标的SQL语句的执行会在一定时间后进行短路有什么建议？

The behavior that I've illustrated means that the OracleCommand.CommandTimeout property can't be used to cancel a long running query associated with a ref cursor. I'm not aware of any property in ODP.NET that can be used to limit the execution time of a ref cursor SQL in this situation. Anyone have any suggestions on how the execution of a long running ref cursor SQL statement could be short circuited after a certain amount of time?

推荐答案

下面是我最终去与解决方案。这只是对OracleDataReader类的扩展方法。这种方法有一个超时值和一个回调函数作为参数。回调函数通常（如果不总是）是OracleCommand.Cancel。

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.

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();
            }
        }
    }
}

下面是它如何被使用的例子。

Here's an example of how it can be used.

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();
            }
        }
    }
}

和这里的输出的一个例子。

And here's an example of the output.

Executing Query...
Reading first record...
Exception: The OracleDataReader.Read operation has timed-out.
Press any key to continue...