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

User · Answer

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.

User · Answer

This setting needs to be set   SET SERVEROUTPUT ON

User · Answer

in SQL Plus you could also use a REFCURSOR variable   SQL gt  VARIABLE x REFCURSOR SQL gt  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 gt  print x   D - X

User · Answer

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

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

Examples related to oracle

Examples related to plsql