39
DBMS_OUTPUT

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers.

The PUT and PUT_LINE procedures in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure.

If you do not call GET_LINE, or if you do not display the messages on your screen in SQL*Plus or Enterprise Manager, then the buffered messages are ignored. The DBMS_OUTPUT package is especially useful for displaying PL/SQL debugging information.

Note:

Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.

This chapter discusses the following topics:

Security, Errors, and Types for DBMS_OUTPUT

Security

At the end of this script, a public synonym (DBMS_OUTPUT) is created and EXECUTE permission on this package is granted to public.

Errors

DBMS_OUTPUT subprograms raise the application error ORA-20000, and the output procedures can return the following errors:

Table 39-1 DBMS_OUTPUT Errors

Error	Description
ORU-10027:	Buffer overflow
ORU-10028:	Line length overflow

Types

Type CHARARR is a table type.

Using DBMS_OUTPUT

A trigger might want to print out some debugging information. To do this, the trigger would do:

DBMS_OUTPUT.PUT_LINE('I got here:'||:new.col||' is the new value');

If you have enabled the DBMS_OUTPUT package, then this PUT_LINE would be buffered, and you could, after executing the statement (presumably some INSERT, DELETE, or UPDATE that caused the trigger to fire), get the line of information back. For example:

BEGIN 
   DBMS_OUTPUT.GET_LINE(:buffer, :status); 
END;

It could then display the buffer on the screen. You repeat calls to GET_LINE until status comes back as non-zero. For better performance, you should use calls to GET_LINES which can return an array of lines.

Enterprise Manager and SQL*Plus implement a SET SERVEROUTPUT ON command to know whether to make calls to GET_LINE(S) after issuing INSERT, UPDATE, DELETE or anonymous PL/SQL calls (these are the only ones that can cause triggers or stored procedures to be executed).

Summary of DBMS_OUTPUT Subprograms

Table 39-2 DBMS_OUTPUT Package Subprograms

Subprogram	Description
"ENABLE Procedure"	Enables message output.
"DISABLE Procedure"	Disables message output.
"PUT and PUT_LINE Procedures"	PUT: Places a line in the buffer. PUT_LINE: Places partial line in buffer.
"NEW_LINE Procedure"	Terminates a line created with `PUT`.
"GET_LINE and GET_LINES Procedures"	Retrieves one line, or an array of lines, from buffer.

39 DBMS_OUTPUT