Learn About DBMS_OUTPUT Package in Oracle
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.
Join the DZone community and get the full member experience.Join For Free
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:
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:
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.
The GET_LINES procedure has an argument that is a PL/SQL table. The table type and the syntax are
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:
ENABLE and DISABLE
The syntax for the ENABLE and DISABLE calls is:
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
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
The message depends on which limit is exceeded.
Published at DZone with permission of Vinish Kapoor . See the original article here.
Opinions expressed by DZone contributors are their own.