Output the result of a SELECT executed using dynamic PL/SQL

Question

Using T-SQL (SQL Server) this couldn't be easier:

DECLARE @MyStatement AS NVARCHAR(max) = 'SELECT * FROM MYTABLE'
EXEC (@MyStatement)

However, using PL/SQL (Oracle) it's been a real pain in the ass. After a couple of hours I could finally reach something that was successfully completed without errors:

declare 
    MyStatement varchar(300) := 'SELECT * FROM MYTABLE';
begin
    execute immediate MyStatement;
end;

With this I can get a PL/SQL procedure successfully completed in the script output window. However, and unlike what happens in T-SQL, it doesn't give me any result on the 'Query Result' window.

I've tryed the solution proposed here: How to output result of SELECT statement which is executed using native dynamic SQL? , which I still cannot replicate. In any case, I believe this solution is trying to output the results in the 'Script output' window. That's not what I'm searching for. I want to output the results in the 'Query Result' window.

Answer 1

When you run the anonymous PL/SQL block in your question the block compiles and executes OK, but the dynamic query isn't actually being executed:

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.

If you were happy to just see the results in the script output window you could use the variable and print SQL Developer client commands and have your block open a ref cursor as a bind variable:

var rc refcursor

declare 
    MyStatement varchar(300) := 'SELECT * FROM MYTABLE';
begin
    open :rc for MyStatement;
end;
/

print rc

That bound cursor approach can be used from other clients, e.g. from a Java application using JDBC. And 12c adds a mechanism to simplify that a bit, but only from a stored procedure - not an anonymous block. You would still only see the results in the script output window though (assuming your version of SQL Developer supports this mechanism at all).

I don't think there's any way to get the result from a dynamic query run in an anonymous block into the SQL Developer query results windows, as SQL Developer isn't running the query - it's happening inside a PL/SQL context on the server.

You could potentially use a function instead, which returns a pipelined collection, and then query that function - but the collection type would have to be known in advance, which restricts how dynamic the query can really be as the column names and data types in the select list would have to match an object type.

It's hard to tell what to advise as your example is something that doesn't need to be dynamic. A real-world scenario might suggest other approaches. It still ins't quite the query result window you wanted, but if you did have a function like;

drop function myfunc;

create function myfunc return sys_refcursor
as
    MyStatement varchar(300) := 'SELECT * FROM MYTABLE';
    MyRefCursor sys_refcursor;
begin
    open MyRefCursor for MyStatement;
    return MyRefCursor;
end;
/

then you could open the function from the object browser (under the connection, in the pane on the left). From there you can click the green triange (or hit control-F10) to run the function. That gives you a window with a pre-populated anonymous block to call the function:

When you click OK the dynamic query results will end up in the 'output variables' section (using a query against the employees table in this case):

That output isn't as flexible as the query result window though you can't export it or sort it, for instance. But it is in a nice grid...