title: Reading CLOB column is slow
body: <p>Hopefully someone can shed a little light on an issue that I'm currently having with an Oracle DB - I'm sure it's something simple!!</p>
  
  <p>I've managed to recreate this in a sample, so here is the DB structure:</p>
  
  <pre><code>CREATE TABLE MyTable(
      ixMyTable NUMBER,
      clobData CLOB
  )
  /
  
  CREATE OR REPLACE PACKAGE PKGTEST
  AS
      PROCEDURE DoSomething(
          cur_OUT OUT SYS_REFCURSOR
          );
  END PKGTEST;
  /
  
  CREATE OR REPLACE PACKAGE BODY PKGTEST
  AS
  
  PROCEDURE DoSomething(
      cur_OUT OUT SYS_REFCURSOR
  )
  AS
  BEGIN
      OPEN cur_OUT FOR 
          SELECT ixMyTable, clobData
          FROM MyTable;
  END;
  
  END PKGTEST;
  /
  
  GRANT EXECUTE ON PKGTEST TO TEST_ROLE
  /
  
  BEGIN
      FOR i IN 1 .. 7000 LOOP
          insert into mytable values (i, TO_CLOB('123456'));
      END LOOP;
  END;
  /
  </code></pre>
  
  <p>Extra info:</p>
  
  <p>Schema owner is TEST_SCHEMA</p>
  
  <p>User is CARL</p>
  
  <p>CARL has the TEST_ROLE role</p>
  
  <p>Given the database setup as above, I have a C# test app that uses the standard System.Data.OracleClient.OracleCommand etc. in order to execute PKGTEST.DoSomething and throw the results into a datagrid (DevExpress).</p>
  
  <p>Pretty sure that the grid is irrelevant here, as we experience the same issue through c++ using the open source OTL (not my department, fortunately).</p>
  
  <p>OK, to the problem....</p>
  
  <p>Time from beginning until the grid is populated is ~35-40s, ouch.</p>
  
  <p>However, if I do the following:</p>
  
  <pre><code>GRANT SELECT ON MyTable TO TEST_ROLE
  /
  </code></pre>
  
  <p>and then perform the query again, it takes ~5-6s.</p>
  
  <p>It seems to me that this has something to do with privileges etc., but I'm not quite sure why it still actually works both ways??</p>
  
  <p>Just to throw something else into the pot, if I change the procedure to </p>
  
  <pre><code>SELECT ixMyTable, TO_CLOB(TO_NCLOB(clobData))
  FROM MyTable;
  </code></pre>
  
  <p>Then the time is ~5-6s, with or without the extra SELECT privilege.</p>
  
  <p>Any pointers or straight out solutions would be much appreciated!</p>
  
  <p>Edit:</p>
  
  <p>OS is Vista x86 Business</p>
  
  <p>Oracle Server is 10.2.0.1</p>
  
  <p>Oracle Client is 10.2.0.3</p>
  
  <p>Edit:</p>
  
  <p>As suggested, I've tried changing from the MS OracleClient to the ODP.NET and this does speed up as required. </p>
  
  <p>Unfortunately, the C# app that was being affected was just an internal app that is used for viewing tables / running SPROCS etc. </p>
  
  <p>Our main deliverable is the C++ app that uses OTL (<a href="http://otl.sourceforge.net/otl3_intro.htm" rel="nofollow noreferrer">http://otl.sourceforge.net/otl3_intro.htm</a>) for database access. This is not really something that can be changed at this time, and so I would really like to understand the reasons for the difference, without having to throw gratuitous GRANT SELECTs around willy-nilly.</p>
  
  <p>If lack of SELECT privilege caused a complete failure then I could probably live with this, but lack of SELECT appears to be causing some slower route for accessing the CLOB data.</p>
  
  <p>I've marked up the 3 answers - thanks for those - but I could really do with a reason, so I'll add a bounty to this.</p>
  
  <p>P.S. We really wanted to go with OCCI at the outset for our C++, but as Oracle are always supporting a version of the IDE before current we couldn't get it to play nicely with our Visual Studio 2008.</p>