OracleBlog

DBMS_OUTPUT.PUT_LINE allows you to write information to a buffer throughout the execution of a trigger/procedure. That information is available to be read by a trigger/procedure (using GET_LINE(S)), or dumped to SQL*Plus upon completion of execution.

One of the most common misconceptions is that PUT_LINE writes data immediately to SQL*Plus. That is not true. PUT_LINE only puts it in the buffer. You will not see it before the block has executed. I can prove that with this example (note: you must load the user_lock package for this):

scott@Robert> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Going to sleep for 10 seconds...');
3 USER_LOCK.SLEEP(1000);
4 DBMS_OUTPUT.PUT_LINE('Woke up after 10 seconds.');
5 END;
6 /
Going to sleep for 10 seconds...
Woke up after 10 seconds.

You will have seen both messages come out after 10 seconds as opposed to one before and one after.

Despite the fact that it doesn't write messages throughout its progress, PUT_LINE can still make a useful debugging tool. I like the way that the messages can be kept but easily disabled by using DBMS_OUTPUT.DISABLE. Any PUT_LINE messages are silently ignored if you have DISABLEd DBMS_OUTPUT (or failed to ENABLE).

To see the messages, you need to call DBMS_OUTPUT.ENABLE. The only parameter is buffer_size, which, if NULL, will default to 20000. The buffer size can be anywhere from 2000 to 1000000.

scott@Robert> BEGIN
2 DBMS_OUTPUT.DISABLE;
3 DBMS_OUTPUT.PUT_LINE('Disabled');
4 DBMS_OUTPUT.ENABLE;
5 DBMS_OUTPUT.PUT_LINE('Enabled');
6 END;
7 /
Enabled

PL/SQL procedure successfully completed.

Incidentally, SQL*Plus's SET SERVEROUTPUT ON will call DBMS_OUTPUT.ENABLE. You can even use SIZE with that command. SET SERVEROUTPUT also includes formatting options, such as FORMAT WRAPPED, WORD_WRAPPED and TRUNCATE (along with a SET LINESIZE) to get the output the way you want it. [EDIT: Fixed Typo]

There are two common errors related to DBMS_OUTPUT.PUT_LINE. The first one is trying to put more than 255 characters per line.

scott@Robert> DECLARE
2 l_string VARCHAR2(300);
3 BEGIN
4 l_string := '1234567890';
5 l_string := l_string || l_string || l_string || l_string || l_string;
6 l_string := l_string || l_string || l_string || l_string || l_string || l_string;
7 DBMS_OUTPUT.PUT_LINE(l_string);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 7

The solution here is to use DBMS_OUTPUT.NEW_LINE to split it up into lines. 255 is a hard limit, if you really want to print a line with more than that, you can write your own package that does the same thing as DBMS_OUTPUT. That is actually a very common thing to do. Tom Kyte's has a handy one in Appendix A of "Expert One-on-One Oracle."

The second common error is overfilling your buffer.

scott@Robert> BEGIN
2 DBMS_OUTPUT.ENABLE(2000);
3 FOR i IN 1..1000 LOOP
4 DBMS_OUTPUT.PUT_LINE('This is line ' || i);
5 END LOOP;
6 END;
7 /
This is line 1

This is line 105
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 4

The solution here is increase the size of your buffer, using ENABLE. The maximum size is 1000000 and that is a hard limit. Once again, you can write your own package as a workaround.

This example also illustrated that even if you have an exception, the contents of the buffer until that point is still available.

The alternative to writing your own package is to write your messages to a table. Then you can query the table at any time to see your debug messages. DBMS_PIPE is another option to consider.

I will close with two more interesting "gotchas" for DBMS_OUTPUT.PUT_LINE.

scott@Robert> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('     What happened to my leading spaces?');
  3  END;
  4  /
What happened to my leading spaces?

This is an SQL*Plus Gotcha. Just be sure to use FORMAT WRAPPED, like so:

scott@Robert> SET SERVEROUTPUT ON FORMAT WRAPPED
scott@Robert> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('     There they are!');
  3  END;
  4  /
     There they are!

Here is the second gotcha.

scott@Robert> DECLARE
2 l_bool BOOLEAN;
3 BEGIN
4 l_bool := TRUE;
5 DBMS_OUTPUT.PUT_LINE(l_bool);
6 END;
7 /
DBMS_OUTPUT.PUT_LINE(l_bool);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

DBMS_OUTPUT.PUT_LINE is not overloaded for Booleans. The solution is to either to write your own package (as mentioned above), or convert from Boolean type to something else for the PUT_LINE call.

For more information, this package is described in (among other places) Oracle's Supplied PL/SQL Packages Reference Guide, Chapter 43:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612.pdf

And here is Dan Morgan's Quick Reference:
http://www.psoug.org/reference/dbms_output.html

// posted by Robert Vollman @ Wednesday, October 12, 2005