title: Oracle, Execute a string as query and return the result set
body: <p>I am trying to create a string query in oracle and then execute it. I know I can do that using execute immediate, however I do not get a result set back. As far as I understand I could use a cursor to retrieve the rows one by one but still this doesn't seem to do the job (I suppose cursors are just references not result sets, or probably my knowledge is quite limited but anyway). To make it more clear, I want to execute this string query through BIRT Reporting Tool, which always gives me an error message "SQL statement does not return a ResultSet object".</p>
  
  <p>The concept is as follows:</p>
  
  <p><strong>1.</strong> I have a <strong>tableA</strong> with some names.For example Nike, Adidas...I do not know which those tables are.(has to be dynamic)</p>
  
  <p><strong>2.</strong> For each table there are other tables such as Nike.<strong>stores</strong>, Adidas.<strong>stores</strong>.
  The bad thing is that the high level table has no connection with the low level ones. For example Nike table has no relation with Nike.<strong>stores</strong> table (Unfortunately this is the way the database was designed..).</p>
  
  <p><strong>3.</strong> So in MS SQL I create a string like:</p>
  
  <pre><code>SELECT @SqlStr = @SqlStr + N'SELECT * FROM ' + names + N'.stores 
  UNION ALL '  FROM **tableA** Group by names
  </code></pre>
  
  <p>which would actually be executed like:</p>
  
  <pre><code>Select * from Nike.stores UNION ALL Select * from Adidas.stores ... 
  </code></pre>
  
  <p>This works fine since it returns a result set.</p>
  
  <p>My question is how is it possible to do it in oracle??By the way I am trying not to use procedures or functions as it is one of the initial requirements. However, I suppose this could be the only way.</p>