Why can't I see the output from dbms_output ?

The Oracle (tm) Users' Co-Operative FAQ

Why can't I see the output from dbms_output ?

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 seem to 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 (see Further Reading below) 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 or making a minor change to your system to use utl_file instead of dbms_output.

Further reading: How do I set up an 'environment' for SQL*Plus by Jonathan Lewis - a client-side script that runs whenever you log on to SQL*Plus.