Oracle provides the DBMS_OUTPUT package to enable you to send information from your programs to a buffer. This buffer can then be read and manipulated by another PL/SQL program or by the host environment.
DBMS_OUTPUT is most frequently used as a simple mechanism for displaying information on your screen.
• The way to write information to this buffer is by calling the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE programs.
• If you want to read from the buffer programmatically, you can use DBMS_OUTPUT.GET_LINE or DBMS_OUTPUT.GET_LINES.
Since the default setting of DBMS_OUTPUT is disabled, calls to the PUT_LINE and PUT programs are ignored and the buffer remains empty. To enable DBMS_OUTPUT, you generally execute a command in the host environment.
For example, in SQL*Plus,you can issue this command :-
SET SERVEROUTPUT ON SIZE UNLIMITED
There are two built-in procedures to choose from when you want to put information into the buffer :-
• PUT_LINE appends a newline marker after your text.
• PUT places text in the buffer without a newline marker (Explicitly need to call DBMS_OUTPUT.NEW_LINE).
If you’re using PUT alone, the output will remain in the buffer even when the call ends. In this case, call DBMS_OUTPUT.NEW_LINE to flush the buffer.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Steven');
3 DBMS_OUTPUT.PUT_LINE(100);
4 DBMS_OUTPUT.PUT_LINE(SYSDATE);
5 END;
6 /
--OUTPUT :::
Steven
100
05-OCT-21
Unfortunately, DBMS_OUTPUT does not know what to do with a variety of common PL/SQL types, most notably Booleans.
SQL> CREATE OR REPLACE PROCEDURE bpl (boolean_in IN BOOLEAN)
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(
5 CASE boolean_in
6 WHEN TRUE THEN 'TRUE'
7 WHEN FALSE THEN 'FALSE'
8 ELSE 'NULL'
9 END
10 );
11 END;
12 /
Procedure created.
SQL> EXECUTE bpl(TRUE);
TRUE
PL/SQL procedure successfully completed.
SQL> EXECUTE bpl(FALSE);
FALSE
PL/SQL procedure successfully completed.
The largest string that you can pass in one call to DBMS_OUTPUT.PUT_LINE is 32,767 bytes in the most recent releases of Oracle. With Oracle Database 10g Release 1 or earlier, the limit is 255 bytes. With any version, if you pass a value larger than the maximum allowed, the database will raise an exception (either VALUE_ERROR or ORU-10028: line length overflow, limit of NNN chars per line).
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('This Is');
3 DBMS_OUTPUT.PUT_LINE('Oracle');
4
5 DBMS_OUTPUT.PUT('This Is');
6 DBMS_OUTPUT.NEW_LINE;
7 DBMS_OUTPUT.PUT('Oracle');
8 DBMS_OUTPUT.NEW_LINE;
9 END;
10 /
--OUTPUT :::
This Is
Oracle
This Is
Oracle
SQL> DECLARE
2 lines DBMS_OUTPUT.CHARARR;
3 num_lines NUMBER;
4 BEGIN
5 -- enable the buffer with default size 20000
6 DBMS_OUTPUT.ENABLE;
7
8 DBMS_OUTPUT.PUT_LINE('Hello Reader!');
9 DBMS_OUTPUT.PUT_LINE('Hope you have enjoyed the tutorials!');
10 DBMS_OUTPUT.PUT_LINE('Have a great time exploring pl/sql!');
11
12 num_lines := 3;
13
14 DBMS_OUTPUT.GET_LINES(lines, num_lines);
15
16 FOR i IN 1..num_lines
17 LOOP
18 DBMS_OUTPUT.PUT_LINE('line#'||i);
19 DBMS_OUTPUT.PUT_LINE(lines(i));
20 END LOOP;
21 END;
22 /
--OUTPUT :::
line#1
Hello Reader!
line#2
Hope you have enjoyed the tutorials!
line#3
Have a great time exploring pl/sql!
SQL> DECLARE
2 line VARCHAR2(32767);
3 num_line NUMBER;
4 nerrorcode NUMBER;
5 BEGIN
6 -- enable the buffer with default size 20000
7 DBMS_OUTPUT.ENABLE;
8
9 DBMS_OUTPUT.PUT('Hello Reader!');
10 DBMS_OUTPUT.NEW_LINE;
11
12 DBMS_OUTPUT.GET_LINE(line,nerrorcode);
13
14 DBMS_OUTPUT.PUT_LINE(line||' - '||nerrorcode);
15 END;
16 /
--OUTPUT :::
Hello Reader! – 0
Disables message output.
Enables message output. A NULL value of buffer_size represents unlimited buffer size.
Retrieves a single line of buffered information.
Retrieves an array of lines from the buffer.
Puts an end-of-line marker.
Places a partial line in the buffer without new line marker.
Places a line in the buffer including new line marker.