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('percenta ge 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.myproc edure))


Thanks a lot and kind regards,
Andre van Rossem
The Netherlands

[Daniel Roy]

Re: dbms_output.put lin show up AFTER completion???

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_APPLICATIO N_INFO.SET_MODU LE 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_APPLICATIO N_INFO.SET_SESS ION_LONGOPS (you then
spy on the dynamic view V$SESSION_LONGO PS 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).