select * from employee;
create or replace type obj_type as object
(empid number,empsal number);
/
create or replace type nested_table as table of obj_type;
/
create or replace function fnc2
return nested_table
as
cursor c1 is select * from employee;
type cursor_type is table of c1%rowtype;
V_cursor_type cursor_type;
V_nested_table nested_table := nested_table();
begin
--
open c1;
loop
fetch c1 bulk collect into V_cursor_type limit 10;
exit when V_cursor_type.count=0;
for i in V_cursor_type.first .. V_cursor_type.last
loop
V_nested_table.extend;
V_nested_table(V_nested_table.last) := obj_type(V_cursor_type(i).empid,V_cursor_type(i).empsal);
end loop;
end loop;
close c1;
return V_nested_table;
end;
/
select * from table(fnc2);
