Re: PL/SQL How to display messages

Martin Douglas <dafis_at_ppp.test.jccbi.gov> wrote:
>
>In article <539a9u$943_at_newsbf02.news.aol.com>, <sjsewell_at_aol.com> writes:
>> Path: news.ou.edu!news.ecn.uoknor.edu!munnari.OZ.AU!spool.mu.edu!uwm.edu!news-peer.gsl.net!news.gsl.net!portc01.blue.aol.com!news=
 tf01.news.aol.com!newsbf02.news.aol.com!not-for-mail
>> From: sjsewell_at_aol.com (SJSewell)
>> Newsgroups: comp.databases.oracle
>> Subject: PL/SQL How to display messages
>> Date: 6 Oct 1996 18:02:38 -0400
>> Organization: America Online, Inc. (1-800-827-6364)
>> Lines: 14
>> Sender: root_at_newsbf02.news.aol.com
>> Message-ID: <539a9u$943_at_newsbf02.news.aol.com>
>> Reply-To: sjsewell_at_aol.com (SJSewell)
>> NNTP-Posting-Host: newsbf02.mail.aol.com
>>
>> Can anyone help me with what I thought would be straight forward
>> problem but has gotten the better of me.
>>
>> Is there any way to print to the screen IMMEDIATELY within PL/SQL ?.
>> For example to give a message showing the progress of a long update.
>>
>> I've used DBMS.OUTPUT.PUT_LINE but this buffers everything up and
>> doesn't get flushed until the end of the program. I thought
>> DBMS.OUTPUT.DISABLE would flush the buffer but it doesnt. You Can
>> flush outputs to files but I just want the screen.
>>
>> Thanks in anticipation.
>>
>> Simon
>>
>That would be cool but I have never found out how to do it. I ended up
>createing a table that contained my counter's, start_date_time,
>current_date_time, and any messages I wanted to see as my procedures
>processed. If you put the messages out at least at commit time then you get a
>good picture of where you are. Then you can always manualy delete your
>"LOG_TABLE" after looking at it and or printing it.
>
>Martin Douglas
>

That's one solution I have used. Messages which have been committed can be monitored from another window.

FYI another problem with the DBMS_OUTPUT.PUT_LINE is that you can run out of buffer space and lose all the messages!

Another solution which works very well is to use a pipe to another table. This overcomes problems when you need to be able to rollback some processes and not others. You can monitor the pipe table from another window. You must open the pipe before starting the program which writes to it. I don't remember offhand all the details, probably can be found in the Utilities manual.

We have set up a "utility" pipe which we can incorporate into any program, using various id columns to make distinctions for later querying.

Regards, Benita. Received on Tue Oct 08 1996 - 00:00:00 CEST