I am handling millions of rows and it takes hours, so I want some feedback to give me an idea about the status of the process. It won't be possible to have real time feedback from a stored procedure since the output will be available only after the complete execution. Any solution?
There are a couple of solutions.
One is to write to a log of some description. You can use a file (writing out with UTL_FILE) or a table (using autonomous transactions so that the records are visible in another session without affecting the main transaction).
The other solution is to write to the V$SESSION_LONGOPS view using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS. Find out more
I think logging is always a good idea with long-running background procedures. If something goes wrong your logs are the only source of info you have.