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
This section contains topics which relate to using the DBMS_OUTPUT package.
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.
If you do not call GET_LINE, or if you do not display the messages on your screen in ttIsql, the buffered messages are ignored.
The ttIsql utility calls GET_LINES after issuing a SQL statement or anonymous PL/SQL calls.
Typing SET SERVEROUTPUT ON in ttIsql 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 usingDBMS_OUTPUT are not actually sent until the sending subprogram completes. There is no mechanism to flush output during the execution of a procedure.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.
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 |
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.
The DBMS_OUTPUT package declares two table types for use with the GET_LINES procedure.
Notes:
The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_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 and NUMBER(38) data types are also identical. This document uses INTEGER throughout.
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.
TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
This package type is to be used with the GET_LINES procedure to obtain text submitted through the PUT procedure and PUT_LINE procedure.
TYPE DBMSOUTPUT_LINESARRAY IS
VARRAY(2147483647) OF VARCHAR2(32767);
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 line in the buffer. |
|
|
Places partial line in buffer. |
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.
DBMS_OUTPUT.DISABLE;
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.
DBMS_OUTPUT.ENABLE (
buffer_size IN INTEGER DEFAULT 20000);
Table 4-3 ENABLE procedure parameters
| Parameter | Description |
|---|---|
|
|
Upper limit, in bytes, for the amount of buffered information Setting |
It is not necessary to call this procedure when you use SET SERVEROUTPUT ON from ttIsql. It is called automatically (with NULL value for buffer_size in the current release).
If there are multiple calls to ENABLE, then buffer_size is the last of the values specified. The maximum size is 1,000,000 and the minimum is 2000 when the user specifies buffer_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.
This procedure retrieves a single line of buffered information.
DBMS_OUTPUT.GET_LINE ( line OUT VARCHAR2, status OUT INTEGER);
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. |
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, call GET_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 special SET SERVEROUTPUT ON command.
After calling GET_LINE or GET_LINES, any lines not retrieved before the next call to PUT, PUT_LINE, or NEW_LINE are discarded to avoid confusing them with the next message.
This procedure retrieves an array of lines from the buffer.
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);
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. |
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, call GET_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 special SET SERVEROUTPUT ON command.
After GET_LINE or GET_LINES is called, any lines not retrieved before the next call to PUT, PUT_LINE, or NEW_LINE are discarded to avoid confusing them with the next message.
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.
DBMS_OUTPUT.NEW_LINE;
This procedure places a partial line in the buffer.
Note:
ThePUT version that takes a NUMBER input is obsolete. It is supported for legacy reasons only.
DBMS_OUTPUT.PUT (
a IN VARCHAR2);
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 calling PUT_LINE.
When you call PUT_LINE, the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, you must add your own end-of-line marker by calling NEW_LINE. GET_LINE and GET_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 or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.
Table 4-7 PUT procedure exceptions
| Exception | Description |
|---|---|
|
|
Buffer overflow, according to the |
|
|
Line length overflow, limit of 32767 bytes for each line |
This procedure places a line in the buffer.
Note:
ThePUT_LINE version that takes a NUMBER input is obsolete. It is supported for legacy reasons only.
DBMS_OUTPUT.PUT_LINE (
a IN VARCHAR2);
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 calling PUT_LINE.
When you call PUT_LINE, the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, then you must add your own end-of-line marker by calling NEW_LINE. GET_LINE and GET_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 or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.
Table 4-9 PUT_LINE procedure exceptions
| Exception | Description |
|---|---|
|
|
Buffer overflow, according to the |
|
|
Line length overflow, limit of 32767 bytes for each line |