return full recordset and sum of recordset

i've got a procedure which is supposed to return a ref cursor but also the sum(field1) from the cursor.

The select statement is quite complex (and will probably be dynamic) and I assume it would be better to do this once rather than twice. In SQL Server I would have done the complex select into a temporary table, then done the sum from the temp table and created the cursor from the temp table but I know temp tables are frowned upon in Oracle.

so:
select sum(amount) into mytotal from .....(complex select) open mycursor for select * from .....(complex select)

is there a better faster way where a) the select is only done once and b) I only have to define it once avoiding the risk that when and if it is maintained that they fall out of synch?

Thanks in advance
Phil Received on Mon May 16 2005 - 10:13:34 CDT