Monitoring Oracle Procedure

[Felgar]

I have an application that uses a fairly extensive Oracle database. Each month the user will run a summary process that will take all the data for the prior month and create summary data which is then used for various purposes.

My problem is that the procedure to fetch and load the data has the potential to take quite a long time. It is not really possible to break it up into multiple procedure calls, which would allow me to update the user to what is happenning before I call each piece. I am basically stuck with executing the Oracle procedure and waiting for it to finish.

I was wondering if anyone knew of a way to have the main application monitor what was happenning in the Oracle procedure while it is executing. I was thinking that dbms output or dbms alerts might serve a purpose here. The problem is that the application won't do anything until the procedure is completed, so I can't simply poll for dbms from my application. Perhaps there is a way to tell Delphi to execute the procedure and then forget about waiting for a return. Or perhaps with the use of threads, a small monitoring module could be developed to inform the user what is happenning... I would appreciate any ideas or input. Thanks.

 

[svanels]

Maybe the SQL monitor can help you visualize what is happening inside your DB server. I have not used it (yet) but when I have some more info I will post it.

Regards S. van Els
SAvanEls@cq-link.sr

 

[Felgar]

I am fairly familiar with the SQL monitor. It basically tells you what data/messages have been passed between Oracle and your application. For instance, when invoking a stored procedure, the monitor will show the values of the paramaters, etc.

While useful for many things, the SQL monitor will not serve my purpose because there are no messages moving between Oracle and my application while my stored procedure is executing. However, something like the monitor is what I need, so that I could monitor (dbms alerts likely) any messages created by my procedure as it runs.