developers

    Forum Stats

  • 3,873,653 Users
  • 2,266,622 Discussions
  • 7,911,597 Comments

Discussions

Command Time Out Issue in case of Batch Query

User_P5SEX
User_P5SEX Member Posts: 4 Green Ribbon

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

        }

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,153 Employee

    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
    User_P5SEX Member Posts: 4 Green Ribbon

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

developers