DBMS_OUTPUT

DBMS_OUTPUT oracle package enables you to send output messages from various other PL/SQL program units like stored procedures, packages, triggers etc.  DBMS_OUTPUT is primarily used for displaying messages or debug information  from a PL/SQL Program. The  procedures contained in this package are DISABLE, ENABLE, PUT, PUT_LINE, GET_LINE, GET_LINES and NEW_LINE. 

DBMS_OUTPUT Procedures

DBMS_OUTPUT.DISABLE : Disable procedure disables the calls to all the procedures contained within dbms_output package, purges the buffers and resets the buffer size back to default. 

Syntax : DBMS_OUTPUT.DISABLE; 

DBMS_OUTPUT.ENABLE : Enable procedure enables calls to all the procedures in the package.  Enable also accepts the buffer size as a parameter and sets the buffer size to the bytes specified.  Value of this parameter can be between 2000 and unlimited. If no size is specified, the buffer size defaults to 20000. Enable call is not required if the serveroutput is set to on 

Syntax : DBMS_OUTPUT.ENABLE (100000); 

DBMS_OUTPUT.PUT : Puts a partial line in the buffer. The old procedure that takes a number as an input is obsolete and is only retained for backward compatibility purposes. 

Syntax : DBMS_OUTPUT.PUT( 'Hello');

DBMS_OUTPUT.PUT_LINE : Put Line procedures a line in the buffer. The difference between the operations of put and put_line primarily is the new line character added at the end. In case of put, you can construct a single line by calling multiple puts, while put line always ends the line with a new line character. 

Syntax : DBMS_OUTPUT.PUT_LINE('This is a test');

DBMS_OUTPUT.GET_LINE : Get Line procedue is used to retrieve a single line of buffered information. 

Syntax : DBMS_OUTPUT.GET_LINE(v_lines out varchar2, v_status out varchar2);

1. When executing get_line, you should pay attention to the variable passed in for the first parameter and size it at varchar2(32767) to avoid the "ORA-06502 : PL/SQL : Numeric or value error: Character string buffer too small".
2. If the call completes successfully the status returns a 0 and if there are no lines in the buffer, the status returns a 1

DBMS_OUTPUT.GET_LINES : This procedure is similar to get_line but allows you to retrieve multiple lines or an array of lines from the buffer.  

Syntax BMS_OUTPUT.GET_LINES(LINES OUT CHARARR, NUMLINES IN OUT INTEGER);

Syntax : DBMS_OUTPUT.GET_LINES(LINES OUT DBMSOUTPUT LINESARRARY, NUMLINES IN OUT INTEGER); 

• Max length of each line is 32767 bytes. Lines returns an array of lines from the buffer.
• numlines parameter can be specified to retrieve a certain number of lines from the buffer. Once the lines are retrieved this value is set to the number of lines returned. If the returned value is less than what was initially passed, then there are no more lines in the buffer. 

DBMS_OUTPUT.NEW_LINE : New line procedure puts an end-of-line marker in the buffer for the preceeding line. The get procedures return lines as demilited by the new line character.  

Syntax : DBMS_OUTPUT.NEW_LINE;

---

DBMS_OUTPUT Exceptions

• ORA-20000, ORU-10027 : Buffer overflow, limit of <limit> bytes  : Try increasing the buffer size if you have not specified any buffer size.  You can use the enable procedure to set the buffer size to your required value
• ORA-20000, ORU-10028 : Line length overflow, limit of 32767 bytes for each line : The maximum line size you can put in a buffer has been exceeded. Try reducing the size of the line or add more new line characters to the line data.

DBMS_OUTPUT Notes 

• If using oracle SQL Plus, try to set serveroutput on instead of enable;
• The maximum line size is 32767 bytes.
• Default buffer size is 20000 bytes, minimum size is 2000 and maximum is unlimited
• There is no mechanism in oracle PL/SQL to flush or display the buffers while the program is in execution.  The output does not display until the oracle PL/SQL program completes
• Any lines not retrieved between a Get and a Put will be discarded to avoid confusing with the next message.