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. 

Answer 2

It depends on what you need the result for.

If you are sure that there's going to be only 1 row, use implicit cursor:

DECLARE    v_foo foobar.foo%TYPE;    v_bar foobar.bar%TYPE; BEGIN    SELECT foo,bar FROM foobar INTO v_foo, v_bar;    -- Print the foo and bar values    dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar); EXCEPTION    WHEN NO_DATA_FOUND THEN      -- No rows selected, insert your exception handler here    WHEN TOO_MANY_ROWS THEN      -- More than 1 row seleced, insert your exception handler here END; 

If you want to select more than 1 row, you can use either an explicit cursor:

DECLARE    CURSOR cur_foobar IS      SELECT foo, bar FROM foobar;     v_foo foobar.foo%TYPE;    v_bar foobar.bar%TYPE; BEGIN    -- Open the cursor and loop through the records    OPEN cur_foobar;    LOOP       FETCH cur_foobar INTO v_foo, v_bar;       EXIT WHEN cur_foobar%NOTFOUND;       -- Print the foo and bar values       dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar);    END LOOP;    CLOSE cur_foobar; END; 

or use another type of cursor:

BEGIN    -- Open the cursor and loop through the records    FOR v_rec IN (SELECT foo, bar FROM foobar) LOOP           -- Print the foo and bar values    dbms_output.put_line('foo=' || v_rec.foo || ', bar=' || v_rec.bar);    END LOOP; END;