Understanding DBMS_OUTPUT Package

external_tables

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

DBMS_OUTPUT.DISABLE

Disables message output.

DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000)

Enables message output. A NULL value of buffer_size represents unlimited buffer size.

DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER)

Retrieves a single line of buffered information.

DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER)

Retrieves an array of lines from the buffer.

DBMS_OUTPUT.NEW_LINE

Puts an end-of-line marker.

DBMS_OUTPUT.PUT(item IN VARCHAR2)

Places a partial line in the buffer without new line marker.

DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2)

Places a line in the buffer including new line marker.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *