Trying to get table from Oracle function. Get Inconsistent datatypes instead

Question

I'm trying to return a query from a stored function in Oracle. This simple attempt fails:

CREATE OR REPLACE TYPE GetObjectsForFolderObjectType IS OBJECT (
       FOLDER_ID NUMBER(9), OBJECT_ID NUMBER(9)
);

CREATE OR REPLACE TYPE GetObjectsForFolderTableType IS
       TABLE OF GetObjectsForFolderObjectType;

CREATE OR REPLACE FUNCTION GetObjectsForFolder
       (FOLDER_ID INTEGER, FOLDER_DATA_TABLE VARCHAR2)
RETURN GetObjectsForFolderTableType IS 
       THE_RESULT GetObjectsForFolderTableType := GetObjectsForFolderTableType ( );
BEGIN 
       EXECUTE IMMEDIATE 'SELECT * FROM '||FOLDER_DATA_TABLE BULK COLLECT INTO THE_RESULT;
       RETURN THE_RESULT;
END; 

SELECT * FROM TABLE(GetObjectsForFolder(1, 'MY_TABLE_NAME'));

The result of the above is

ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "USERNAME.GETOBJECTSFORFOLDER", line 6
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Note: although I've masked some things in the example, the ORA-00932 line does display "expected - got -".

Answer 1

In your dynamic PL/SQL statement you cannot bulk collect the table columns directly into your table of objects. You must instead first select your columns into your object, then bulk collect your objects into your table type:

    CREATE OR REPLACE TYPE GetObjectsForFolderObjectType IS OBJECT (
           FOLDER_ID NUMBER(9), OBJECT_ID NUMBER(9)
    );

    CREATE OR REPLACE TYPE GetObjectsForFolderTableType IS
           TABLE OF GetObjectsForFolderObjectType;

    CREATE OR REPLACE FUNCTION GetObjectsForFolder
           (FOLDER_ID INTEGER, FOLDER_DATA_TABLE VARCHAR2)
    RETURN GetObjectsForFolderTableType IS 
           THE_RESULT GetObjectsForFolderTableType := GetObjectsForFolderTableType ( );
    BEGIN 
           EXECUTE IMMEDIATE 'SELECT GetObjectsForFolderObjectType(folder_id, object_id) FROM '||FOLDER_DATA_TABLE BULK COLLECT INTO THE_RESULT;
           RETURN THE_RESULT;
    END;