Hi all,
Normally, the stored function return one values per time.In oracle9i pipelined functions return the amount of data at a time,
the first step create a type object.
-
CREATE OR REPLACE package My_Types is
-
type lookup_row is record ( idx number, text varchar2(20) );
-
type lookups_tab is table of lookup_row;
-
end My_Types;
-
the second step create a function
-
create or replace function Lookups_Fn
-
-
(p_id in employees.department_id%type)
-
return emp_Types.lookups_tab
-
pipelined
-
is
-
cursor emp_cur is
-
select employee_id,last_name
-
from employees
-
where department_id=p_id;
-
v_row emp_Types.lookup_row;
-
begin
-
open emp_cur;
-
loop
-
fetch emp_cur
-
into
-
v_row.employee_id,v_row.last_name;
-
exit when emp_cur%notfound;
-
pipe row ( v_row );
-
end loop;
-
return;
-
end Lookups_Fn;
-
Please use it in the future
