Getting results in a result set from dynamic SQL in Oracle

Posted by msorens on Stack Overflow See other posts from Stack Overflow or by msorens
Published on 2010-04-05T17:45:25Z Indexed on 2010/04/05 18:03 UTC
Read the original article Hit count: 446

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:

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:

EXEC sp_executesql N'select * from countries'

Or more properly:

DECLARE @stmt nvarchar(100)
SET @stmt = N'select * from countries'
EXEC sp_executesql @stmt

The question "How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?" addresses the first half of the problem--executing dynamic SQL into a cursor. The question "How to make Oracle procedure return result sets" 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 this post 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 one more possible avenue using 'execute immediate...bulk collect', but this example again renders the results with a loop of dbms_output.put_line statements. This link attempts to address the topic but the question never quite got answered there either.

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.


So to summarize:

  • I want to execute an arbitrary SQL statement (hence dynamic SQL).
  • The platform is Oracle.
  • The solution must be a PL/SQL block with no procedures or functions.
  • The output must be generated as a canonical result set; no print statements.
  • The output must render as a result set in SqlDeveloper without using any SqlDeveloper special functionality.

Any suggestions?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about dynamic-sql