Re: Q: DBMS_OUTPUT display while processing?

[Quoted] Chris Hamilton wrote:
>
> On Sun, 08 Aug 1999 00:19:50 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
> wrote:
>
> >A copy of this was sent to ewong74_at_netscape.net
> >(if that email address didn't require changing)
> >On Sat, 07 Aug 1999 23:23:56 GMT, you wrote:
> >
> >>Hi,
> >>
> >>I have a large PL/SQL package with 20 procedures within the package.
> >>The purpose of the package is do some batch process which will take
> >>about 10 hours.
> >>
> >>I tried to have DBMS_OUTPUT.PUT_LINE in every single procedure to print
> >>out the status so that I can monitor each process(to see if there is
> >>errors) while the package is still running. However, the DBMS_OUTPUT
> >>didn't display until the whole package is done. This is very annoying.
> >>Is there any work around?
> >>
> >>Thanks in advance.
> >>
> >>Ed
> >>
> >>
> >>Sent via Deja.com http://www.deja.com/
> >>Share what you know. Learn what you don't.
> >
> >
> >dbms_output cannot be display until the procedure is done executing.
> >
> >one option that works very well is the dbms_application_info. this allows you
> >to set values in the v$session table (to tell where you are) and in 8.0 and up,
> >to put many rows in the V$SESSION_LONGOP table with the
> >dbms_application_info.set_session_longops procedure. these rows are visible
> >immediately (no commit needed) so they are perfect for monitoring long running
> >procedures from any terminal (a simple query shows the status of a long running
> >procedure that has calls to dbms_application_info in it)....
>
> I had a similar problem. I created a series of "Job Control" tables:
>
> JC_JOBS -- one row per batch job per run.
> JC_TASKS -- one row per task of each job.
> JC_EVENTS -- one row per event of each task.
>
> And a package called JOB_CONTROL.
>
> My batch PL/SQL programs make calls to JOB_CONTROL, which in turn
> populates the tables with status rows, which have timestamps and
> descriptions of the tasks. You can then track the ongoing status of
> jobs, seeing where it is and how long it took to get to that point.
> Also tracked are parameters passed to the various events (procedures),
> etc.
>
> Very handy.
>
> Chris
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Christopher Hamilton
> Oracle DBA -- Wall Street Sports
> chris_at_wallstreetsports.com
> http://www.wallstreetsports.com/

The only downside being that you need to commit to see that status from a different session...

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."