Can not return table type from a function PLSQL

Question

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;

Answer 1

It appears that you cannot select using the function directly in the SQL like this:

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

But you can do this:

declare
  coll package_employees.name_table;
begin
  coll := package_employees.f_deps ('6');
  for r in (select * from table(coll)) loop
    dbms_output.put_line(r.name);
  end loop;
end;

Your function code isn't right though, it should be more like this:

function f_deps
  (i_id_dept IN integer) 
  return name_TABLE  is 
t_name name_TABLE;
rec_name  name_RECORD;

BEGIN

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

return t_name;

END f_deps;

i.e.

• No cursor and loop needed
• You need to construct a value of type name_record to bulk collect into a collection of name_record.