Re: DBMS_OUTPUT in Production

Hi Steve,

If I were you, I'd comment out the DBMS_OUTPUT calls. I once worked on a package that was supposed to loop records on a table and perform an operation on them. I used DBMS_OUTPUT for my debug messages. I found out that many related tables had triggers that also had debug messages, so a single update from my package caused a cascade of DBMS_OUTPUT. When we put the code in production, we blew out the 1-meg buffer. I quickly fixed that by commenting out all of the calls.

We chose the conservative route. Perhaps you can leave the calls if you include a call to DBMS_OUTPUT.DISABLE in the calling routine, or a SET SERVEROUTPUT OFF in the SQL*Plus.

Tad Harrison
harrist4_at_war.wyeth.com
Wyeth-Ayerst Research
Princeton, NJ

Steve wrote:

> Are there any problems with leaving DBMS_OUTPUT statements in a trigger or
> stored procedure in a production environment?
>
> I used the statements to help debug the trigger and procedure. If I leave
> the statements in after debugging, does the output go into the bit bucket
> after each firing of the trigger or does it keep filling a buffer somewhere
> that eventually overflows?
>
> We have such a situation and it appears that after some use in our
> production environment, the DBMS hangs. We remove the offending trigger and
> all runs normally. Everything else in the trigger and stored procedure
> check out OK. Could DBMS_OUTPUT be the cause?
>
> Thanks,
> Steve Carrell
> scarrell_at_xmission.com
Received on Wed Nov 04 1998 - 10:51:14 CST