returning cursors

From: PKN <tigger_at_etigers.net>
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
Pradeep
Received on Fri Jan 10 2003 - 22:42:02 CET

Original text of this message