returning cursors
Date: 10 Jan 2003 13:42:02 -0800
Message-ID: <2f6a8aa.0301101342.3b780631_at_posting.google.com>
Hi,
I am trying to return a cursor from a function but am running into
problems. I see examples on the web of how you open a cursor and
return it. My problem is that I need to update each record and then
return the same set of records back to the calling program.
Here is what I am trying to do:
create or replace function foo(x in number)
return type.ref_cursor as -- where ref_cursor has been previously
defined
s_cursor types.ref_cursor ;
nextnum number;
s_rec stock_prices%rowtype;
begin
open s_cursor for select * from tab1 where rownum < x + 1 ;
loop
fetch s_cursor into s_rec ;
select to_char(sysdate, 'yyyymmddhh24miss')||id.nextval into nextnum
from d
ual ;
update tab1 set field1=nextnum,field2='xxxx' where
field1=s_rec.field1 ;
if stock_cursor%NOTFOUND then
exit ;
end if ;
end loop ;
return s_cursor ;
end foo;
--- I know I can close the cursor, open it again and return it, but I could have multiple processes doing the same thing and I don't want to get different set of records the second time. Thanks PradeepReceived on Fri Jan 10 2003 - 22:42:02 CET