DBMS_OUTPUT.PUT_LINE

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.


PUT_LINE can make a useful debugging tool. 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.

DISABLE
Disable DBMS_OUTPUT and reset the buffer size to the default	dbms_output.disable;
	exec dbms_output.disable;

ENABLE
Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000	dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
	exec dbms_output.enable(1000000);

Some errors:

A) overfilling your buffer

sqlplus> set serveroutput on

sqlplus>Execute DBMS_OUTPUT.ENABLE(2000);
PL/SQL procedure successfully completed.

sqlplus>BEGIN
2 FOR i IN 1..1000 LOOP
3 DBMS_OUTPUT.PUT_LINE('This is line ' || i);
4 END LOOP;
5 END;
6 /

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 158
ORA-06512: at "SYS.DBMS_OUTPUT", line 121
ORA-06512: at line 4

Solution: Increase the size of your buffer, using ENABLE. The maximum size is 1000000 and that is a hard limit.

exec dbms_output.enable(1000000);