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

  • 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 ( 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
          // 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;

            catch (Exception e)
              throw e;

          return retVal;



    • Moved by Alan_chen Monday, October 10, 2011 7:42 AM (From:ADO.NET DataSet)
    Friday, October 7, 2011 7:00 PM