SQL & PL/SQL

Hi Oracle Gurus,

I need to create a package which counts the no of rows returned by a select query on multiple tables and according to the returned rowcount inputs the resultset in a ref cursor. Procedure will be called by a .NET program and output param refcursor will be assigned to a data reader which will redirect all the data to an Excel file.

All the above is done. Issue is due to Excel's limit of 64000 rows, if data returned by query is greater than 64K it wont be fit in 1 Excel sheet. So, in order to overcome this limit I need to do some looping in Oracle package which keeps on storing the query results (rows<64K) in different ref cursors so that these refcursors as OUT params can be redirected to separate Excel sheets in C# program.

NOTE : Earlier on I created 2 procedures in the package to fetch rows<64K and another one to fetch rows between 64K and rowcount of the query. My program was calling 2 different procedures to redirect data into 2 diff Excel sheets.

But this fails when query resultset is even greater than 128000 or more and demands 3-4 or even more Excel sheets to be created.

Please help.

Any idea how to do looping in Oracle to accomplish this?