title: Getting results in a result set from dynamic SQL in Oracle
body: <p><em>This question is similar to a couple others I have found on StackOverflow, but the differences are signficant enough to me to warrant a new question, so here it is:</em></p>
  
  <p>I want to obtain a result set from dynamic SQL in Oracle and then display it as a result set in a SqlDeveloper-like tool, just as if I had executed the dynamic SQL statement directly. This is straightforward in SQL Server, so to be concrete, here is an example from SQL Server that returns a result set in SQL Server Management Studio or Query Explorer:</p>
  
  <pre><code>EXEC sp_executesql N'select * from countries'
  </code></pre>
  
  <p>Or more properly:</p>
  
  <pre><code>DECLARE @stmt nvarchar(100)
  SET @stmt = N'select * from countries'
  EXEC sp_executesql @stmt
  </code></pre>
  
  <p>The question <a href="https://stackoverflow.com/questions/2153053/how-to-return-a-resultset-cursor-from-a-oracle-pl-sql-anonymous-block-that-exec">"How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?"</a> addresses the first half of the problem--executing dynamic SQL into a cursor. The question <a href="https://stackoverflow.com/questions/304600/how-to-make-oracle-procedure-return-result-sets">"How to make Oracle procedure return result sets"</a> provides a similar answer. Web search has revealed many variations of the same theme, all addressing just the first half of my question. I found <a href="https://stackoverflow.com/questions/1078506/oracle-sql-developer-how-to-view-results-from-a-ref-cursor">this post</a> explaining how to do it in SqlDeveloper, but that uses a bit of functionality of SqlDeveloper. I am actually using a custom query tool so I need the solution to be self-contained in the SQL code. This custom query tool similarly does not have the capability to show output of print  (dbms_output.put_line) statements; it only displays result sets. Here is yet <a href="http://www.club-oracle.com/forums/execute-immediate-with-bulk-collect-t1428/" rel="nofollow noreferrer">one more possible avenue</a> using 'execute immediate...bulk collect', but this example again renders the results with a loop of dbms_output.put_line statements. <a href="http://dbaforums.org/oracle/index.php?act=ST&amp;f=2&amp;t=9536&amp;st=0" rel="nofollow noreferrer">This link</a> attempts to address the topic but the question never quite got answered there either.</p>
  
  <p>Assuming this is possible, I will add one more condition: I would like to do this without having to define a function or procedure (due to limited DB permissions). That is, I would like to execute a self-contained PL/SQL block containing dynamic SQL and return a result set in SqlDeveloper or a similar tool.</p>
  
  <hr>
  
  <p>So to summarize:</p>
  
  <ul>
  <li>I want to execute an arbitrary SQL statement (hence dynamic SQL).</li>
  <li>The platform is Oracle.</li>
  <li>The solution must be a PL/SQL block with no procedures or functions.</li>
  <li>The output must be generated as a canonical result set; no print statements.</li>
  <li>The output must render as a result set in SqlDeveloper without using any SqlDeveloper special functionality.</li>
  </ul>
  
  <p>Any suggestions?</p>