Re: Aborting PL/SQL script in Exception handler

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/31
Message-ID: <33927b75.3316769_at_newshost>#1/1

On Thu, 29 May 1997 22:22:59 -0500, Will Kooiman <wkooiman_at_csac.com> wrote:

>Mike Hill_at_PW wrote:
>>
>
>-snip-
>
>>
>> This PL/SQL script kicks off a number of other Oracle scripts and
>> procedures. Using dbms_output.put_line, I'm attempting to track progress on
>> the screen. Unfortunately, if I encase the entire PL/SQL script as a single
>> block with a global exception handler on the bottom, the
>> dbms_output.put_line statements do not display to the screen as each script
>> finishes, but rather all at once as soon as the entire PL/SQL is completed.
>
>Sorry, but that's how dbms_output works. When you say
>dbms_output.put_line(), your text is stored in a buffer. It is up to
>SQL*Plus to execute dbms_output.get_lines(), and then to do printf's to
>display the output.
>
>The only way I am aware of to track progress is to use Oracle pipes to
>send the messages to a daemon which writes the output to a file or
>another screen. You can look at the dbms_pipe script in the rdbms/admin
>directory for info on how to use Oracle pipes. I think the name is
>dbmspipe.sql or prvtpipe.sql.
>

If you have 7.3 on UNIX there exists another option (perhaps on NT, don't know if "tail -f" exists on NT or not). Try:

declare

    l_file utl_file.file_type; begin

    l_file := utl_file.fopen( '/tmp','test.dat', 'w' );  

    for i in 1 .. 5 loop
        utl_file.put_line( l_file,  'Hello' );
        utl_file.fflush( l_file );
        dbms_lock.sleep( 10 );

    end loop;  

    utl_file.fclose( l_file );
end;
/

And in another window, do a "tail -f /tmp/test.dat" after you start the above block. You'll see the hello's appear every 10 seconds. By using fflush with the tail -f, you'll see output every time you fflush....

You need to set up utl_file for this to work. See the application developers guide, chapter 8, pl/sql IO...

>Will.
>--
>======================================================================
>Will Kooiman Computer Systems Authority
>Systems Consultant 6380 LBJ Freeway, Suite 181
>(972) 960-0180 x236 Dallas, TX 75240
>mailto:wkooiman_at_csac.com http://www.csac.com

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 31 1997 - 00:00:00 CEST