Re: SQL Worksheet: Bufferoverflow

Karsten Farrell wrote:

> DA Morgan wrote:
> > I've got what I think is a better idea. Drop the DBMS_OUTPUT calls in your
> > code. They do not belong in any code after debugging.
> >
> > Daniel Morgan
> >
> Just curious - FMI (For My Information):
>
> I mostly agree with you ... but I have a whole series of "production"
> scripts that create and then populate all the schema objects for our
> application developers here (each developer is a schema owner).
>
> They're filled with 'prompt' statements (if sqlplus) or
> 'dbms_output.put_line' statements (if pl/sql). It allows me to write
> stuff to the screen (or spooled output) that tells what's going on. A
> sqlplus session (with judicious 'set' commands) looks something like:
>
> SQL> _at_schema_sequences
> ==Begin schema_sequences.sql
> ..Found n existing sequences
> ..Drop existing sequences
> ..Create sequences
> ....Create sequence: seq_name_1
> ...[etc]...
> ....Create sequence: seq_name_n
> ..Created n sequences
> ==End schema_sequences.sql
>
> I also leave all (or many of) my debugging statements in procedures,
> functions, and packages (just in case I need to modify them in the
> future and don't want to type them all in again to verify that my
> changes didn't break something).
>
> Am I missing something? Or were you making a "generic" statement? Even
> Tom Kyte (I don't like to be a name-dropper) has a 'set serveroutput on
> size 1000000' in his login.sql (at least he does according to his book).

What I don't like about production code wth DBMS_OUTPUT in it is that it is overhead without purpose and just more junk that someone doing maintenance must wade through.

I almost never use DBMS_OUTPUT for debugging purposes either. What I use is a procedure with PRAGMA AUTONOMOUS_TRANSACTION that accepts messages and inserts values into a table. This way I get my messages no matter what happens, I can easily review them after the code has run without worry about scrolling stuff off the screen, compare last run with current run, etc.

I'm certainly not saying that DBMS_OUTPUT has no place and should never be used. But a simple proc beats it in every way I can imagine without ever having to worry about buffer size, etc. And no matter which way debugging is done ... I still think all debug code should be stripped before something goes from dev to test and definitely should be gone when it goes to production.

Daniel Morgan Received on Wed Jan 15 2003 - 18:02:21 CET