oracle - Is it possible to output a SELECT statement from a PL/SQL block?

Question

How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT?

For example how to do a SELECT like:

SELECT foo, bar FROM foobar;

Hint :

BEGIN
SELECT foo, bar FROM foobar;
END;

doesn't work.

Answer 1

You can do this in Oracle 12.1 or above:

declare
    rc sys_refcursor;
begin
    open rc for select * from dual;
    dbms_sql.return_result(rc);
end;

I don't have DBVisualizer to test with, but that should probably be your starting point.

For more details, see Implicit Result Sets in the Oracle 12.1 New Features Guide, Oracle Base etc.

For earlier versions, depending on the tool you might be able to use ref cursor bind variables like this example from SQL*Plus:

set autoprint on

var rc refcursor

begin
    open :rc for select count(*) from dual;
end;
/

PL/SQL procedure successfully completed.


  COUNT(*)
----------
         1

1 row selected.