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

Question

I have this table:

ALLITEMS --------------- ItemId  | Areas --------------- 1       | EAST 2       | EAST 3       | SOUTH 4       | WEST 

The DDL:

drop table allitems;  Create Table Allitems(ItemId Int,areas Varchar2(20)); Insert Into Allitems(Itemid,Areas) Values(1,'east'); Insert Into Allitems(ItemId,areas) Values(2,'east'); insert into allitems(ItemId,areas) values(3,'south'); insert into allitems(ItemId,areas) values(4,'east'); 

In MSSQL, to get a cursor from a dynamic SQL I can do:

DECLARE @v_sqlStatement VARCHAR(2000); SET @v_Sqlstatement = 'SELECT * FROM ALLITEMS'; EXEC (@v_sqlStatement); --returns a resultset/cursor, just like calling SELECT  

In Oracle, I need to use a PL/SQL Block:

SET AUTOPRINT ON; DECLARE  V_Sqlstatement Varchar2(2000);  outputData SYS_REFCURSOR; BEGIN  V_Sqlstatement := 'SELECT * FROM ALLITEMS';  OPEN outputData for v_Sqlstatement;  End; --result is : anonymous block completed 

**But all I get is

anonymous block completed".

How do I get it to return the cursor?

(I know that if I do AUTOPRINT, it will print out the information in the REFCURSOR (it's not printing in the code above, but thats another problem))

I will be calling this Dynamic SQL from code (ODBC,C++), and I need it to return a cursor. How?

Answer 1

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; 

Answer 2

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