Reuse cursors in Stored Procedure

Hi

I have a stored procedure which basically does the following:

function XYZ (...) return cur_abc is

	cr cur_abc;
	temprec r_abc;
begin;
	open cr for
		select <big statement>;
	loop
		exit when cr%notfound;
		fetch cr into temprec;
		update <history_table> set ... where key = temprec.xyz_key;
	end loop;
	close cr;
	open cr for
		select <the same statement as above>;
	return cr;

I'm calling this procedure from Java through JDBC using some proprietary feature of the Oracle JDBC driver. It's the only way I have found to return result sets from stored procedures to Java programs. BTW, has anyone done the same and has figured out when the returned cursor actually is closed? I don't see how to explicitly close it in the Java program.

My problem is now, that the above statement is a really big one, and it's written twice, so it's quite dangerous to update it, as I might forget to do it in both statements. Is there any way to reuse an already completely fetched cursor, so that I can return it and fetch the data again? Is there any other way to avoid having the statement twice (other than doing the update operation in the Java main program, which I don't want to do)?

Thanks for any comment.

bye
--
Sam Jordan Received on Wed Dec 29 1999 - 09:58:21 CST