Learn About DBMS_OUTPUT Package in Oracle

In this article, we will examine the DBMS_OUTPUT package in some detail. DBMS_OUTPUT package in Oracle, like other DBMS packages, is owned by the Oracle user SYS.

The script which creates DBMS_OUTPUT grants the EXECUTE permission on the package to the PUBLIC, and creates a public synonym for it. This means that any Oracle user can call the routines in DBMS_OUTPUT without having to prefix the package name with SYS.

How does DBMS_OUTPUT work in Oracle?

Two basic operations, GET and PUT, are implemented through procedures in the package. A PUT operation takes its argument and places it into an internal buffer for storage.

A GET operation reads from this buffer and returns the contents as an argument to the procedure. There is also an ENABLE procedure that sets the size of the buffer.

Procedures in DBMS_OUTPUT Package

The PUT routines in the package are PUT, PUT_LINE, and NEW_LINE. The GET routines are GET_LINE and GET_LINES. ENABLE and DISABLE control of the buffer.

PUT and PUT_LINE The syntax for the PUT and PUT_LINE calls are:

PROCEDURE PUT(a VARCHAR2);
PROCEDURE PUT(a NUMBER);
PROCEDURE PUT(a DATE);
PROCEDURE PUT(a VARCHAR2);
PROCEDURE PUT(a NUMBER);
PROCEDURE PUT(a DATE);

Where a is the argument to be placed in the buffer. Note that the type of the parameter overloads these procedures. Because of the three different versions of PUT and PUT_LINE, the buffer can contain values of types VARCHAR2, NUMBER, and DATE are stored in the buffer in their original format. However, GET_LINE and GET_LINES retrieve from the buffer and return Character strings only.

When a GET operation is performed, the contents of the buffer will be converted to a Character string according to the default datatype conversion rules. If you want to specify a format for the conversion, use an explicit TO_CHAR call on the PUT, rather than the GET.

The buffer is organized into lines, each of which can have a maximum of 255 bytes. PUT_LINE appends a newline Character after its argument, signaling the end of a line. PUT does not. PUT_LINE is equivalent to calling PUT and then calling NEW_LINE.

NEW_LINE The syntax for the NEW_LINE call is:

PROCEDURE NEW_LINE;

NEW_LINE puts a newline Character into the buffer, signaling the end of a line. There is no limit to the number of lines in the buffer. The total size of the buffer is limited to the value specified in ENABLE, however.

GET_LINE The syntax for GET_LINE is:

PROCEDURE GET_LINE(line OUT VARCHAR2, status OUT INTEGER);

Where the line is a Character string that will contain one line of the buffer, and status indicates whether the line was retrieved successfully. The maximum length of a line is 255 bytes. If the line was retrieved, the status would be 0; if there are no more lines in the buffer, it will be 1.

NOTE

Although the maximum size of a buffer line is 255 bytes, the output variable line can be more than 255 Characters. The buffer line can consist of DATE values, for example. 

These take up 7 bytes of storage in the buffer but are usually converted to Character strings with Lengths greater than 7.

GET_LINES

The GET_LINES procedure has an argument that is a PL/SQL table. The table type and the syntax are

TYPE CHARARR IS TABLE OF VERCHAR2(255)
INDEX BY BINARY_INTEGER;

PROCEDURE GET_LINES(lines OUT CHARARR,
numlines IN OUT INTEGER);

Where lines is a PL/SQL table will contain multiple lines from the buffer, and numlines indicates how many lines are requested. On input to GET_LINES, numlines specifies the requested number of lines. On Output, numlines will contain the actual number of lines returned, Which will be less than or equal to the number requested. GET_LINES is designed to replace multiple calls to GET_LINES.

The CHARARR type is also defined in the DBMS_OUTPUT package. Therefore, if you want to call GET_LINES explicitly in your code, you need to declare a variable of type DBMS_OUTPUT. CHARARR. For example:

Create Table temp_table (char_col varchar2(4000))
/
DECLARE

/* Demonstrates using PUT_LINE and GET_LINE. */

v_Data DBMS_OUTPUT.CHARARR;

v_NumLines NUMBER;

BEGIN

-- Enable the buffer first.

DBMS_OUTPUT.ENABLE(1000000);

-- Put some data in the buffer first, so GET_LINES will

-- retrieve something.

DBMS_OUTPUT.PUT_LINE( 'Line' );

DBMS_OUTPUT.PUT_LINE( 'Line Two' );

DBMS_OUTPUT.PUT_LINE( 'Line Three' );

--Set the maximum number of lines that we want to retrieve.

v_NumLines := 3 ;

/* Get the contents of the buffer back. Note that v_DATA is

declared of type DBMS_OUTPUT. CHARARR, so that it matches

the declaration of DBMS_OUTPUT. GET_LINES. */

DBMS_OUTPUT.GET_LINES(v_DATA, v_NumLines);

/* Loop through the returned buffer, and insert the contents

into tem_table. */

For v_Counter IN 1.. v_NumLines Loop

INSERT INTO temp_table ( char_col )

VALUES ( v_Data ( v_Counter ));

END LOOP;

END;
/

ENABLE and DISABLE

The syntax for the ENABLE and DISABLE calls is:

PROCEDURE ENABLE (buffer_size IN INTEGER DEFAULT 20000);

PROCEDURE DISABLE;

Where buffer_size is the initial size of the internal buffer, in bytes. The default size is 20,000 bytes, and the maximum size is 1,000,000 bytes.

Later, arguments to PUT or PUT_LINE will be placed in this buffer. They are stored in their internal format, taking up as much space in the buffer as their structure dictates.

If DISABLE is called, the contents of the buffer are their structure dictates. If DISABLE is called, the contents of the buffer are purged, and subsequent calls to PUT and PUT_LINE do not affect.

Using DBMS_OUTPUT

The DBMS_OUTPUT package itself does not contain any mechanism for printing. Essentially, it implements merely a first in, first out data structure.

Having said that, how can we use DBMS_OUTPUT for printing? SQL*Plus, SQL*DBA, and Manager ll have an option known as SERVEROUTPUT. In addition, some third-party products (SQL-Station included) have an option that allows the display of DBMS_OUTPUT data.

With this option, SQL*Plus will automatically call DBMS_OUTPUT.GET_LINES when a PL/SQL block concludes and prints the results, if any, to the screen.

The SQL*Plus command SET SERVEROUTPUT ON implicitly calls, which sets up the internal buffer. Optionally, you can specify size with SET SERVEROUTPUT ON SIZE buffer_size where beffer_size will be used as the initial size of the buffer (the argument to DBMS_OUTPUT.ENABLE).

With SERVEROUTPUT on, SQL*Plus will call DBMS_OUTPUT.GET_LINES after the PL/SQL block has completed. This means that the output will be echoed to the screen when the block has finished and not during execution of the block. This usually is not a problem when  DBMS_OUTPUT is used for debugging.

CAUTION

DBMS_OUTPUT is designed to be used primarily for debbuging. It is not meant for general reporting. If you need to customize the output from your queries, it is better to use tools such as Oracle Reports than DBMS_OUTPUT and SQL*Plus.

SET serveroutput on SIZE 1000000
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before loop');
    FOR v_Counter IN 1..10 LOOP
      DBMS_OUTPUT.PUT_LINE('Inside loop, couter = ' || v_Counter);
     END LOOP;
     DBMS_OUPUT.PUT_LINE( 'After loop');
END;
/

The internal buffer does have a maximum size (specified in DBMS_OUTPUT.ENABLE), and each line has a maximum length of 255 bytes. As a result, calls to DBMS_OUTPUT.PUT, DBMS_OUTPUT.PUT_LINE, and DBMS_OUTPUT.NEW_LINE can raise either

ORA-20000: ORU-10027: buffer overflow,
           limit of <buf_limit> bytes.

Or

ORA-20000: ORU-10028 line lenth overflow,
           limit of 255 bytes per line.

The message depends on which limit is exceeded.

See also:

• How to Enable DBMS_OUTPUT in Toad for Oracle?
• Log DBMS_OUTPUT.Put_Line Output Into Table In Oracle With DBMS_OUTPUT.Get_Lines