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> 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>