Is it possible to output a SELECT state 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 do a SELECT like:

SELECT foo, bar FROM foobar;

Hint :

BEGIN
SELECT foo, bar FROM foobar;
END;

doesn't work.

Answer 1

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;

Answer 2

Create a function in a package and return a SYS_REFCURSOR:

FUNCTION Function1 return SYS_REFCURSOR IS 
       l_cursor SYS_REFCURSOR;
       BEGIN
          open l_cursor for SELECT foo,barFROM FOOBAR; 
          return l_cursor; 
END Function1;

Answer 3

From an anonymous block? I'd like to now more about the situation where you think that to be required, because with subquery factoring clauses and inline views it's pretty rare that you need to resort to PL/SQL for anything other than the most complex situations.

If you can use a named procedure then use pipelined functions. Here's an example pulled from the documentation:

CREATE PACKAGE pkg1 AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));