question about dbms_output.put_line

[PradeepPillai Pradeep]

I have some stored procedures which are running in production database as jobs. I have one dbms_output.put_line each in each of the procedures which are not in a loop. Those get displayed one time for each procedure when it ends. This is good for me to see the messages when the procedures are done when I run them individually. But when they are run as a job and as per my understanding they won’t get logged anywhere and I or nobody can see the messages written by those put_lines (or are they even written anywhere?). So my question is should I remove those put_lines from the stored procedures, are these put_lines expensive, or they get ignored by the job?
I appreciate any help on this. Thank you very much.

[Martin Vajsar]

The documentation you need is here. These features can be controlled completely using this package, you could even retrieve the messages from Java.

Basically, the DBMS_OUTPUT package must be activated in a session before the messages it generates start to be logged. I assume that overhead is negligible if they are not activated, probably just an if. If there is no procedure or command in the job that would activate the DBMS_OUTPUT package, then the messages won't be generated.

[PradeepPillai Pradeep]

That is what I thought. You have to set the serveroutput on. So when the DBA asked me to remove it, it didn't make sense to me. Does it make sense to you, if yes tell me why?
Thank you very much.

[Martin Vajsar]

Well, with serveroutput off the only overhead I can think of is performance. I think it is negligible, but in any case you can benchmark it. With numbers, the debate might lead somewhere eventually.

I personally would not remove that code. But if you really have to - PL/SQL supports conditional compilation; I'd use that.

[PradeepPillai Pradeep]

Thank you.