Output from Stored Procedures and Triggers

Server Manager or SQL*Plus can also display messages buffered by the DBMS_OUTPUT procedures. To do this, you must issue the command SET SERVEROUTPUT ON in Server Manager or SQL*Plus.

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 do not display the messages on your screen in SQL*Plus or Server Manager, the buffered messages are ignored. The DBMS_OUTPUT package is especially useful for displaying PL/SQL debugging information.

Note: Messages sent using the 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.

Summary

Function/Procedure	Description	Refer to
ENABLE	enable message output
DISABLE	disable message output
PUT_LINE	place a line in the buffer
PUT	place partial line in buffer
NEW_LINE	terminate a line created with PUT
GET_LINE	retrieve one line of information from buffer
GET_LINES	retrieve array of lines from buffer

Table 8 - 10. DBMS_OUTPUT Package Functions and Procedures

Creating the DBMS_OUTPUT Package

Errors

ORU-10027: buffer overflow

ORU-10028: line length overflow

ENABLE Procedure

You must specify the amount of information, in bytes, to buffer. Items are stored in the DBMS_OUTPUT package. If the buffer size is exceeded, you receive the following error message:

ORA-20000, ORU-10027: buffer overflow, limit of <buffer_limit> bytes.

Multiple calls to ENABLE are allowed. If there are multiple calls to ENABLE, BUFFER_SIZE is the largest of the values specified. The maximum size is 1000000 and the minimum is 2000.

Syntax

DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 2000);

DISABLE Procedure

Syntax

DBMS_OUTPUT.DISABLE;

PUT and PUT_LINE Procedures

All items are converted to VARCHAR2 as they are retrieved. If you pass an item of type NUMBER or DATE, when that item is retrieved, it is formatted with TO_CHAR using the default format. If you want to use a different format, 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, 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, you receive an error message.

Attention: Output that you create using PUT or PUT_LINE is buffered in the SGA. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller. So, for example, Server Manager or SQL*Plus do not display DBMS_OUTPUT messages until the PL/SQL program completes. In this release, there is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program.

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;

GET_LINE and GET_LINES Procedures

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);

Parameter	Description
line	Returns a single line of buffered information, excluding a final newline character. The maximum length of this parameter is 255 bytes.
status	If the call completes successfully, the status returns as 0. If there are no more lines in the buffer, the status is 1.

Table 8 - 11. DBMS_OUTPUT.GET_LINE Procedure Parameters

The parameters for the GET_LINES procedure are described in Table 8 - 12. The syntax for this procedure is

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

where CHARARR is a table of VARCHAR2(255), defined as a type in the DBMS_OUTPUT package specification.

Parameter	Description
lines	Returns an array of lines of buffered information. The maximum length of each line in the array is 255 bytes.
numlines	Specify the 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, there are no more lines in the buffer.

Table 8 - 12. DBMS_OUTPUT.GET_LINE Procedure Parameters

Examples Using the DBMS_OUTPUT Package

Example 1

An example of a function that queries the employee table and returns the total salary for a specified department follows. 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 you execute the following statements in the Server Manager SQL Worksheet input pane:

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

You 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

This example assumes that the user has used the EXPLAIN PLAN command to retrieve information about the execution plan for a statement and store it in PLAN_TABLE, and that the user has 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 is used to
      -- determine indention level.
      DBMS_OUTPUT.PUT_LINE (lpad(' ',2*(line.level-1)) ||
         line.operation || ' ' || line.options || ' ' ||
         line.object_name); 
   END LOOP; 
END;