Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Command Time Out Issue in case of Batch Query

User_P5SEXSep 2 2021

Hi,
I am use Ref Cursors to read multiple data in batch query. Its work fine but problem is Command Time out not works in case of batch reading. Without Cursor, Command Time out works. My Sample Code is
// Oracle.ManagedDataAccess.Client
cmd = new OracleCommand(@"begin
open : 1 for select * from Table1;
open : 2 for select * from Table2;

    end;", con);  

    cmd.Parameters.Add(new OracleParameter("1", OracleDbType.RefCursor, ParameterDirection.InputOutput));  
    cmd.Parameters.Add(new OracleParameter("2", OracleDbType.RefCursor, ParameterDirection.InputOutput));  
    cmd.CommandType = System.Data.CommandType.Text;  
    cmd.CommandTimeout = 5;  

    //var a = cmd.ExecuteNonQuery();  
    reader=cmd.ExecuteReader();  
    while (reader.Read())  
    {  
      Console.WriteLine(reader.GetInt32(0)); //Just example  
    }  

    reader.NextResult();  
    while (reader.Read())  
    {  
      Console.WriteLine(reader.GetInt32(0)); //Just example  
    }

Comments

Alex Keh-Oracle

Command cancellation does work with anonymous PL/SQL, but the cancellation behavior is not the same as a typical SQL statement. It's determined by how PL/SQL treats the cancellation call.
I believe PL/SQL will check every so often if a cancellation has been received, then stop execution. Also, the behavior differs of the PL/SQL (i.e. DB) is running on Linux vs. Windows.
You may want to ask this question on the PL/SQL forum as ODP.NET relies on PL/SQL to perform the cancellation on its behalf.

User_P5SEX

Thanks @Alex. Will post my question in PL/SQL forum.

1 - 2

Post Details

Added on Sep 2 2021
2 comments
108 views