Getting Results Back from Execute Immediate

Couldn't find much clear info on this using Google. But, actually its really easy-- if you run your execute immediate inside a stored procedure.

Execute Immediate is normally limited to returning a single row using EXECUTE IMMEDIATE sql INTO var1, var2, var3...

But you can use a SYS_REFCURSOR to store multiple rows.

Before the BEGIN of your stored procedure, just declare a SYS_REFCURSOR:

c2 sys_refcursor;

Inside the BEGIN/END block, open the cursor using your dynamic SQL.

open c2 for dynamicSQL;

Where dynamicSQL is a Varchar2 containing your SQL.

You can now iterate through the cursor like so (just make sure to list enough variables after the FETCH ... INTO ... to store all columns in the cursor!):

LOOP   
      FETCH c2 INTO tmpVar1;
      EXIT WHEN c2%NOTFOUND;
       dbms_output.put_line('Int: ' || tmpVar2);
END LOOP;

And don't forget to close the cursor.

close c2;

And a commit for good measure.

commit;