PRB - System.Data.OracleClient is using cached data on fetching data.

Question

PRB - System.Data.OracleClient is using cached data on fetching data.

Please help,

I have encountered a PRB with the System.Data.OracleClient to Oracle 11 and 9i.

If I issue many inserts/update/deletes to a table in a seperate application and seperate machine, and then try to use a simple fetch operation with System.Data.OracleClient, the OracleDataAdapter.Fill command will not fetch all of the data if executed FAST-ENGOUGH. If executed FAST-ENOUGH, it may literally see no data. And if given time, and re-iterated, one can literally watch as the results sets in multiple fetches gets more and more of the results. But, if one goes to TOAD (9.7.2.5) it will get all of the expected rows.

It is as-if, something is causing the System.Data.OracleClient to use cached data.

Here is code to reproduce the issue:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
 
namespace MyNamespace
{
  public class MyClass
  {
    MyClass()
    {
      // Step 1.. Go to TOAD and run MANY inserts/updates/deletes to tables.
 

      // Step 2.. AS-FAST-AS-POSSIBLE, run this code to where “_cachedData” is set and notice not all records (if any)
      // returned until ENOUGH-TIME has passsed.
 

      String _sql = @”select * from TABLE";
      DataTable _cachedData = GetDataTableForSQL(_sql);
    }

    public DataSet GetDataSetFor(string query)
    {
      DataSet retVal = new DataSet();

      using (OracleConnection connection = new OracleConnection("Data Source=MY_ORACLE_DB;User ID=MyUID;Password=MyPWD;Unicode=True"))
      {
        OracleCommand cmd = new OracleCommand(query, connection);

        OracleDataAdapter adapter = new OracleDataAdapter();           
        adapter.SelectCommand = cmd;

        try
        {
          connection.Open();
          cmd.Prepare();
          adapter.Fill(retVal);
        }
        catch (Exception e)
        {
          throw e;
        }
        finally
        {
          connection.Close();
        }
      }

      return retVal;
    }
  }
}