Dynamic PL/SQL and Bulk Collect

Today was the first time that I needed to use dynamic sql with BULK COLLECT. It was due to a distributed database system that used dblinks from a master reporting database. I programmed this on Oracle 10g but it should work on 9i or later.

declare

  TYPE table1Type is TABLE OF table1%ROWTYPE
        index by binary_integer;
   v_results      table1Type ;

begin
  v_sql := 'SELECT *
             FROM  table1@' || v_dblink || '
             WHERE  mydate_dttm > sysdate - 1';
     
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_results;
end;

That's it... This loaded all of the records into the v_results PL/SQL table and you can loop through the results like a array.