Re: dbms_output and trigger

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 7 Oct 2005 07:58:49 -0400
Message-ID: <-dOdnd344rBj_9veRVn-vA@comcast.com>

<sybrandb_at_yahoo.com> wrote in message
news:1128673533.967653.47990_at_g14g2000cwa.googlegroups.com...
> As far as I discovered, before being flushed to stdout, the output goes
> to your PGA.
> If the output is never flushed, you can run out of PGA.
> That happened to me once.
> So you may think the output is going into a 'black hole', but it isn't,
> and it can potentially bite you.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

I took a few minutes to test my assumptions about enabled vs not enabled DBMS_OUTPUT From the docs (10g):

---

ENABLE Procedure
This procedure enables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_ LINES. Calls to these procedures are ignored if the DBMS_OUTPUT package is not
enabled.

---

So, presumably, your experience was with DBMS_OUTPUT enabled, which of course would eventually give the 'ORU-10027: buffer overflow' error

Without enabling DBMS_OUTPUT, this block appears to run forever, and the PUT_LINE appears to do nothing

begin
loop
dbms_output.put_line('endless loop');
end loop;
end;

PGA usage does not change (based on 'session pga memory' in V$SESSTAT)

But with DBMS_OUTPUT enabled, ORU-10027 comes quickly, and PGA usage increases

++ mcs Received on Fri Oct 07 2005 - 06:58:49 CDT