"Bulk Collect Into" and "Execute Immediate" in Oracle

Question

is it possible to execute the "bulk Collect into" with the "execute immediate" commands in oracle? All of that would be part of a function that returns a pipe lined table as a result.

Answer 1

Yes, technically you can:

  1  SQL> declare
  2   type x is table of t.id%type index by pls_integer;
  3   xx x;
  4  begin
  5   execute immediate
  6   'select id from t' bulk collect into xx;
  7   dbms_output.put_line(xx.count);
  8  end;
  9  /
426 

And Oracle clearly states this in the documentation:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

But you can use more useful way event if you really NEED to execute Dynamic SQL - weak ref cursors. You will have the access to such powerful option as LIMIT and will be able to use collections of records.

SQL> declare
  2   type x is table of t%rowtype index by pls_integer;
  3   xx x;
  4   c sys_refcursor;
  5  begin
  6    open c for 'select * from t';
  7    loop
  8      fetch c bulk collect into xx limit 100;
  9      dbms_output.put_line(xx.count);
 10      exit when c%notfound;
 11    end loop;
 12    close c;
 13  end;
 14  /
100                                                                             
100                                                                             
100                                                                             
100                                                                             
26