Using pipe is a good example.
Here is another method w/o pipe:
drop type myTableType
/
drop type myRecordtype
/
drop function my_function
/
create or replace type myRecordType
as object ( x int)
/
create or replace type myTableType
as table of myRecordType;
/
create or replace function my_function
(p_st in number, p_end in number )
return myTableType
as
my_data myTableType := myTabletype();
J number := 0 ;
begin
for I in p_st .. p_end loop
J := I - p_st + 1;
my_data.extend;
my_data(J) := myRecordType(I);
end loop;
return my_data;
end;
/
select * from TABLE ( cast( my_function(10, 20) as mytableType ) )
/
select * from TABLE
( cast ( my_function((select min(id) from t1), (select max(id) from t1)) as mytabletype))
minus
select * from t1
/
The cube solution is simple that works w/o a procedure/functions.
Tamil