469,082 Members | 1,258 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

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

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.
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE package My_Types is
  2.   type lookup_row is record ( idx number, text varchar2(20) );
  3.   type lookups_tab is table of lookup_row;
  4. end My_Types;
  5.  
the second step create a function
Expand|Select|Wrap|Line Numbers
  1. create or replace function Lookups_Fn
  2.  
  3. (p_id in employees.department_id%type)
  4. return emp_Types.lookups_tab
  5.   pipelined
  6. is
  7.   cursor emp_cur is
  8.   select employee_id,last_name
  9.   from employees
  10.   where department_id=p_id;
  11.   v_row emp_Types.lookup_row;
  12. begin
  13.   open emp_cur;
  14.   loop
  15.   fetch emp_cur
  16.   into
  17.   v_row.employee_id,v_row.last_name;
  18.   exit when emp_cur%notfound;
  19.   pipe row ( v_row );
  20.   end loop;
  21.   return;
  22. end Lookups_Fn;
  23.  
Please use it in the future
Jul 20 '07 #1
1 3150
rk2006
1
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
Aug 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Penn Markham | last post: by
2 posts views Thread by spawncamper | last post: by
21 posts views Thread by Hallvard B Furuseth | last post: by
3 posts views Thread by Aaron | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.