Re: serveroutput in PL/SQL scripts

Hi

Just change your calls to dbms_output into utl_file, you can do this with one command in vi ie

:1,$s/dbms_output.put_line(/utl_file.put_line(fptr,/g

 or search and replace in a windows editor. You will need to declare a file pointer

fptr utl_file.file_type;

and then open the file

fptr:=utl_file.fopne('directory','filenaqme','A');

and after you have finished close it with

utl_file.fclose(fptr);

you will need the dba to add a directory to the init parameter utl_file_dir or use one that exists, this is the directory in your fopen call. The database will need to be bounced for this to take.

The main thing for you with this is that utl_file writes output straight away so you can see where your long pl/sql hangs. you can tail -f the file or create the file first as a pipe and cat it before you start writing in another terminal.

hope this helps

cheers

Pete Finnigan
www.pentest-limited.com

In article <760023b4.0108290736.69bd9190_at_posting.google.com>, Scott Mattes <ScottMattes_at_yahoo.com> writes
>I don't think the idea someone had of writing to a table will work,
>since you can't see what is there until after a) your program ends and
>IF you are running it from SQL/PLUS you can do a select, b) a commit
>is done (and I don't use commits while I am debugging).
>
>I have had occasion these past couple of days to look into DBMS_PIPE
>more and I think I have a way of using this package without having to
>write an external program (well, at least you don't have to know C or
>Delphi, just PL/SQL).
>
>Read the article at http://www.elementkjournals.com/dbm/9701/dbm9714.htm
>and then envision using two SQL/PLUS or TOAD sessions. The one seesion
>is where your program to debug is running and in its code you write to
>the pipe. In the other seesion you will periodically run a PL/SQL
>script that for each execution it will read everything currently in
>the pipe, writes that information with DBMS_OUTPUT and then exits (so
>that the output will display, don't forget to 'set serveroutput on').
>Every so often you run the reader script (you might want to turn on
>logging on the reader session of SQL/PLUS).
>
>This isn't as 'nice' as it could be, and if I get some spare time I'll
>work on a Delphi app to do this in a more user friendly way.
>
>
>"Scott Mattes" <ScottMattes_at_yahoo.com> wrote in message news:<VDsi7.368$QG5.3737
>56_at_news1.news.adelphia.net>...
>> There is NONE! A big shame, since Oracle is such a big company.
>>
>> DBMS_OUTPUT.put_line is queuing ALL your output to a PL/SQL table whose max
>> size is 1,000,000 characters (only if you set it that high, the default is
>> much lower).
>>
>> And, as you noticed, the msgs don't come out until after the PL/SQL has
>> ended. Very unuseful.
>>
>> I wrote something that uses TEXT_IO for use in Forms and Reports, but
>> TEXT_IO isn't available outside of them. Mine writes to a file and closes it
>> after each write, so I can at least get an idea of how things are going.
>>
>> There are 'options', do a google search on DBMS_OUTPUT to see the ideas
>> about pipes. It sounds like more work than anyone should have to do to get
>> something so simple as console I/O.
>>
>>
>> "Adam" <ask_at_me.spam> wrote in message
>> news:LE4h7.64$Nz1.1724_at_news.get2net.dk...
>> > Hey.
>> >
>> > I have a PL/SQL script which is about 850 lines long. Along the way I have
>> > some dbms_ouput.put_line statements to show progress and so on.
>> >
>> > But it seems to me that when I execute the script from SQL*Plus the output
>> > doesn't show until the entire script has executed, and this is a problem
>> > because somewhere along the line it hangs the DB for some reason, and I
>> > don't know where. I realise I could comment out parts of the program, but
>> > that will take 100 years to trace the error. Instead, I would like to see
>> > the output lines when they are called, instead of when the script
>> finishes,
>> > so that I can see where it fails or infinite loops or whatever.
>> >
>> > Anybody know of a SQL*Plus setting or PL/SQL command that forces the
>> output
>> > on screen as the .put_line commands are reached?
>> >
>> > Thanks,
>> > Adam
>> >
>> >

-- 
Pete Finnigan
IT Security Consultant
PenTest Limited

Office  01565 830 990
Fax     01565 830 889
Mobile  07974 087 885

pete.finnigan_at_pentest-limited.com

www.pentest-limited.com