Use table function to convert type to a 'table' : Select « Object Oriented « Oracle PL/SQL Tutorial

SQL>
SQL>
SQL> create or replace type myRecordType as object
  2  ( seq int,
  3    a int,
  4    b varchar2(10),
  5    c date
  6  )
  7  /

Type created.

SQL>
SQL>
SQL> create or replace type myTableType
  2  as table of myRecordType
  3  /

Type created.

SQL>
SQL> create or replace function my_function return myTableType
  2  is
  3      l_data myTableType;
  4  begin
  5      l_data := myTableType();
  6
  7      for i in 1..5
  8      loop
  9          l_data.extend;
 10          l_data(i) := myRecordType( i, i, 'row ' || i, sysdate+i );
 11      end loop;
 12      return l_data;
 13  end;
 14  /

Function created.

SQL>
SQL> select *
  2    from TABLE ( cast( my_function() as mytableType ) )
  3   where c > sysdate+1
  4   order by seq desc
  5  /


       SEQ          A B          C
---------- ---------- ---------- ---------
         5          5 row 5      30-JUL-08
         4          4 row 4      29-JUL-08
         3          3 row 3      28-JUL-08
         2          2 row 2      27-JUL-08

SQL>
SQL> drop type myTableType;

Type dropped.

SQL> drop type myRecordType;

Type dropped.

SQL>
SQL>

32.11.Select
	32.11.1.	Selecting Rows from the Table with object type column
	32.11.2.	Select an individual column object from a table
	32.11.3.	Querying Rows from the object Table
	32.11.4.	SELECTing Only One Column in the Composite
	32.11.5.	COLUMN Formatting in SELECT for Object
	32.11.6.	ORA-00932: inconsistent datatypes: expected REF
	32.11.7.	Query table column with user defined type
	32.11.8.	Use table function to convert type to a 'table'
	32.11.9.	Create type and use it in inner query
	32.11.10.	Use user-defined type to combine query logic