Oracle8i Supplied Packages Reference
Release 8.1.5
A68001-01

Library
Product
Contents
Index

Prev Next

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

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 26-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 Subprograms

Table 26-2 DBMS_OUTPUT Package Subprograms
Subprogram  Description  
ENABLE procedure
 

Enables message output.  

 
DISABLE procedure
 

Disables message output.  

 
PUT and PUT_LINE procedures
 

Places a line in the buffer.  

 
PUT and PUT_LINE procedures
 

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.  

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

Note:

It is not necessary to call this procedure when you use the SERVEROUTPUT option of Enterprise Manager or SQL*Plus.  

If there are multiple calls to ENABLE, then buffer_size is the largest of the values specified. The maximum size is 1,000,000, and the minimum is 2,000.

Syntax

DBMS_OUTPUT.ENABLE (
   buffer_size IN INTEGER DEFAULT 20000);

Parameters

Table 26-3 ENABLE Procedure Parameters
Parameter  Description  
buffer_size
 

Amount of information, in bytes, to buffer.  

Pragmas

pragma restrict_references(enable,WNDS,RNDS);

Errors

Table 26-4 ENABLE Procedure Errors
Error  Description  
ORA-20000:, 
ORU-10027:
 

Buffer overflow, limit of <buffer_limit> bytes.  

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 ENABLE, you do not need to call this procedure if you are using the SERVEROUTPUT option of Enterprise Manager or SQL*Plus.

Syntax

DBMS_OUTPUT.DISABLE;

Parameters

None.

Pragmas

pragma restrict_references(disable,WNDS,RNDS);

PUT and PUT_LINE procedures

You can either place an entire line of information into the buffer by calling PUT_LINE, or you can build a line of information piece by piece by making multiple calls to PUT. Both of these procedures are overloaded to accept items of type VARCHAR2, NUMBER, or DATE to place in the buffer.

All items are converted to VARCHAR2 as they are retrieved. If you pass an item of type NUMBER or DATE, then when that item is retrieved, it is formatted with TO_CHAR using the default format. If you want to use a different format, then you should pass in the item as VARCHAR2 and format it explicitly.

When you call PUT_LINE, the item that 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 line exceeds the buffer limit, then you receive an error message.

Note:

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.

For example, Enterprise Manager or SQL*Plus do not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program. For example: 

SQL> SET SERVER OUTPUT ON
SQL> BEGIN
     2 DBMS_OUTPUT.PUT_LINE ('hello');
     3 DBMS_LOCK.SLEEP (10);
     4 END;
 

Syntax

DBMS_OUTPUT.PUT      (item IN NUMBER);
DBMS_OUTPUT.PUT      (item IN VARCHAR2);
DBMS_OUTPUT.PUT      (item IN DATE);
DBMS_OUTPUT.PUT_LINE (item IN NUMBER);
DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2);
DBMS_OUTPUT.PUT_LINE (item IN DATE);
DBMS_OUTPUT.NEW_LINE;

Parameters

Table 26-5 PUT and PUT_LINE Procedure Parameters
Parameter  Description  
a
 

Item to buffer.  

Errors

Table 26-6 PUT and PUT_LINE Procedure Errors
Error  Description  
ORA-20000, 
ORU-10027:
 

Buffer overflow, limit of <buf_limit> bytes.  

 
ORA-20000, 
ORU-10028:
 

Line length overflow, limit of 255 bytes per line.  

NEW_LINE procedure

This procedure puts an end-of-line marker. GET_LINE(S) returns "lines" as delimited by "newlines". Every call to PUT_LINE or NEW_LINE generates a line that is returned by GET_LINE(S).

Syntax

DBMS_OUTPUT.NEW_LINE;

Parameters

None.

Errors

Table 26-7 NEW_LINE Procedure Errors
Error  Description  
ORA-20000, 
ORU-10027:
 

Buffer overflow, limit of <buf_limit> bytes.  

 
ORA-20000, 
ORU-10028:
 

Line length overflow, limit of 255 bytes per line.  

GET_LINE and GET_LINES procedures

You can choose to retrieve from the buffer a single line or an array of lines. Call the GET_LINE procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the GET_LINES procedure to retrieve an array of lines from the buffer.

You can choose to automatically display this information if you are using Enterprise Manager or SQL*Plus 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.

Syntax

DBMS_OUTPUT.GET_LINE (
   line   OUT VARCHAR2,
   status OUT INTEGER);

Parameters

Table 26-8 GET_LINE Procedure Parameters
Parameter  Description  
line
 

Returns a single line of buffered information, excluding a final newline character: The maximum length is 255 bytes.  

 
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.  

Syntax

DBMS_OUTPUT.GET_LINES (
   lines       OUT  CHARARR,
   numlines IN OUT  INTEGER);

CHARARR is a table of VARCHAR2(255).

Parameters

Table 26-9 GET_LINES Procedure Parameters
Parameter  Description  
lines
 

Returns an array of lines of buffered information.

The maximum length of each line in the array is 255 bytes.  

 
numlines
 

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.  

Examples

The DBMS_OUTPUT package is commonly used to debug stored procedures and triggers, as shown in Example 1. This package can also be used to enable you to retrieve information about an object and format this output, as shown in Example 2.

Example 1

This is an example of a function that queries the employee table and returns the total salary for a specified department. The function includes several calls to the PUT_LINE procedure: 

CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
   CURSOR emp_cursor IS
      SELECT sal, comm FROM emp WHERE deptno = dnum;
   total_wages    NUMBER(11, 2) := 0;
   counter        NUMBER(10) := 1;
BEGIN

   FOR emp_record IN emp_cursor LOOP
      emp_record.comm := NVL(emp_record.comm, 0);
      total_wages := total_wages + emp_record.sal
         + emp_record.comm;
      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 dept_salary;

Assume the EMP table contains the following rows: 

EMPNO          SAL     COMM     DEPT
-----        ------- -------- -------
1002           1500      500      20
1203           1000               30
1289           1000               10
1347           1000      250      20

Assume the user executes the following statements in the Enterprise Manager SQL Worksheet input pane: 

SET SERVEROUTPUT ON
VARIABLE salary NUMBER;
EXECUTE :salary := dept_salary(20);

The user would then see the following information displayed in the output pane: 

Loop number = 1; Wages = 2000
Loop number = 2; Wages = 3250
Total wages = 3250


PL/SQL procedure successfully executed.
Example 2

In this example, the user has used the EXPLAIN PLAN command to retrieve information about the execution plan for a statement and has stored it in PLAN_TABLE. The user has also assigned a statement ID to this statement. The example EXPLAIN_OUT procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement. 

 /****************************************************************/
/* Create EXPLAIN_OUT procedure. User must pass STATEMENT_ID to */
/* to procedure, to uniquely identify statement.                */
/****************************************************************/
CREATE OR REPLACE PROCEDURE explain_out 
   (statement_id IN VARCHAR2) AS 

   -- Retrieve information from PLAN_TABLE into cursor EXPLAIN_ROWS.

   CURSOR explain_rows IS 
      SELECT level, id, position, operation, options,
         object_name 
      FROM plan_table 
      WHERE statement_id = explain_out.statement_id 
      CONNECT BY PRIOR id = parent_id 
         AND statement_id = explain_out.statement_id 
      START WITH id = 0
       ORDER BY id; 
 
BEGIN 


   -- Loop through information retrieved from PLAN_TABLE:

   FOR line IN explain_rows LOOP 

      -- At start of output, include heading with estimated cost.

      IF line.id = 0 THEN 
         DBMS_OUTPUT.PUT_LINE ('Plan for statement '
            || statement_id
            || ', estimated cost = ' || line.position); 
      END IF; 

      -- Output formatted information. LEVEL determines indention level.

      DBMS_OUTPUT.PUT_LINE (lpad(' ',2*(line.level-1)) ||
         line.operation || ' ' || line.options || ' ' || 
         line.object_name); 
   END LOOP; 

END;

See Also:

Chapter 28, "UTL_FILE"  


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index