I have PL/SQL Block, it query from a Table function and I use a cursor to process it record by record, have some business logic and finally write the Qualified records into the file.
Number of records to be processed is upto 1 Million. And the total processing speed is roughly 10000 records per minute.(After testing with few chunks of data)
Now, that I need to indicate the processing status in a different environment, JSP.
DECLARE
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
my_cursor IS SELECT * FROM MYTABLE;
my_details my_cursor%rowtype;
BEGIN
vSFile := utl_file.fopen('ORALOAD', file_name,'r');
IF utl_file.is_open(vSFile) THEN
utl_file.get_line(vSFile, vNewLine);
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_details;
EXIT WHEN sll_cur%NOTFOUND;
-- Do processing
utl_file.putf(logfile,'%s ',my_details);
-- A info tht record completed!
END LOOP;
CLOSE logfile;
CLOSE my_cursor;
END IF;
EXCEPTION
WHEN OTHERS THEN
--Error handling
END;
/
The log information written here, is not available until the completion of the process. So, I am unable to track, how far it is completed. Can someone please assist me on this?