Xref: alice comp.databases.oracle.server:60824 comp.databases.oracle.tools:29815
Path: alice!news-feed.fnsi.net!netnews.com!feed1.news.rcn.net!rcn!not-for-mail
From: ToneCzar@erols.com (Chris Hamilton)
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: Q: DBMS_OUTPUT display while processing?
Date: Sun, 08 Aug 1999 13:09:21 GMT
Organization: City of Washington Pipe Band
Lines: 61
Message-ID: <37ad8015.3078219@news.erols.com>
References: <7oif65$mbk$1@nnrp1.deja.com> <37afcc11.6058071@newshost.us.oracle.com>
Reply-To: toneczar@erols.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: 15Z+CB6DhLS2LRGxA1vHngyegd8NxHQQET+EkLWzT3MKPlTJGdpULA==
X-Complaints-To: abuse@rcn.com
NNTP-Posting-Date: 8 Aug 1999 13:10:03 GMT
X-Newsreader:  Forte Agent 1.5/32.451

On Sun, 08 Aug 1999 00:19:50 GMT, tkyte@us.oracle.com (Thomas Kyte)
wrote:

>A copy of this was sent to ewong74@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@wallstreetsports.com
http://www.wallstreetsports.com/