dbms_output.putlin show up AFTER completion???

[Andre]

Hello all,

I have made a procedure which is running a very, very long time (about
20 hours)

Within the procedure I put some output-lines, to show the user that
the procedure is still running. Lines like this
DBMS_OUTPUT.PUT_LINE('percentage ready : '!!mypercentage);

But when I start the procedure I DO NOT see these lines. The lines do
appear AFTER the procedure is finished. So after 20 hours I get a lot
of output-lines at once.

How can I show the user the lines while running the procedure???

(Using oracle 8.1.7, I am starting the procedure from sql+ (exec
myserver.myprocedure))
Thanks a lot and kind regards,
Andre van Rossem
The Netherlands

[Daniel Roy]

Congratulations,
you just discovered (the hard way, unfortunately!) probably the
biggest drawback of PL/SQL! What you have to do is rely on something
else than DBMS_OUTPUT.PUT_LINE to find out where you are in your
subroutine. I personally usually make use of
DBMS_APPLICATION_INFO.SET_MODULE spread through the code to see where
my PL/SQL block is at. If using this, you need to look in V$SESSION
(columns ACTION and MODULE) to see where the program is. One more
option is to use DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (you then
spy on the dynamic view V$SESSION_LONGOPS to see where you're at). 20
hours seems like a very long time for a procedure. What about posting
your code so that we can give some ideas for performance improvement?
Or you can email me if you want.

Daniel (remove JUNK from my email address to reach me).