Can not return table type from a function PLSQL

Viewed 1496

I have defined record type, table type and function inside package specification.

TYPE name_RECORD IS RECORD (
name    VARCHAR2(244),
surname VARCHAR2(244) );

TYPE name_TABLE IS TABLE OF name_RECORD;

 function f_deps
  (i_id_dept IN employees.id_department%type) 
  return name_TABLE; 

And wrote function which returns table type inside package body.

function f_deps
  (i_id_dept IN employees.id_department%type) 
  return name_TABLE  is 

CURSOR c1 IS (select * from employees ); 
t_name name_TABLE;
rec_name  name_RECORD;

BEGIN
t_name := name_TABLE();
for i in c1
LOOP

   select  name, surname  BULK COLLECT INTO t_name from employees where id_department = i_id_dept ;             

END LOOP; 

return t_name;

END f_deps; 

Function compiles normal, but when i try to execute function like this:

select * from table( PACKAGE_employees.f_deps ('6')) ;

I get this error:

ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
Error at Line: 29 Column: 22

UPDATE: I have defined types with CREATE TYPE statement in command line, like what Bob Jarvis suggested but I still get the same error message

create or replace type  name_RECORD as object (
name    VARCHAR2(244),
surname VARCHAR2(244) );

create or replace type name_TABLE AS TABLE OF name_RECORD;
1 Answers
Related