DBMS_OUTPUT package

The DBMS_OUTPUT package give you the possibility to send messages from pl/sql. An option that is used mostly is to enable the DBMS_OUTPUT package and show the contents of the message-buffer in the screen. In SQL*PLUS this can be done with the command: 'SET SERVEROUTPUT ON' or 'SET SERVEROUT ON' In tools like TOAD you can enable the output with a button.

subprograms

dbms_output.enable

With dbms_output.enable you can enable the subprograms PUT, PUT_LINE, GET_LINE, GET_LINES and NEW_LINE. If the dbms_output package is not enabled these subprograms will ignore the call. If the command 'SET SERVEROUT ON' is called, the package will automatically be enabled.
syntax
dbms_output.enable; or
dbms_output.enable(buffer_size); (Up to Oracle 10g the buffersize os an integer between 2,000 and 1,000,000; From Oracle 10g Release 2 the buffersize is unlimited)

dbms_output.disable

With dmbs_output.disable you disable the subprograms PUT, PUT_LINE, GET_LINE, GET_LINES and NEW_LINE. A call to these subprograms will be ignored and the buffer is purged.

syntax
dbms_output.disable;

dbms_output.put and dbms_output.put_line

The dbms_output.put_line or dbms_output.put procedures add the passed parameter to the buffer. These procedures are overloaded and can be called with a VARCHAR2, DATE or NUMBER parameter. These parameters will be implicitely converted to VARCHAR2 before they are put on the buffer. The put_line procedure automatically adds and end-of-line to the buffer. If the line is bigger than the buffer limit, you will receive an error message.

syntax
dbms_output.put(parameter); (with parameter a VARCHAR2, DATE or NUMBER value) dbms_output.put_line(parameter); (with parameter a VARCHAR2, DATE or NUMBER value)

dbms_output.new_line

The dbms_output.new_line procedure adds and end-of-line to the buffer.

syntax
dbms_output.new_line;

dbms_output.get_line and dbms_output.get_lines

The dbms_output.get_line and dbms_output.get_lines procedures get 1 or more lines from the buffer. The get_line procedure gets 1 line and the get_lines procedure gets a array of lines from the buffer.

syntax
dbms_output.get_line(p_line,p_status);
with p_line the returned line-value and status an integer value. Up to Oracle 10gR2 the maximum lenght of p_line is 255 bytes. From Oracle 10gR2 the line length limit is 32K (32767). If p_status returns 0, the call is completed succesfully.
If there are no more lines in the buffer, p_status will return 1.

dbms_output.get_lines(p_lines, p_numlines)
where p_lines has up to Oracle 10gR2 datatype CHARARR (table of VARCHAR2(255)). And from Oracle 10gR2 the datatype is DBMSOUTPUT_LINESARRAY (VARRAY(2147483647) OF VARCHAR2(32767)) And p_numlines is an IN/OUT parameter with datatype INTEGER representing the number of lines you want to get from the buffer. The returning value of p_numlines is the number of lines actually retrieved.