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;