How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?

You can write a PL/SQL function to return that cursor (or you could put that function in a package if you have more code related to this):

CREATE OR REPLACE FUNCTION get_allitems
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT * FROM allitems;
  RETURN my_cursor;
END get_allitems;

This will return the cursor.

Make sure not to put your SELECT-String into quotes in PL/SQL when possible. Putting it in strings means that it can not be checked at compile time, and that it has to be parsed whenever you use it.

---

If you really need to use dynamic SQL you can put your query in single quotes:

  OPEN my_cursor FOR 'SELECT * FROM allitems';

This string has to be parsed whenever the function is called, which will usually be slower and hides errors in your query until runtime.

Make sure to use bind-variables where possible to avoid hard parses:

  OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;

in SQL*Plus you could also use a REFCURSOR variable:

SQL> VARIABLE x REFCURSOR
SQL> DECLARE
  2   V_Sqlstatement Varchar2(2000);
  3  BEGIN
  4   V_Sqlstatement := 'SELECT * FROM DUAL';
  5   OPEN :x for v_Sqlstatement;
  6  End;
  7  /

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> print x;

D
-
X