Returning a table from an Oracle function

Question

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.

I am on Oracle 11g. I need a function to return a record set (table). Here is the code I'm using:

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;

The errors I receive from this are:

1. 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

2. PLS-00330: invalid use of type name or subtype name on the RETURN line

What am I doing wrong with the table types?

Solution

T_TABLE_COLL is a collection. You cannot use insert on collections.

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL
IS
  l_res_coll T_TABLE_COLL;
  l_index number;
BEGIN
  l_res_coll := T_TABLE_COLL();
  FOR I IN (SELECT col1, col2 FROM Table1)
  LOOP
    IF I.col1 = 1 THEN
      l_res_coll.extend;
      l_index := l_res_coll.count;  
      l_res_coll(l_index):= T_TABLE(i.col1, i.col2);
    END IF;
  END LOOP;
  return l_res_coll;
END;

Function in action

select *
  from table(FN_MyFunction())

To get more information about what collections are and how to use them read this

Comments

[Ben]

Kudos for answering! One quick point you've removed the elsif, which was doing nothing. I think this was a coding error on the OPs part rather than anything else...

[Matt]

Thanks for the response. I missed that this was a collection. One follow up question though, do you see any more straight forward ways of returning a table from a function? Im actually converting TSQL code that was using a table-value function which is not converted easily to PLSQL.

[Matt]

corrected the ELSIF to be relevant, it was a conversation error. the actual SQL has about 6 ELSIF's, didnt think to was unnecessary to post them all.

[Nick Krasnov]

Take a look at this docs.oracle.com/cd/E11882_01/appdev.112/e10765/….