DBMS_OUTPUT Package

DBMS_OUTPUT is a Oracle built in package that lets you send messages from stored procedures, packages and triggers. The package is helpful during debugging or displaying PL/SQL code. The most frequently used procedure within this package is PUT_LINE. There are other procedures like GET_LINE, NEW_LINE, PUT etc inside the package. The other procedures are not as common as PUT_LINE but worth checking and knowing them.

Syntax:
DBMS_OUTPUT.PUT_LINE('This is an example!');

Example: 1

SET serveroutput ON
BEGIN
 DBMS_OUTPUT.PUT_LINE('This is an example!') ;
END;
/
Output:
PL/SQL procedure successfully completed.

This is an example!

Example: 2

SET serveroutput ON

BEGIN
 DBMS_OUTPUT.PUT_LINE('This is an example' || ' of a PUT_LINE procedure.') ;
END;
/

PL/SQL procedure successfully completed.

This is an example of a PUT_LINE procedure.

Example: 3

SET serveroutput ON

DECLARE
procedure_name varchar2(50) := 'PUT_LINE';
BEGIN
 DBMS_OUTPUT.PUT_LINE('This is an example of ' || procedure_name ||' procedure.') ;
END;
/

PL/SQL procedure successfully completed.

This is an example of PUT_LINE procedure.

Similarly, PUT_LINE accepts any columns from the table not just declared variable or hard-coded message.

What is SET serveroutput ON/OFF?

SET serveroutput OFF invokes
DBMS_OUTPUT.DISABLE (buffer_size => NULL);

The DISABLE procedure purges the buffer of any remaining information from PUT, NEW_LINE, GET_LINE, PUT_LINE, and all the procedures under DBMS_OUTPUT packages. The DBMS_OUTPUT is disabled by default therefore you are required to turn on for each time you are logged in.

SET serveroutput ON invokes
DBMS_OUTPUT.ENABLE (buffer_size => NULL);

The ENABLE procedure under the the DBMS_OUTPUT  package. PUT, NEW_LINE, GET_LINE, PUT_LINE etc. PUT_LINE procedure displays the output message only when the procures are enabled.

Oracle 10g Release and prior version were restricted with 255 byte limit. Oracle later increased the line limit to 32767 bytes which I have not tried.

PUT_LINE procedure is very helpful while debugging or printing message. I would never leave the DBMS_OUTPUT package inside the production code. The best practice to use it and remove it before deploying the code.