title: Returning a table from an Oracle function
body: <p>I've looked at many solutions here to try to solve this and they have gotten pretty far but now I'm in the weeds on some errors that I can#t seem to get past.</p>
  
  <p>I am on Oracle 11g. I need a function to return a record set (table). Here is the code I'm using:</p>
  
  <pre><code>CREATE TYPE T_TABLE IS OBJECT
  (
      Field1 int
      , Field2 int
  );
  
  CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  
  CREATE OR REPLACE FUNCTION FN_MyFunction
  RETURN T_TABLE_COLL 
  IS
  BEGIN
    FOR I IN (SELECT Field1, Field2 FROM Table1) LOOP
      IF I.Field1 = 1 THEN
          BEGIN           
              INSERT INTO T_TABLE     
              SELECT Field1, Field2
              FROM Table2
              WHERE Field2 = I.Field2;
          END;
      ELSIF I.Field1 = 2 THEN
          BEGIN           
              INSERT INTO T_TABLE     
              SELECT Field1, Field2
              FROM Table2
              WHERE Field2 = I.Field2;
          END;  
    END IF;
    END LOOP;
    RETURN T_SMRYACCT_TABLE_COLL;
  END;
  </code></pre>
  
  <p>The errors I receive from this are:</p>
  
  <ol>
  <li><p>Statement Ignored on the FUNCTION FN_MyFunction line and PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here on each line INSERT INTO T_TABLE_COLL line</p></li>
  <li><p>PLS-00330: invalid use of type name or subtype name on the RETURN line</p></li>
  </ol>
  
  <p>What am I doing wrong with the table types? </p>