So, I wanted to know if it is an industry practice to not to include dbms_output package in the code?When your code goes into production, then yes, usually dbms_output should not be enabled by default.
And if it is so, then why ?For performance and/or resource reasons.
Tany wrote:The problem is with your code directly calling the DBMS_OUTPUT package. There is no flexibility when this is done.
I use dbms_output built-in package for debugging messages whenever I write a package/procedure etc., so that I know WHICH PART of code is creating problem.
But in my organization, it is not appreciated on the production environment and on code maintenance tools.
So instead of your app code calling DBMS_OUTPUT.put_line(), it simply calls DebugLib.Write().
create or replace package DebugLib as --// initialises debugging - if not call then none of --// the calls below actually records data procedure Initialise( .. ); --// writes a debug line procedure Write( line varchar2 ); --// records the PL/SQL stack procedure WriteCallStack; --// etc. end;
So, I wanted to know if it is an industry practice to not to include dbms_output package in the code? And if it is so, then why ?Industry practise? Industry is littered with ignorant people.
Tany wrote:Yes. DBMS_OUTPUT has a static PL/SQL variable that is used as the text buffer. This resides in PGA (private process memory). The more data written into the buffer, the more memory consumed. Have a number of these processes (e.g. database sessions or database jobs) running, each doing this - each of these is spending server memory on the DBMS_OUTPUT text buffer.
By default, dbms_output is not enabled. But, if it is enabled, will it affect database performance and consume unnecessary resources?
tkyte wrote:Agree. And that is what I said, wasn't it?
I disagree here.
dbms_output is by default NOT enabled.
If the code already has dbms_output for tracing/diagnostic stuff in it, I'd be glad to accept it into production, if it had ZERO instrumentation - I'd be not as willing to let it into production.Agree.
It is true that it would be better to use a logging package with more features - beyond a simple "on/off", but push comes to shove - I'd take a ton of dbms_output over <this space left intentionally blank>Yeah - but when push comes to shove I tend to reach for the old lead pipe and shove back. :-)
This code is called repeatedly to process a specific business entity. This lower level loop has anything from a 100 to a few 1000 iterations per call.
.. begin .. loop DBMS_OUTPUT.put_line( .. ); .. end loop; .. end;
Tany wrote:It is session based. If enabled, all DBMS_OUTPUT calls are recorded by DBMS_OUTPUT for that session. Until it is again disabled.
If I enable the dbms_output package, then will it be enabled for a particular session or for the whole system until the database is restarted?
The SERVEROUTPUT command (of sqlplus) calls the DBMS_OUTPUT package to enable and disable it.
SQL> --// enable DBMS_OUTPUT SQL> set serveroutput on SQL> exec FooProc123 ...output is displayed by sqlplus after the database call completes... SQL> SQL> --// disable DBMS_OUTPUT SQL> set serveroutput off SQL> exec FooProc123 ...no dbms_output is displayed...