Hi All

I have following code for a package and a function.

create or replace package types
as
type cursorType is ref cursor;
end;
/

create or replace function ListEmp(eno number) return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp
where empno >= eno order by ename;
return l_cursor;
end;
/

I can see the result as follows.

variable c refcursor
exec :c:= listemp(7788)

However I need to use the function in select. If I call above function in normal select statement it gives me error as follows:

select listemp(7788) from dual;

ERROR at line 1:
ORA-00902: invalid datatype

This may be happening because this returns a cursor data type. Can anyone tell me how to display data from functions using SELECT which returns a CURSOR.

Thanks in Advance

Amol