4 DBMS_OUTPUT
The DBMS_OUTPUT
package enables you to send messages from stored procedures and packages. The package is especially useful for displaying PL/SQL debugging information.
This chapter contains the following topics:
-
-
Overview
-
Operational notes
-
Rules and limits
-
Exceptions
-
Examples
-
-
-
Table types
-
Using DBMS_OUTPUT
This section contains topics which relate to using the DBMS_OUTPUT
package.
Overview
The PUT Procedure and PUT_LINE Procedure in this package enable you to place information in a buffer that can be read by another procedure or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE Procedure and GET_LINES Procedure.
If the package is disabled, all calls to subprograms are ignored. In this way, you can design your application so that subprograms are available only when a client can process the information.
Operational Notes
-
If you do not call
GET_LINE
, or if you do not display the messages on your screen inttIsql
, the buffered messages are ignored. -
The
ttIsql
utility callsGET_LINES
after issuing a SQL statement or anonymous PL/SQL calls. -
Typing
SET
SERVEROUTPUT
ON
inttIsql
has the same effect as the following:DBMS_OUTPUT.ENABLE (buffer_size => NULL);
There is no limit on the output.
-
You should generally avoid having application code invoke either the DISABLE Procedure or ENABLE Procedure because this could subvert the attempt by an external tool like
ttIsql
to control whether to display output.
Note:
Messages sent using DBMS_OUTPUT
are not actually sent until the sending subprogram completes. There is no mechanism to flush output during the execution of a procedure.
Rules and Limits
-
The maximum line size is 32767 bytes.
-
The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.
Exceptions
DBMS_OUTPUT
subprograms raise the application error ORA-20000
, and the output procedures can return the following errors:
Table 4-1 DBMS_OUTPUT Exceptions
Exception | Description |
---|---|
|
Buffer overflow |
|
Line length overflow |
Examples
The DBMS_OUTPUT
package is commonly used to debug stored procedures or functions.
This function queries the employees
table of the HR
schema and returns the total salary for a specified department. The function includes calls to the PUT_LINE
procedure:
CREATE OR REPLACE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
CURSOR emp_cursor IS
select salary, commission_pct from employees where department_id = dnum;
total_wages NUMBER(11, 2) := 0;
counter NUMBER(10) := 1;
BEGIN
FOR emp_record IN emp_cursor LOOP
emp_record.commission_pct := NVL(emp_record.commission_pct, 0);
total_wages := total_wages + emp_record.salary
+ emp_record.commission_pct;
DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
'; Wages = '|| TO_CHAR(total_wages)); /* Debug line */
counter := counter + 1; /* Increment debug counter */
END LOOP;
/* Debug line */
DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
TO_CHAR(total_wages));
RETURN total_wages;
END;
Assume the user executes the following statements in ttIsql
:
Command> SET SERVEROUTPUT ON
Command> VARIABLE salary NUMBER;
Command> EXECUTE :salary := dept_salary(20);
The user would then see output such as the following:
Loop number = 1; Wages = 13000
Loop number = 2; Wages = 19000
Total wages = 19000
PL/SQL procedure successfully executed.
Data Structures
The DBMS_OUTPUT
package declares two table types for use with the GET_LINES Procedure.
Note:
-
The
PLS_INTEGER
andBINARY_INTEGER
data types are identical. This document usesBINARY_INTEGER
to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples. -
The
INTEGER
andNUMBER(38)
data types are also identical. This document usesINTEGER
throughout.
CHARARR Table Type
This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure.
Syntax
TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
DBMSOUTPUT_LINESARRAY Table Type
This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure.
Syntax
TYPE DBMSOUTPUT_LINESARRAY IS
VARRAY(2147483647) OF VARCHAR2(32767);
DBMS_OUTPUT Subprograms
Table 4-2 summarizes the DBMS_OUTPUT
subprograms, followed by a full description of each subprogram.
Table 4-2 DBMS_OUTPUT Package Subprograms
Subprogram | Description |
---|---|
Disables message output. |
|
Enables message output. |
|
Retrieves one line from buffer. |
|
Retrieves an array of lines from buffer. |
|
Terminates a line created with |
|
Places a partial line in the buffer. |
|
Places a line in the buffer. |
DISABLE Procedure
This procedure disables calls to PUT
, PUT_LINE
, NEW_LINE
, GET_LINE
, and GET_LINES
, and purges the buffer of any remaining information.
As with the ENABLE Procedure, you do not need to call this procedure if you are using the SET SERVEROUTPUT ON
setting from ttIsql
.
Syntax
DBMS_OUTPUT.DISABLE;
ENABLE Procedure
This procedure enables calls to PUT
, PUT_LINE
, NEW_LINE
, GET_LINE
, and GET_LINES
. Calls to these procedures are ignored if the DBMS_OUTPUT
package is not activated.
Syntax
DBMS_OUTPUT.ENABLE (
buffer_size IN INTEGER DEFAULT 20000);
Parameters
Table 4-3 ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
Upper limit, in bytes, for the amount of buffered information Setting |
Usage Notes
-
It is not necessary to call this procedure when you use
SET SERVEROUTPUT ON
fromttIsql
. It is called automatically (withNULL
value forbuffer_size
in the current release). -
If there are multiple calls to
ENABLE
, thenbuffer_size
is the last of the values specified. The maximum size is 1,000,000 and the minimum is 2000 when the user specifiesbuffer_size
(NOT
NULL
). -
NULL
is expected to be the usual choice. The default is 20000 for backward compatibility with earlier database versions that did not support unlimited buffering.
GET_LINE Procedure
This procedure retrieves a single line of buffered information.
Syntax
DBMS_OUTPUT.GET_LINE (
line OUT VARCHAR2,
status OUT INTEGER);
Parameters
Table 4-4 GET_LINE Procedure Parameters
Parameter | Description |
---|---|
|
A single line of buffered information, excluding a final newline character You should declare this parameter as |
|
Call status If the call completes successfully, then the status returns as 0. If there are no more lines in the buffer, then the status is 1. |
Usage Notes
-
You can choose to retrieve from the buffer a single line or an array of lines. Call
GET_LINE
to retrieve a single line of buffered information. To reduce the number of calls to the server, callGET_LINES
to retrieve an array of lines from the buffer. -
You can choose to automatically display this information if you are using
ttIsql
by using the specialSET
SERVEROUTPUT
ON
command. -
After calling
GET_LINE
orGET_LINES
, any lines not retrieved before the next call toPUT
,PUT_LINE
, orNEW_LINE
are discarded to avoid confusing them with the next message.
GET_LINES Procedure
This procedure retrieves an array of lines from the buffer.
Syntax
DBMS_OUTPUT.GET_LINES (
lines OUT DBMS_OUTPUT.CHARARR,
numlines IN OUT INTEGER);
DBMS_OUTPUT.GET_LINES (
lines OUT DBMS_OUTPUT.DBMSOUTPUT_LINESARRAY,
numlines IN OUT INTEGER);
Parameters
Table 4-5 GET_LINES Procedure Parameters
Parameter | Description |
---|---|
|
Array of lines of buffered information The maximum length of each line in the array is 32767 bytes. It is recommended that you use the varray overload version in a 3GL host program to execute the procedure from a PL/SQL anonymous block. |
|
Number of lines you want to retrieve from the buffer After retrieving the specified number of lines, the procedure returns the number of lines actually retrieved. If this number is less than the number of lines requested, then there are no more lines in the buffer. |
Usage Notes
-
You can choose to retrieve from the buffer a single line or an array of lines. Call
GET_LINE
to retrieve a single line of buffered information. To reduce the number of trips to the server, callGET_LINES
to retrieve an array of lines from the buffer. -
You can choose to automatically display this information if you are using
ttIsql
by using the specialSET
SERVEROUTPUT
ON
command. -
After
GET_LINE
orGET_LINES
is called, any lines not retrieved before the next call toPUT
,PUT_LINE
, orNEW_LINE
are discarded to avoid confusing them with the next message.
NEW_LINE Procedure
This procedure puts an end-of-line marker.
The GET_LINE Procedure and the GET_LINES Procedure return "lines" as delimited by "newlines". Every call to the PUT_LINE Procedure or to NEW_LINE
generates a line that is returned by GET_LINE
or GET_LINES
.
Syntax
DBMS_OUTPUT.NEW_LINE;
PUT Procedure
This procedure places a partial line in the buffer.
Note:
The PUT
version that takes a NUMBER
input is obsolete. It is supported for legacy reasons only.
Syntax
DBMS_OUTPUT.PUT (
a IN VARCHAR2);
Parameters
Table 4-6 PUT Procedure Parameters
Parameter | Description |
---|---|
|
Item to buffer |
Usage Notes
-
You can build a line of information piece by piece by making multiple calls to
PUT
, or place an entire line of information into the buffer by callingPUT_LINE
. -
When you call
PUT_LINE
, the item you specify is automatically followed by an end-of-line marker. If you make calls toPUT
to build a line, you must add your own end-of-line marker by callingNEW_LINE
.GET_LINE
andGET_LINES
do not return lines that have not been terminated with a newline character. -
If your lines exceed the line limit, you receive an error message.
-
Output that you create using
PUT
orPUT_LINE
is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.
Exceptions
Table 4-7 PUT Procedure Exceptions
Exception | Description |
---|---|
|
Buffer overflow, according to the |
|
Line length overflow, limit of 32767 bytes for each line |
PUT_LINE Procedure
This procedure places a line in the buffer.
Note:
The PUT_LINE
version that takes a NUMBER
input is obsolete. It is supported for legacy reasons only.
Syntax
DBMS_OUTPUT.PUT_LINE (
a IN VARCHAR2);
Parameters
Table 4-8 PUT_LINE Procedure Parameters
Parameter | Description |
---|---|
|
Item to buffer |
Usage Notes
-
You can build a line of information piece by piece by making multiple calls to
PUT
, or place an entire line of information into the buffer by callingPUT_LINE
. -
When you call
PUT_LINE
, the item you specify is automatically followed by an end-of-line marker. If you make calls toPUT
to build a line, then you must add your own end-of-line marker by callingNEW_LINE
.GET_LINE
andGET_LINES
do not return lines that have not been terminated with a newline character. -
If your lines exceeds the line limit, you receive an error message.
-
Output that you create using
PUT
orPUT_LINE
is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.
Exceptions
Table 4-9 PUT_LINE Procedure Exceptions
Exception | Description |
---|---|
|
Buffer overflow, according to the |
|
Line length overflow, limit of 32767 bytes for each line |