Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Packages Reference
11g Release 2 (11.2.2)

E21645-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 DBMS_OUTPUT

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:


Using DBMS_OUTPUT

This section contains topics which relate to using the DBMS_OUTPUT package.


Overview

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.


Operational notes

Note:

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

Rules and limits


Exceptions

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

ORU-10027

Buffer overflow

ORU-10028

Line length overflow



Examples

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.

Data structures

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.

Table types

CHARARR table type

DBMSOUTPUT_LINESARRAY table type


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

Syntax

TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

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.

Syntax

TYPE DBMSOUTPUT_LINESARRAY IS
     VARRAY(2147483647) OF VARCHAR2(32767);

Summary of DBMS_OUTPUT subprograms

Table 4-2 DBMS_OUTPUT package subprograms

Subprogram Description

DISABLE procedure

Disables message output.

ENABLE procedure

Enables message output.

GET_LINE procedure

Retrieves one line from buffer.

GET_LINES procedure

Retrieves an array of lines from buffer.

NEW_LINE procedure

Terminates a line created with PUT.

PUT procedure

Places a line in the buffer.

PUT_LINE procedure

Places partial line in buffer.



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 the ENABLE procedure, you do not need to call this procedure if you are using the SET SERVEROUTPUT ON setting from ttIsql.

Syntax

DBMS_OUTPUT.DISABLE;

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

Syntax

DBMS_OUTPUT.ENABLE (
   buffer_size IN INTEGER DEFAULT 20000);

Parameters

Table 4-3 ENABLE procedure parameters

Parameter Description

buffer_size

Upper limit, in bytes, for the amount of buffered information

Setting buffer_size to NULL specifies that there should be no limit.


Usage notes


GET_LINE procedure

This procedure retrieves a single line of buffered information.

Syntax

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

Parameters

Table 4-4 GET_LINE procedure parameters

Parameter Description

line

A single line of buffered information, excluding a final newline character

You should declare this parameter as VARCHAR2(32767) to avoid the risk of "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

status

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.


Usage notes


GET_LINES procedure

This procedure retrieves an array of lines from the buffer.

Syntax

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

Parameters

Table 4-5 GET_LINES procedure parameters

Parameter Description

lines

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.

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.


Usage notes


NEW_LINE procedure

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.

Syntax

DBMS_OUTPUT.NEW_LINE;

PUT procedure

This procedure places a partial line in the buffer.

Note:

The PUT version that takes a NUMBER input is obsolete. It is supported for legacy reasons only.

Syntax

DBMS_OUTPUT.PUT (
    a IN VARCHAR2);

Parameters

Table 4-6 PUT procedure parameters

Parameter Description

a

Item to buffer


Usage notes

Exceptions

Table 4-7 PUT procedure exceptions

Exception Description

ORA-20000, ORU-10027

Buffer overflow, according to the buffer_size limit specified in the ENABLE procedure call

ORA-20000, ORU-10028

Line length overflow, limit of 32767 bytes for each line



PUT_LINE procedure

This procedure places a line in the buffer.

Note:

The PUT_LINE version that takes a NUMBER input is obsolete. It is supported for legacy reasons only.

Syntax

DBMS_OUTPUT.PUT_LINE (
   a IN VARCHAR2);

Parameters

Table 4-8 PUT_LINE procedure parameters

Parameter Description

a

Item to buffer


Usage notes

Exceptions

Table 4-9 PUT_LINE procedure exceptions

Exception Description

ORA-20000, ORU-10027

Buffer overflow, according to the buffer_size limit specified in the ENABLE procedure call

ORA-20000, ORU-10028

Line length overflow, limit of 32767 bytes for each line