Question:
I have a stored procedure that uses the dbms_output package to report status, but I don't see any output. Why not ?
Answer:
The dbms_output package contains procedures put_line(), put(), and new_line; and it is common practice, often for debugging purposes, to scatter these calls through a stored procedure so that you can leave a trail showing which bits of code were exercised.
There are two problems though, first is that when you run the procedure, you don't see any output until the procedure completes. The second is that you never see any output at all.
The import point to remember is that the dbms_output package is using an in-memory buffer as it's target, it not actually doing any 'real' output. If you want to see the results of dbms_output, something has to raid the buffer using the dbms_output.get_lines() procedure and then do some real output for you.
In Svrmgr, this use of dbms_output.get_lines() is triggered by the command:
set serveroutput on size 999999
In SQL*Plus, the same command will work, but the presentation rules are different and you are better off using the command:
set serveroutput on format wrapped size 1000000
If you use a simple 'set serveroutput on' in SQL*Plus, then leading spaces are trimmed, and the newline() command is ignored, so your carefully planned output easily becomes unreadable.
The 'size {number}' command limits the buffer available to your session. The size can be between 2,000 and 1,000,000 bytes (watch out for the side-effect of multi-byte character sets) although SQL*Plus and svrmgr disagree on whether you can actually use the value 1,000,000.
The default value is 2,000, so I tend to use the glogin.sql feature to set it to the maximum possible value for SQL*Plus. Be careful, if you have a procedure that uses dbms_output, then it is a fatal error if you try to push too much data into the buffer.Your procedure will fail with the error:
ORA-20000: ORU-10027: buffer overflow, limit of XXXXXX bytes
There are a couple of oddities about when you see the output. For example, dbms_output doesn't seem to work from within the procedures used for generating the predicates for use by 'fine grained access control'. Perhaps more commonly seen is the little quirk that if you use dbms_output in a trigger, but the trigger fails and raises an exception, you won't see the output, but it will be in the buffer waiting for the next opportunity to be displayed - this can cause terrible confusion when you are trying to use dbms_output to figure out where your code is failing, and is a good reason for using either a 'pipe' method (which I will write about eventually) or making a minor change to your system to use utl_file instead of dbms_output.