CODEWITHSUNDEEP
oracle-databasefunctionplsqloracle11g
Matt
Matt

Reputation: 53

Returning a table from an Oracle function

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?

Upvotes: 3

Views: 39592

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 5

Related Questions