title: Why can't I do a "with x as (...)" with ADODB and Oracle?
body: <p>I fail to execute an SQL query with a <em>with clause</em> via ADODB and Oracle.</p>
  
  <p>That is, the following snippet works:</p>
  
  <pre><code>Dim cn As ADODB.connection
  Set cn = ....
  
  Dim rs As ADODB.recordSet
  Set rs = New ADODB.Recordset
  
  rs.Open "select 'foo' x from dual", cn
  
  
  Do While Not rs.eof
     ...
     rs.MoveNext
  Loop
  </code></pre>
  
  <p>However, the following doesn't work - it genererats a <strong>Run-Time error 3704: Operation is not allowed when the object is closed.</strong></p>
  
  <pre><code>Dim cn As ADODB.connection
  Set cn = ....
  
  Dim rs As ADODB.recordSet
  Set rs = New ADODB.Recordset
  
  rs.Open "with w as (select 'foo' x from dual) select x from w", cn
  
  Do While Not rs.eof
     ...
     rs.MoveNext
  Loop
  </code></pre>
  
  <p>Obviously, this is a trimmed-down demonstration of the real problem which consists
  of a more sophisticated query.</p>
  
  <p>It seems to me that ADODB sort of parses the query before it passes it to the Oracle instance and does not understand the <em>with clause</em>. Anyway, any help on this is highly
  appreciated.</p>