CODEWITHSUNDEEP
sqloracle-database
user92382
user92382

Reputation: 389

ORACLE table valued function

I'm in the process of migrating from MS SQL Server to Oracle and I'm struggling with a simple table-valued function:

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'));
/

However it fails with

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.

Any help is appreciated.

Upvotes: 1

Views: 3626

Answers (1)

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

CREATE OR REPLACE FUNCTION fEmployee (
dep IN VARCHAR
)
RETURN ATypeCol PIPELINED IS
BEGIN
  FOR i IN (SELECT Department.Name, Employee.FirstName || ' ' || Employee.LastName Employee
            FROM Employee JOIN Department ON DepartmentId = Department.Id
            WHERE Department.Name = dep) LOOP
            PIPE ROW(AType(i.Name, i.Employee));
  END LOOP;
  RETURN;
END;

Formal parameter must not have scale/precision. Also, you should specify correct aliases in cursor. Please use SELECT * FROM USER_ERRORS WHERE NAME = '' or "show error" SQL*Plus command to get the information about compilliation errors.

Upvotes: 1

Related Questions