title: Oracle WITH clause returns no data
body: <p>I am trying to use a WITH clause in Oracle, but it is not returning any data.</p>
  
  <p>This is the query I am trying to run...</p>
  
  <pre><code>with test as 
   (select count(*)  
   from my_table)
  select *
  from test;
  </code></pre>
  
  <p>When I run this code, I get back the count of the records in my_table</p>
  
  <pre><code>select count(*)  
   from my_table
  </code></pre>
  
  <p>I am on Oracle 10g so the query should work...</p>
  
  <pre><code>select * from v$version;
  </code></pre>
  
  <p>yields</p>
  
  <blockquote>
    <p>Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi<br>
    PL/SQL Release 10.2.0.4.0 - Production<br>
    CORE    10.2.0.4.0  Production<br>
    TNS for Solaris: Version 10.2.0.4.0 - Production<br>
    NLSRTL Version 10.2.0.4.0 - Production  </p>
  </blockquote>
  
  <p>Could it a permissions issue or something?</p>
  
  <p>*<em>EDIT: *</em></p>
  
  <p>I believe my question is clear. Using the WITH statement will not return any records for me, even though the "select count(*) from my_table" statement inside the WITH statement works correctly, which would lead me to believe that there is another issue that I am unable to figure out, hence this question :)</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>OK, so if I try and execute the query from a linked server from SQL server management studio I get some error information back:</p>
  
  <p>sg 7357, Level 16, State 2, Line 1
  Cannot process the object "with test as
  (select count(*)
  from v$version)
  select * from test;". The OLE DB provider "MSDAORA" for linked server "MyServer" indicates that either the object has no columns or the current user does not have permissions on that object.</p>