title: How to efficiently handle slow CLOB reading from Oracle in .Net?
body: <p>At my company we have a WPF application, it can connect to different databases (MS SQL, MySQL, SQLite and Oracle). We have numerous tables (sometimes 200+, dont ask why, its very complicated) and our dataaccess layer has several interfaces and virtual/overridden methods to handle data reading in specific database. It is important, as we dont work specifically on one table as most tutorials shows, instead we have dynamically created database-specific commands we need to run. Under MS SQL, MySQL and SQLite everything works well, but in Oracle CLOB reading is very slow. To read 2000 rows of data it takes 40-50 seconds. Unfortunately in most of the cases we cannot assume that we dont need CLOB type as we are storing financial data in xml format and sometimes it exceeds of 4000, 8000 and even 10k+ char-long size. Im using ODP.Net solution to read data, this is what im doing:</p>
  
  <p>Our environment:
  Oracle: 11.2.0.1.0
  VS: 2010, Professional edition
  Oracle.DataAccess.dll is referenced from my Oracle home install:
  C:\Oracle\ODP.NET\bin\4\Oracle.DataAccess.dll</p>
  
  <p>And my testcode (from our solution):</p>
  
  <p>Property:</p>
  
  <pre><code>private string ConnectionString
  {
     get
     {
        return
           string.Format(
           "User Id={0}; Password={1}; POOLING=true;
           Data Source= (DESCRIPTION=(ADDRESS=
           (PROTOCOL=TCP)(HOST={2})(PORT={3})) (CONNECT_DATA=(SID={4})
           (SERVICE_NAME={5})));",
           "ourUser", "ourPassword", "ourHost", "ourPort", "ourSID",
           "ourDatabaseName");
          }
      }
  </code></pre>
  
  <p>The command to run:</p>
  
  <pre><code>string sql = "SELECT * FROM OurTable";
  //This table contains at least one CLOB column
  </code></pre>
  
  <p>And our test code:</p>
  
  <pre><code>List&lt;object[]&gt; readerList = new List&lt;object[]&gt;();
  
  using (Oracle.DataAccess.Client.OracleConnection oraConn = new
      Oracle.DataAccess.Client.OracleConnection(ConnectionString))
  {
     oraConn.Open();
     Oracle.DataAccess.Client.OracleCommand oraComm = new
        Oracle.DataAccess.Client.OracleCommand(sql);
     oraComm.CommandType = CommandType.Text;
     oraComm.Connection = oraConn;
     Oracle.DataAccess.Client.OracleDataReader oraReader;
     oraReader = oraComm.ExecuteReader();
     oraComm.InitialLOBFetchSize = -1;
  
     while (oraReader.Read())
     {
        object[] readObjects = new object[oraReader.FieldCount];
        oraReader.GetValues(readObjects);
        readerList.Add(readObjects);
     }
  
    oraConn.Close();
  }
  </code></pre>
  
  <p>The while iteration runs very slow, unless we dont need to read CLOB as in this it would be fast.
  Unfortunately i cannot make table specific solutions as not in every case i know on which tables do i have to work on (sometimes there are dynamically created tables).</p>
  
  <p>So, the question:
  Is there any solution to make it as fast as reading TEXT type objects from MS SQL?</p>