The DBMS_OUTPUT.PUT procedure is part of Oracle’s DBMS_OUTPUT package, which is commonly used for debugging, logging, and displaying information from PL/SQL blocks, procedures, functions, packages, and triggers. The DBMS_OUTPUT package provides a way for the PL/SQL environment to communicate with the client application, typically SQL*Plus or another Oracle database tool that supports server output.
The PUT procedure specifically is used to place a string (or part of a string) into the buffer that DBMS_OUTPUT maintains for each session. Unlike DBMS_OUTPUT.PUT_LINE, which adds a string to the buffer and then appends a line terminator (effectively moving the cursor to the next line), DBMS_OUTPUT.PUT does not add a line terminator after the string. This allows multiple calls to PUT to append strings together on the same line until a line terminator is explicitly added (for example, by calling DBMS_OUTPUT.PUT_LINE with no arguments or by including a line terminator in a string).
Syntax
The basic syntax of DBMS_OUTPUT.PUT is:
DBMS_OUTPUT.PUT(line IN VARCHAR2);
line: The string to be placed into the output buffer. If the string exceeds the line buffer size (which, by default, is 32767 bytes from Oracle Database 12c onward), it will be truncated.
Usage
To use DBMS_OUTPUT.PUT, you first need to ensure that the output buffer is enabled. In SQL*Plus or Oracle SQL Developer, you can do this by running the command:
SET SERVEROUTPUT ON
This command enables the display of messages from DBMS_OUTPUT in the session.
Here is a simple example of how to use DBMS_OUTPUT.PUT in a PL/SQL block:
BEGIN
DBMS_OUTPUT.PUT('Hello, ');
DBMS_OUTPUT.PUT('World!');
DBMS_OUTPUT.NEW_LINE; -- This will move the cursor to the next line
END;
In this example, the two strings “Hello, ” and “World!” will be concatenated on the same line because we used DBMS_OUTPUT.PUT for both, and a newline character is added at the end by calling DBMS_OUTPUT.NEW_LINE.
Considerations
The DBMS_OUTPUT buffer has a limit on how much data it can hold, which by default is 20000 bytes but can be increased up to 1000000 bytes in Oracle 12c and later versions using the DBMS_OUTPUT.ENABLE procedure.
If the buffer overflows, older messages will be discarded.
DBMS_OUTPUT.PUT is primarily useful for debugging and simple outputs. For more complex logging or output requirements, consider using other mechanisms such as writing to a file or a logging table.
The visibility of output generated by DBMS_OUTPUT.PUT depends on the client tool being used and whether it supports displaying DBMS_OUTPUT messages. Always ensure that SERVEROUTPUT is set to ON in the client tool.
In summary, DBMS_OUTPUT.PUT is a convenient way to output messages from PL/SQL blocks for debugging or informational purposes, but it’s important to be mindful of its limitations and the settings of your SQL client tool.
