Re: Q: DBMS_OUTPUT display while processing?
Date: Sun, 08 Aug 1999 13:09:21 GMT
Message-ID: <37ad8015.3078219_at_news.erols.com>
[Quoted] 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)....
[Quoted] 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/ Received on Sun Aug 08 1999 - 15:09:21 CEST