Select from a function

The following steps allow you select from a function return value just like a normal table.

1. Create a record type

Create TYPE MyRecType AS OBJECT
(c1 number, c2 varchar2(5));
/

2. Create a table type

Create Type MyTabType AS Table of MyRecType;
/

3. Create a function returning the Table Type

Create or Replace function MyFunc return MyTabType
AS
mytab MyTabType := MyTabType();
BEGIN
mytab.extend(2);
mytab(1) := MyRecType(1, 'abc');
mytab(2) := myRecType(2, 'efg');
return mytab;
END;
/

4. Do you select

SQL> select * from TABLE(MyFunc);

C1 C2
---------- --------------------
1 abc
2 efg

** Note **
You can make the function as PIPELINE (refer to Oracle Docs for details)