Accessing Table type/Record Type PLSQL function using Oracle SQL Statement.

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>