Re: serveroutput in PL/SQL scripts

[Quoted] 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.373756_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
> >
> >
Received on Wed Aug 29 2001 - 17:36:12 CEST