/*
|| spec!
*/
CREATE OR REPLACE PACKAGE dynquery
IS
   TYPE tdatatab IS TABLE OF VARCHAR2 (256)
      INDEX BY BINARY_INTEGER;

   PROCEDURE retrieve_data (query IN VARCHAR2, data IN OUT tdatatab);
END;
/

/*
|| body!
*/
CREATE OR REPLACE PACKAGE BODY dynquery
IS
   PROCEDURE retrieve_data (query IN VARCHAR2, data IN OUT tdatatab)
   IS
      sel_cursor   INTEGER;
      err#         NUMBER (8);
      retval       VARCHAR2 (256);
      rec_count    NUMBER (8);
   BEGIN
      rec_count := 0;
      data.delete;
      sel_cursor := DBMS_SQL.open_cursor;
      DBMS_SQL.parse (sel_cursor, query, DBMS_SQL.v7);
      DBMS_SQL.define_column (sel_cursor, 1, retval, 256);
      err# := DBMS_SQL.execute (sel_cursor);

      LOOP
         rec_count := rec_count + 1;

         IF DBMS_SQL.fetch_rows (sel_cursor) > 0
         THEN
            DBMS_SQL.column_value (sel_cursor, 1, retval);
            data (rec_count) := retval;
         ELSE
            EXIT;
         END IF;
      END LOOP;
   END;
END;
/

set serveroutput on
/*
|| Test it
*/
declare
   d   dynquery.tdatatab;
   i   NUMBER (8);
BEGIN
   dynquery.retrieve_data ('select object_name || '','' || object_type from user_objects where rownum < 21',
      d
   );
   FOR i IN 1 .. d.COUNT
   LOOP
      DBMS_OUTPUT.put_line (d (i));
   END LOOP;
END;
/