了解DBMS_OUTPUT包

DBMS_OUTPUT程序包是我们在Oracle开发过程中常用的一个包体，使用该包我们可以从存储过程、包或触发器发送信息(messages)。Oracle推荐在debug PL/SQL程序时使用该程序包，不推荐使用该包来做报表输出或其他格式化输出之用。

概述

DBMS_OUTPUT包主要用于调试PL/SQL程序，或者在SQL*PLUS命令中显示信息(displaying message)和报表，譬如我们可以写一个简单的匿名PL/SQL程序块，而该块出于某种目的使用DBMS_OUTPUT包来显示一些信息。

在该DBMS_OUTPUT包中存在2个存储过程，它们是PUT_LINE和PUT过程，使用这2个Procedure可以做到将信息存放到PL/SQL的Buffer中，以便其他的触发器、存储过程、程序包来读取。在独立的PL/SQL程序或匿名块中，我们还可以使用GET_LINES和GET这2个存储过程来将存放在PL/SQL Buffer中的信息输出(display)到屏幕。

如果该DBMS_OUTPUT包被禁用了，那么所有对其子程序(subprogram)的调用都将被忽略。这样用户可以设计应用程序，仅在客户端程序能够处理这些信息的时候启用这些子程序。

安全模型

必须使用SYS用户运行$ORACLE_HOME/rdbms/admin/dbmsotpt.sql，该脚本会为DBMS_OUTPUT创建同义词，并将该包的执行权限赋予PUBLIC角色。

操作提示

• 若不调用GET_LINE函数，或者不在SQL*PLUS中将信息(information)输出到屏幕的话，那么缓存的信息(buffered message)最终将被忽略。
• SQL*PLUS会在SQL语句或匿名PL/SQL块调用结束后调用GET_LINES过程
• 在SQL*PLUS中输入SET SERVEROUTPUT ON，将启动下面语句的效果:
• DBMS_OUTPUT.ENABLE (buffer_size => NULL);
• 输出不再有限制(no limit on the output)
• 不推荐在应用程序代码中调用ENABLE或DISABLE过程，因为这将导致如SQL*PLUS这种外部工具无法正常控制输出与否。

注意使用DBMS_OUTPUT传送的message在实际执行该DBMS_OUTPUT的子程序或触发器执行完成之前都不会实际被发送。这也就导致了在整个运行过程中不会有信息被写出，所以用DBMS_OUTPUT包做日志输出的话只能等到整个子程序结束才会有日志出现。

程序异常

DBMS_OUTPUT子程序可能引发ORA-20000错误，同时其OUTPUT存储过程可能返回以下错误:

规则和限制

• 最大的单行长度是32767 bytes字节
• 默认的buffer大小时20000 bytes字节，最小的buffer为2000 bytes字节，最大没有限制

使用示例

示例1：在触发器生成输出

我们可以使用一个触发器来打印正在调试进程的一些信息，譬如你可以在触发器中调用以下代码:

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

若启用了DBMS_OUTPUT包，那么由PUT_LINE所生成的文本将被缓存到Buffer中，之后我们可以通过以下代码获取该Buffer中的信息:

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

你还可以额外地将buffer显示在屏幕上。可以反复调用GET_LINE直到status返回非零值。处于性能的考虑，推荐使用GET_LINES过程，该过程将返回一个行的数组(an array of lines)。

示例2:调试存储过程和触发器

DBMS_OUTPUT包常被用于调试(debug)存储过程和触发器。该包也可以用于获取对象信息并且格式化地输出。

以下函数将查询EMP表并返回某个部门的总薪水，该函数包含了多次对PUT_LINE过程的调用。

假设EMP表的数据如下：

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

函数代码如下：

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

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;

假设用户在SQL*PLUS中执行以下语句:

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

用户将在屏幕输出中看到以下信息:

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

PL/SQL procedure successfully executed.

示例3：检索对象相关信息

以下示例中用户使用EXPLAIN PLAN命令以检索某条语句的执行计划信息，这些信息存放在plan_table表中。用户还为语句分配了语句ID(statement ID)。示例的EXPLAIN_OUT存储过程从表上检索信息并嵌套地格式化输出。

/****************************************************************/
/* 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;

数据结构

DBMS_OUTPUT包声明了2中集合类型，以便GET_LINES函数使用。

TABLE Types

CHARARR Table Type

OBJECT Types

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

DBMS_OUTPUT包子程序摘要

子程序	描述
DISABLE 存储过程	禁用消息输出
ENABLE  存储过程	启用消息输出
GET_LINE 存储过程	从buffer中获取单行信息
GET_LINES 存储过程	从buffer中获取信息数组
NEW_LINE 存储过程	终结有PUT过程所创建的一行
PUT 存储过程	将一行信息放到buffer中
PUT_LINE 存储过程	将部分行信息放到buffer中

注意目前PUT过程已废弃，因为遗留问题将被保留，但不在推荐使用。

DISABLE存储过程

该存储过程用以禁用对PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES过程的调用，并会清理buffer中任何残存信息。
与之相反的是ENABLE存储过程，若在SQL*PLUS中使用SERVEROUTPUT选项则不需要调用该存储过程。

语法
DBMS_OUTPUT.DISABLE;

编译器指示

pragma restrict_references(disable,WNDS,RNDS);

ENABLE存储过程

该存储过程用以启用对PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES存储过程的调用。

语法

DBMS_OUTPUT.ENABLE (
buffer_size IN INTEGER DEFAULT 20000);

编译指示

pragma restrict_references(enable,WNDS,RNDS);

参量

参量	描述
buffer_size	Buffer长度的上限，单位为bytes字节。将buffer_size设置为NULL即指定没有限制

使用注意事项

• 当在SQL*PLUS总使用 SET SERVEROUTPUT选项时没有必要使用该过程
• 若有多次对ENABLE过程的调用，那么buffer_size以最后指定的为准。当用户没有指定buffer size为NULL时，该参量的上限为1,000,000字节，而下限为2,000
• 一般建议buffer size设置为NULL，10g中默认值使用20000以便兼容老的数据库版本。

GET_LINE 存储过程

该过程用以从buffer中获取单行信息。

语法

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

参量

参量	描述
line	将返回buffer中的一行信息，但不包括最后的换行符。一般建议申明该参数为VARCHAR2(32767)类型，以避免出现ORA-06502: PL/SQL: numeric or value error: character string buffer too small”.错误
status	若调用成功则返回0，若buffer中没有更多行的信息则返回1

使用注意事项

• 可以选择是从buffer中获取单行还是多行的数组信息。使用GET_LINE过程可以获取单行信息。从性能考虑，应当减少调用次数，使用GET_LINES函数直接从buffer中返回一个多行的数组
• 若在使用SQL*PLUS则可以设置SET SERVEROUTPUT选项控制是否直接输出到屏幕
• 在调用GET_LINE或GET_LINES后，任何行信息在下一次的PUT, PUT_LINE, or NEW_LINE前没有被获取的都将被丢弃，以避免误会

GET_LINES 存储过程

该存储过程用以从Buffer中获取一个多行的的数组

语法

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

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

参量

参量	描述
lines	返回Buffer中多行信息的数组。该数组中每行的最大长度为32767bytes。
numlines	想要获取行的数量。在成功获取多行信息后，该过程将返回实际获取的行数。若返回的数字小于要求的值时，说明buffer中没有更多行的信息了。

使用注意事项：

• 可以选择是从buffer中获取单行还是多行的数组信息。使用GET_LINE过程可以获取单行信息。从性能考虑，应当减少调用次数，使用GET_LINES函数直接从buffer中返回一个多行的数组
• 若在使用SQL*PLUS则可以设置SET SERVEROUTPUT选项控制是否直接输出到屏幕
• 在调用GET_LINE或GET_LINES后，任何行信息在下一次的PUT, PUT_LINE, or NEW_LINE前没有被获取的都将被丢弃，以避免误会

NEW_LINE 存储过程

该存储过程将放置一个换行符。GET_LINE和GET_LINES过程所返回的行由”newlines”标记分隔。每次调用PUT_LINE或NEW_LINE过程生成的行，最后由GET_LINE(S)过程返回。

语法

DBMS_OUTPUT.NEW_LINE;

PUT 存储过程

该过程用以追加部分内容到Buffer中的行

语法

DBMS_OUTPUT.PUT (
item IN VARCHAR2);

参量

参量	描述
item	加入buffer的内容

程序异常

PUT and PUT_LINE 存储过程可能发生的程序异常

错误	描述
ORA-20000, ORU-10027:	Buffer overflow, limit of <buf_limit> bytes.Buffer溢出
ORA-20000, ORU-10028:	Line length overflow, limit of 32767 bytes for each line.行长溢出

使用注意事项

• 可以多次调用PUT过程将信息一点一点地追加到单行，或者使用PUT_LINE过程将整行信息存放到Buffer中
• 当调用PUT_LINE过程时将自动加入换行符，若使用PUT来构建行，那么必须手动使用NEW_LINE过程来加入换行符。GET_LINE和GET_LINES过程不会返回没有以换行符终结的一行。
• 若行长超过限制那么将报错
• 注意在调用PUT或PUT_LINE的PL/SQL程序单元结束之前，对PUT或PUT_LINE指定的内容不会输出。
• 如以下SQL*PLUS中执行的匿名块，直到该匿名块结束才会有输出：

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

PUT_LINE 存储过程

该存储过程用以在Buffer中放置一行信息。

语法

DBMS_OUTPUT.PUT_LINE (
item IN VARCHAR2);

参量

参量	描述
item	加入buffer的内容

程序异常

PUT and PUT_LINE 存储过程可能发生的程序异常

错误	描述
ORA-20000, ORU-10027:	Buffer overflow, limit of <buf_limit> bytes.Buffer溢出
ORA-20000, ORU-10028:	Line length overflow, limit of 32767 bytes for each line.行长溢出

使用注意事项

• 可以多次调用PUT过程将信息一点一点地追加到单行，或者使用PUT_LINE过程将整行信息存放到Buffer中
• 当调用PUT_LINE过程时将自动加入换行符，若使用PUT来构建行，那么必须手动使用NEW_LINE过程来加入换行符。GET_LINE和GET_LINES过程不会返回没有以换行符终结的一行。
• 若行长超过限制那么将报错
• 注意在调用PUT或PUT_LINE的PL/SQL程序单元结束之前，对PUT或PUT_LINE指定的内容不会输出。
• 如以下SQL*PLUS中执行的匿名块，直到该匿名块结束才会有输出：

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

以下文字译自官方文档<DBMS_OUTPUT>

以下为DBMS_OUTPUT包的头部信息:

create or replace package dbms_output authid definer as

-- DE-HEAD     <- tell SED where to cut when generating fixed package

  ------------
  --  OVERVIEW
  --
  --  These procedures accumulate information in a buffer (via "put" and
  --  "put_line") so that it can be retrieved out later (via "get_line" or
  --  "get_lines").  If this package is disabled then all
  --  calls to this package are simply ignored.  This way, these routines
  --  are only active when the client is one that is able to deal with the
  --  information.  This is good for debugging, or SP's that want to want
  --  to display messages or reports to sql*dba or plus (like 'describing
  --  procedures', etc.).  The default buffer size is 20000 bytes.  The
  --  minimum is 2000 and the maximum is 1,000,000.

  -----------
  --  EXAMPLE
  --
  --  A trigger might want to print out some debugging information.  To do
  --  do this the trigger would do
  --    dbms_output.put_line('I got here:'||:new.col||' is the new value');
  --  If the client had enabled the dbms_output package then this put_line
  --  would be buffered and the client could, after executing the statement
  --  (presumably some insert, delete or update that caused the trigger to
  --  fire) execute
  --    begin dbms_output.get_line(:buffer, :status); end;
  --  to get the line of information back.  It could then display the
  --  buffer on the screen.  The client would repeat calls to get_line
  --  until status came back as non-zero.  For better performance, the
  --  client would use calls to get_lines which can return an array of
  --  lines.
  --
  --  SQL*DBA and SQL*PLUS, for instance, implement a 'SET SERVEROUTPUT
  --  ON' command so that they 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).

  ------------
  --  SECURITY
  --
  --  At the end of this script, a public synonym (dbms_output) is created
  --  and execute permission on this package is granted to public.

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure enable (buffer_size in integer default 20000);
  pragma restrict_references(enable,WNDS,RNDS);
  --  Enable calls to put, put_line, new_line, get_line and get_lines.
  --    Calls to these procedures are noops if the package has
  --    not been enabled.  Set default amount of information to buffer.
  --    Cleanup data buffered from any dead sessions.  Multiple calls to
  --    enable are allowed.
  --  Input parameters:
  --    buffer_size
  --      Amount of information, in bytes, to buffer.  Varchar2, number and
  --      date items are stored in their internal representation.  The
  --      information is stored in the SGA. An error is raised if the
  --      buffer size is exceeded.  If there are multiple calls to enable,
  --      then the buffer_size is generally the largest of the values
  --      specified, and will always be >= than the smallest value
  --      specified.  Currently a more accurate determination is not
  --      possible.  The maximum size is 1,000,000, the minimum is 2000.

  procedure disable;
  pragma restrict_references(disable,WNDS,RNDS);
  --  Disable calls to put, put_line, new_line, get_line and get_lines.
  --    Also purge the buffer of any remaining information.

  procedure put(a varchar2);
  pragma restrict_references(put,WNDS,RNDS);
  --  Put a piece of information in the buffer.  When retrieved by
  --    get_line(s), the number and date items will be formated with
  --    to_char using the default formats. If you want another format
  --    then format it explicitly.
  --  Input parameters:
  --    a
  --      Item to buffer

  procedure put_line(a varchar2);
  pragma restrict_references(put_line,WNDS,RNDS);
  --  Put a piece of information in the buffer followed by an end-of-line
  --    marker.  When retrieved by get_line(s), the number and date items
  --    will be formated with to_char using the default formats.  If you
  --    want another format then format it explicitly. get_line(s) return
  --    "lines" as delimited by "newlines". So every call to put_line or
  --    new_line will generate a line that will be returned by get_line(s).
  --  Input parameters:
  --    a
  --      Item to buffer
  --  Errors raised:
  --    -20000, ORU-10027: buffer overflow, limit of <buf_limit> bytes.
  --    -20000, ORU-10028:line length overflow, limit of 32767 bytes per line.

  procedure new_line;
  pragma restrict_references(new_line,WNDS,RNDS);
  --  Put an end-of-line marker.  get_line(s) return "lines" as delimited
  --    by "newlines".  So every call to put_line or new_line will generate
  --    a line that will be returned by get_line(s).

  procedure get_line(line out varchar2, status out integer);
  pragma restrict_references(get_line,WNDS,RNDS);
  --  Get a single line back that has been buffered.  The lines are
  --    delimited by calls to put_line or new_line.  The line will be
  --    constructed taking all the items up to a newline, converting all
  --    the items to varchar2, and concatenating them into a single line.
  --    If the client fails to retrieve all lines before the next put,
  --    put_line or new_line, the non-retrieved lines will be discarded.
  --    This is so if the client is interrupted while selecting back
  --    the information, there will not be junk left over which would
  --    look like it was part of the NEXT set of lines.
  --  Output parameters:
  --    line
  --      This line will hold the line - it may be up to 32767 bytes long.
  --    status
  --      This will be 0 upon successful completion of the call.  1 means
  --      that there are no more lines.

  type chararr is table of varchar2(32767) index by binary_integer;
  procedure get_lines(lines out chararr, numlines in out integer);
  pragma restrict_references(get_lines,WNDS,RNDS);
  --  Get multiple lines back that have been buffered.  The lines are
  --    delimited by calls to put_line or new_line.  The line will be
  --    constructed taking all the items up to a newline, converting all
  --    the items to varchar2, and concatenating them into a single line.
  --    Once get_lines is executed, the client should continue to retrieve
  --    all lines because the next put, put_line or new_line will first
  --    purge the buffer of leftover data.  This is so if the client is
  --    interrupted while selecting back the information, there will not
  --    be junk left over.
  --  Input parameters:
  --    numlines
  --      This is the maximum number of lines that the caller is prepared
  --      to accept.  This procedure will not return more than this number
  --      of lines.
  --  Output parameters:
  --    lines
  --      This array will line will hold the lines - they may be up to 32767
  --      bytes long each.  The array is indexed beginning with 0 and
  --      increases sequentially.  From a 3GL host program the array begins
  --      with whatever is the convention for that language.
  --    numlines
  --      This will be the number of lines actually returned.  If it is
  --      less than the value passed in, then there are no more lines.

  --FIXED_ONLYTYPE dbmsoutput_linesarray IS VARRAY(2147483647) OF
  --FIXED_ONLY     VARCHAR2(32767);
  procedure get_lines(lines out dbmsoutput_linesarray, numlines in out integer);
  --  get_lines overload with dbmsoutput_linesarray varray type for lines.
  --  It is recommended that you use this overload in a 3GL host program to
  --  execute get_lines from a PL/SQL anonymous block.
  pragma restrict_references(get_lines,WNDS,RNDS);

  pragma TIMESTAMP('2000-06-22:11:21:00');

end;

-- CUT_HERE    <- tell sed where to chop off the rest

Working Around DBMS_OUTPUT Limitation on Real-time Display

Overview
--------

When using the PL/SQL Package DBMS_OUTPUT to display messages, the
messages are not displayed in real-time through applications such as
SQL*Plus.  

This behavior is due to the way that DBMS_OUTPUT is structured.  The
text strings are stored in a PL/SQL table and then pulled out by the
calling application through the use of DBMS_OUTPUT.GET_LINES().  In
order to obtain real-time messaging, another method must be used.

Methods for Real-time Messaging
-------------------------------

The following methods may be used to accomplish real-time messaging.  

1.  Perform INSERT statements to a log table.  This method, however, requires
    a COMMIT to be able to see the messages from another session.  

2.  Write a Pro*C or OCI application and communicate to it through
    DBMS_PIPE.  This method, however, is overly complex.  It requires several
    constructs to ensure that any particular instance of the application is
    listening to the correct pipe, unless the application is designed to
    handle all outputs.  

3.  On UNIX systems, a third method is available using the UTL_FILE package.
    The package allows PL/SQL to write to an OS file,  and from another
    UNIX session, a tail -f can be issued on the file to obtain 'real-time'
    updates (this is done from another UNIX session).  

Method 3 Example Program
------------------------

Same code demonstrating the third method of achieving real-time messaging
is included below.  In the sample below, because the code executes so
quickly, the file is generated and then 'tail -f plsql.log' is run before
the PL/SQL block.  

Before running the program, the initSID.ora parameter of utl_file_dir must
include '/tmp' or be set to '*'.

Example Code
------------

DECLARE
  fhnd   utl_file.file_type;
  CURSOR c1 IS SELECT ename FROM emp;
BEGIN
  fhnd:= utl_file.fopen( '/tmp', 'plsql.log', 'w' );

  FOR c1_rec IN c1 LOOP
    utl_file.put_line( fhnd, 'Found => ' || c1_rec.ename );
    utl_file.fflush( fhnd );
  END LOOP;

  utl_file.fclose( fhnd );

EXCEPTION
  WHEN OTHERS THEN
    IF utl_file.is_open( fhnd ) THEN
      utl_file.fclose( fhnd );
    END IF;
END;
/

Results
-------

% touch plsql.log
% cat plsql.log
% tail -f plsql.log
Found => SMITH
Found => ALLEN
Found => WARD
Found => JONES
Found => MARTIN
Found => BLAKE
Found => CLARK
Found => SCOTT
Found => KING
Found => TURNER
Found => ADAMS
Found => JAMES
Found => FORD
Found => MILLER
^C%

Related Issues
--------------

BUG:178592     ADD REAL TIME FLUSHING OF BUFFER FOR DBMS_OUTPUT.PUT_LINE
               (enhancement request to allow SQL*Plus to obtain real-time
                output)

ORA-20000 ORU-10027 USING DBMS_OUTPUT PROCEDURE

Applies to:

PL/SQL - Version: 7.3.2.0 to 11.2.0.1 - Release: to 11.2
Oracle Server - Enterprise Edition - Version: 7.3.2.0 to 11.2.0.1   [Release: 7.3.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 02-Sep-2010***
Symptoms

When running a procedure that uses DBMS_OUTPUT an ORA-20000 and an ORU-10027 error may
return even if serveroutput was not enabled in SQL*Plus. 

ORA-20000:
Cause: The stored procedure RAISE_APPLICATION_ERROR was called,
       which causes this message to be generated.
Action: Correct the problem as described in the message or contact
        the application administrator or database administrator
        for more information. 

The following example demonstrates the error.
1 - Create the stored procedure. 

create or replace procedure blowup as
   i number;
begin
   for i in 1..2000 loop
      dbms_output.put_line('123456789012345678901234567890123456789012345');
   end loop;
end;
/
2 - Execute the procedure 

SQL> execute blowup 

PL/SQL procedure successfully completed.
3 - This enables the output and shows the serveroutput is off. 

SQL> execute dbms_output.enable(2000); 

PL/SQL procedure successfully completed. 

SQL> show serveroutput
serveroutput OFF
4 - The following executes the procedure and produces the error. 

SQL> execute blowup
BEGIN blowup; END; 

*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "T1.BLOWUP", line 5
ORA-06512: at line 1
Cause

This is correct behavior for the DBMS_OUTPUT procedure. 

The meaning of the ORU-10027 error is pretty clear, but it may not be clear why the error is returned
since the output is set to disabled.

In the example above the DBMS_OUT_PUT.ENABLE(2000) procedure to set the maximum
buffer size for output. What isn't clear is that when the enable procedure is used it causes
all subsequent DBMS_OUT_PUT.PUT_LINE statements to place information into the DBMS_OUTPUT
buffer regardless of whether SEVEROUTPUT is enabled or disabled in the front-end tool. 

The SET SERVEROUTPUT ON/OFF is the mechanism used by the front-end tools SQL*Plus and
Server Manager to enable or disable the printing of the output buffer to the screen,
it also makes the appropriate calls to the database to enable / disable the buffer at the database end. 

When 'SET SERVEROUTPUT ON' in set in SQL*Plus both the front end buffer and
a call BEGIN DBMS_OUTPUT.ENABLE(2000); END; is made to the database.
When 'SET SERVEROUTPUT OFF' is set in the same way the local buffer is released and the call
 BEGIN DBMS_OUTPUT.DISABLE; END; is made. 

The confusing part is that issuing the command 'SHOW SERVEROUTPUT' will ONLY
show the buffer size if serveroutput is enabled with the 'SET SERVEROUTPUT ON'
command, the problem comes when BEGIN DBMS_OUTPUT.ENABLE(2000); END; is issued
without also initializing the front end buffer so it is not obvious in SQL*Plus
that output is written to the DBMS_OUTPUT buffer on the database side.

Solution

Ensure that any calls to the DBMS_OUTPUT.ENABLE procedure set the buffer size
to a large enough value for subsequent DBMS_OUTPUT.PUT_LINE calls. 

Since the DBMS_OUTPUT procedures are used mostly for debugging PL/SQL scripts setting
the output buffer size to its maximum of 1,000,000 may seem like a valid fix.
This will work as long as the amount of data is reasonable. However,
in a production environment it may be impossible to anticipate how
much data will be sent to the buffer via the DBMS_OUTPUT.PUT_LINE calls. 

By removing all DBMS_OUTPUT.ENABLE calls from the code you will guarantee that
production users will not encounter the ORA-20000 and ORU-10027 errors no matter
how much data is involved. This also ensure that when an end user issues the
'SHOW SERVEROUTPUT' command that they will see the
appropriate information. 

Note that if there is a desire to always have SERVEROUTPUT ON it can be placed the
appropriate command in a login.sql file in the directory where SQL*Plus is executed.
Any statements in this file are automatically issued whenever SQL*Plus is started. 

Use one of the following two methods to increase the size of the buffer: 

1. At the SQL*PLUS prompt, type the following where
SET SERVEROUTPUT ON SIZE n
where n is an integer between 2,000 and 1,000,000. 

Example: To increase the buffer size to 10,000 type:

SQL> SET SERVEROUTPUT ON SIZE 10000 

2. Inside the PL/SQL block type
DBMS_OUTPUT.ENABLE(n)
where n is an integer between 2,000 and 1,000,000.

DBMS_OUTPUT.ENABLE(10000);

Starting with version 10.2 DBMS_OUTPUT can be set for unlimited buffer size when using "null".

The following URL lead to documentation which discusses this relaxed limitation.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14214/chapter1.htm#FEATURENO05591

The above URL navigates to the Oracle� Database New Features Guide documentation.
From within that document is section 1.1.5.3 titled "Relaxation of Line Length and
Overall Limits for the DBMS_OUTPUT PL/SQL Package".

ORA-20000, ORU-10028 WITH DBMS_OUTPUT

Applies to:

PL/SQL - Version: 10.1.0.6 to 10.2.0.5 - Release: 10.1 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 19-Feb-2010***
Symptoms

Attempting to use the DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUT_LINE command and getting the following error:

ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
Cause

For database versions 10.1x and earlier the maximum length for the
DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE is 255. The following is from the 10.1 Documentation.

ORA-20000, ORU-10028:         Line length overflow, limit of 255 bytes for each line

For database versions 10.2.x and higher, the maximum length for the DBMS_OUTPUT.PUT
and DBMS_OUTPUT.PUT_LINE have been increased to 32767 bytes for each line.
The following is from the 10.2 Documentation.

ORA-20000, ORU-10028:             Line length overflow, limit of 32767 bytes for each line.

The limit for the PUT command MUST include a space for the end-of-line marker.
Using DBMS_OUTPUT.PUT together DBMS_OUTPUT.NEW_LINE is similar to using DBMS_OUTPUT.PUT_LINE. 

Solution

If using a 10.1.x Database or earlier, the string will have to be scaled down,
but if this is not feasible at all, it may be best to write the string to a table that
contains a large VARCHAR2 field or even a CLOB.

If using a 10.2.x Database or higher, there is less of a chance this issue will be
encountered due to the significant increase in buffer size, but if this issue is
still being encountered, the above solution can also be implemented here as well
except that a CLOB would be a better choice than a VARCHAR2 field.