title: .NET Oracle SP call errors ORA-00604 and ORA-01001 when passing CTE-based cursor to application
body: <p>The SP is as follows: <a href="http://pastebin.com/nknGhSM2" rel="nofollow">SP code</a></p>
  
  <p>C# code calling it: <a href="http://pastebin.com/TLV1rxCf" rel="nofollow">C# code</a> (this is the version after making changes described in update #3)</p>
  
  <p>Using System.Data.OracleClient, .NET 3.5 SP1, Oracle Express 11g, Windows 7 x64.</p>
  
  <p>This executes fine in SQL Developer(2,3). However, when calling from .NET, this happens: (see update 3)</p>
  
  <p><strong>Update 1:</strong> Using ExecuteReader with 2 OUT parameters <em>works</em>, when the cursor parameter is reading a global temp. table joined with a regular table. Also, this will eventually have to work inside an ORM (Lightspeed) which can only use ExecuteReader.</p>
  
  <p><strong>Update 2:</strong> Looks like same thing happens inside SQL Developer. I used this:</p>
  
  <pre><code>LOOP
    FETCH results INTO v_rec;
    EXIT WHEN results%notfound;
    DBMS_OUTPUT.PUT_LINE(v_rec.name);
  END LOOP;
  </code></pre>
  
  <p>... to fetch the contents of "results": works in the SP, and produces ORA-01001 when used in the PL/SQL block calling the SP (after removing the same code inside the SP, of course).</p>
  
  <p><strong>Update 3:</strong> Turning "results1" into an OUT parameter solved the problem in SQL Developer, which apparently was cursor being automatically closed when going out of scope. However, .NET now barfs:</p>
  
  <pre><code>ORA-00604: error occurred at recursive SQL level 1
  ORA-01059: parse expected before a bind or execute
  </code></pre>
  
  <p>See <a href="http://pastebin.com/TLV1rxCf" rel="nofollow">C# code</a> for parameters. Tried ExecuteReader, ExecuteNonQuery and ExecuteOracleNonQuery methods, to no avail.</p>