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
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
How does DBMS_OUTPUT work in Oracle?
Two basic operations,
PUT, are implemented through procedures in the package. A
PUT operation takes its argument and places it into an internal buffer for storage.
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
PUT routines in the package are
GET routines are
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_LINE, the buffer can contain values of types
DATE are stored in the buffer in their original format. However,
GET_LINES retrieve from the buffer and return Character strings only.
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
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 The syntax for the NEW_LINE call is:
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.
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
DATEvalues, for example.
These take up 7 bytes of storage in the buffer but are usually converted to Character strings with Lengths greater than 7.
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.
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
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_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.
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_LINE do not affect.
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
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
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.
DBMS_OUTPUTis 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
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.NEW_LINE can raise either
ORA-20000: ORU-10027: buffer overflow, limit of <buf_limit> bytes.
ORA-20000: ORU-10028 line lenth overflow, limit of 255 bytes per line.
The message depends on which limit is exceeded.