I would like to generate a SQL statement within a package function and bulk collect the records into a table type returned by the function. I then need to get the data using regular SQL (SELECT * FROM TABLE(…)).

I get an ORA-00932 error when using dynamic SQL and EXECUTE IMMEDIATE … BULK COLLECT INTO … However, if I write the SQL state completely in the function, then it works. To avoid duplicate code, I would to be able to construct the SQL dynamically based on an input to the function.

For performance reasons it is a requirement to use BULK COLLECT.

Test case:

CREATE TABLE foobar AS SELECT last_ddl_time, object_name FROM dba_objects;
DROP TYPE t_table;
DROP TYPE t_record;
CREATE TYPE t_record IS OBJECT( ts DATE, name VARCHAR2 (50) );
CREATE TYPE t_table IS TABLE OF t_record;

CREATE OR REPLACE PACKAGE foobar_pkg AS
   FUNCTION nogood (v_input NUMBER)
      RETURN t_table;

   FUNCTION good1
      RETURN t_table;

   FUNCTION good2
      RETURN t_table;

END;

CREATE OR REPLACE PACKAGE BODY foobar_pkg AS
   FUNCTION good1
      RETURN t_table IS
      l_table   t_table := t_table ();
   BEGIN
      SELECT t_record (last_ddl_time, 'option 1: ' || object_name)
        BULK COLLECT INTO l_table
        FROM foobar;

      RETURN l_table;
   END;

   FUNCTION good2
      RETURN t_table IS
      l_table   t_table := t_table ();
   BEGIN
      SELECT t_record (last_ddl_time, 'option 2: ' || object_name)
        BULK COLLECT INTO l_table
        FROM foobar;

      RETURN l_table;
   END;

   FUNCTION nogood (v_input NUMBER)
      RETURN t_table IS
      l_sql     VARCHAR2 (500);
      l_table   t_table := t_table ();
   BEGIN
      IF v_input = 1 THEN
         l_sql := 'SELECT last_ddl_time, ''option 1: '' || object_name FROM foobar';
      ELSE
         l_sql := 'SELECT last_ddl_time, ''option 2: '' || object_name FROM foobar';
      END IF;

      EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_table;

      RETURN l_table;
   END;

END;

SELECT * FROM TABLE (foobar_pkg.nogood (1));
SELECT * FROM TABLE (foobar_pkg.nogood (2));
SELECT * FROM TABLE (foobar_pkg.good1);
SELECT * FROM TABLE (foobar_pkg.good2);

CREATE OR REPLACE PACKAGE BODY foobar_pkg AS
   FUNCTION good1
      RETURN t_table IS
      l_table   t_table := t_table ();
   BEGIN
      SELECT t_record (last_ddl_time, 'option 1: ' || object_name)
        BULK COLLECT INTO l_table
        FROM foobar;

      RETURN l_table;
   END;

   FUNCTION good2
      RETURN t_table IS
      l_table   t_table := t_table ();
   BEGIN
      SELECT t_record (last_ddl_time, 'option 2: ' || object_name)
        BULK COLLECT INTO l_table
        FROM foobar;

      RETURN l_table;
   END;

   FUNCTION nogood (v_input NUMBER)
      RETURN t_table IS
      l_sql     VARCHAR2 (500);
      l_table   t_table := t_table ();
   BEGIN
      IF v_input = 1 THEN
         --  added t_record call
         l_sql := 'SELECT t_record (last_ddl_time, ''option 1: '' || object_name) FROM foobar';
      ELSE
         --  added t_record call
         l_sql := 'SELECT t_record (last_ddl_time, ''option 2: '' || object_name) FROM foobar';
      END IF;

      EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_table;

      RETURN l_table;
   END;

END;
/

SELECT * FROM TABLE (foobar_pkg.nogood (1))
WHERE  ROWNUM = 1;

TS                NAME                         
----------------- ------------------------------
07 Jul 2014 06:11 option 1: ICOL$               

SELECT * FROM TABLE (foobar_pkg.nogood (2))
WHERE  ROWNUM = 1;

TS                NAME                         
----------------- ------------------------------
07 Jul 2014 06:11 option 2: ICOL$