The function return amount of data at a time (Oracle)

[holdingbe]

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

[rk2006]

Hi I tried to use the above .but some how this functions fails to return the set of values when used directly..say Lookups_fn('6') .pls advice