bulk collect or FOR Loop to generate huge data

[fahadrafique]

How can we create an Excel file using bulk collect or a straightforward for loop instead of a ref cursor?
I need to handle large data files by utilizing values directly from the loop. The current procedure doesn't allow to retrieve the counter value effectively with a ref cursor.

[mbleron]

You can create cells one by one within the loop by calling put*Cell procedures, but it'll be slower and more memory consuming than using a SQL data source.

What do you mean by counter value?

[fahadrafique]

put*Cell is very very slow and consuming huge resources.

counter. i am referring as TOTAL PROCESSED RECORDS within the loop. which i need to display on the front end for the user to how much has been processed so far.

is there any way to get the processed records from within the refcursor, using your technique?

this below PROCESSED RECORD, i need it to get it from your refcusor somehow.

BEGIN
FOR X IN 1..100
LOOP
DBMS_OUTPUT.PUT_LINE('PROCESSED RECORDS='||X)
END LOOP;
END;

[mbleron]

is there any way to get the processed records from within the refcursor, using your technique?

No, it's not possible to monitor the progress.
ExcelGen is not designed that way, it is a server-side process. Once you call createFile, you have to wait for its completion, then you can retrieve the total number of rows processed via getRowCount.

Conceptually, displaying a progress would also require knowing the total amount of work (i.e. rows) in the first place, which is not possible using a cursor.