title: How to retrieve Oracle Clob more than 4000 characters ODP.NET
body: <p>I am trying to retrieve CLOB data via ODP.NET. 
  My problem is that reader.GetValue(i).ToString() can return only up to 4000 characters and the rest of the data is truncated. 
  If I use reader.GetOracleClob(1).Value I got "Specified cast is not valid" error.</p>
  
  <p>Any idea what I have missed? I am using ODP.NET 4.0</p>
  
  <p>Below is my code. </p>
  
  <pre><code>using (OracleConnection oConn = new OracleConnection())
  {
    oConn.ConnectionString = pConnstr;
    oConn.Open();
    using (OracleCommand oCmd = new OracleCommand("select varchar_column, clob_column from test", oConn))
    {
      oCmd.InitialLOBFetchSize = -1;
      string key, value, value1;
      var rd = oCmd.ExecuteReader();
      while (rd.Read())
      {
        if (rd.IsDBNull(1)) { value = ""; }
        else
        {
          key = rd.GetValue(0).ToString();
          value = rd.GetValue(1).ToString(); // unable to get more than 4000.
          value1 = rd.GetOracleClob(1).Value; // Specified cast is not valid.
        }
      }
    }
  }
  </code></pre>
  
  <p>I found in Oracle Doc that When the InitialLOBFetchSize property is set to a nonzero value, the GetOracleBlob, GetOracleClob, GetOracleBlobForUpdate, and GetOracleClobForUpdate typed accessor methods are disabled. That may help explain why I got "Specified cast is not valid" error.</p>
  
  <p>However, I still cannot get data beyond 4000 limit.</p>
  
  <p>I have tried different combinations documented here
  <a href="http://docs.oracle.com/cd/E11882_01/win.112/e18754/featData.htm#autoId6" rel="nofollow">http://docs.oracle.com/cd/E11882_01/win.112/e18754/featData.htm#autoId6</a>
  But no luck.</p>