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 do I do this? I'm stumped.

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.

EDIT: See Using REF CURSOR in a .NET Application.

Answer 2

Hi Liao,

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

Answer 3

You should be able to declare a cursor to be a bind variable (called parameters in other DBMS')

like Vincent wrote, you can do something like this:

begin
  open :yourCursor
    for 'SELECT "'|| :someField ||'" from yourTable where x = :y'
      using :someFilterValue;
end;

You'd have to bind 3 vars to that script. An input string for "someField", a value for "someFilterValue" and an cursor for "yourCursor" which has to be declared as output var.

Unfortunately, I have no idea how you'd do that from C++. (One could say fortunately for me, though. ;-) )

Depending on which access library you use, it might be a royal pain or straight forward.