title: ORACLE table valued function
body: <p>I'm in the process of migrating from MS SQL Server to Oracle and I'm struggling with a simple table-valued function:</p>
  
  <pre><code>CREATE TABLE Department (
    Id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  
    Name VARCHAR(64) NOT NULL,  
    PRIMARY KEY (Id)
  );
  /
  CREATE TABLE Employee (
      Id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
      PRIMARY KEY (Id),
      DepartmentId INT NOT NULL REFERENCES Department(Id),
      FirstName VARCHAR(64) NOT NULL,
      LastName VARCHAR(64) NOT NULL,
      Email VARCHAR(64) NULL    
  );
  /
  CREATE TYPE AType AS OBJECT (Department VARCHAR(64), Employee VARCHAR(64));
  /
  CREATE TYPE ATypeCol AS TABLE OF AType;
  /
  CREATE OR REPLACE FUNCTION fEmployee(dep IN VARCHAR(64))
  RETURN ATypeCol PIPELINED IS
  BEGIN
    FOR i IN (SELECT Department.Name, Employee.FirstName || ' ' || Employee.LastName
              FROM Employee   JOIN Department ON DepartmentId = Department.Id 
              WHERE Department.Name = dep) LOOP 
              PIPE ROW(AType(i.Department, i.Employee));
    END LOOP;
    RETURN;
  END;
  /  
  SELECT * FROM TABLE(fEmployee('IT'));
  /
  </code></pre>
  
  <p>However it fails with</p>
  
  <pre><code>FUNCTION FEMPLOYEE compiled
  Errors: check compiler log
  
  Error starting at line 94 in command:
  SELECT * FROM TABLE(fEmployee('IT'))
  Error at Command Line:94 Column:21
  Error report:
  SQL Error: ORA-06575: Package or function FEMPLOYEE is in an invalid state
  06575. 00000 -  "Package or function %s is in an invalid state"
  *Cause:    A SQL statement references a PL/SQL function that is in an
             invalid state. Oracle attempted to compile the function, but
             detected errors.
  *Action:   Check the SQL statement and the PL/SQL function for syntax
             errors or incorrectly assigned, or missing, privileges for a
             referenced object.
  </code></pre>
  
  <p>Any help is appreciated.</p>