43
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.

This chapter discusses the following topics:

• Security, Errors, and Types for DBMS_OUTPUT
• Using DBMS_OUTPUT
• Summary of DBMS_OUTPUT Subprograms

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 43-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 nonzero. 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).