重庆思庄Oracle、Redhat认证学习论坛

标题: ORA-20000：ORU-10027：buffer overflow,limit of 2000 bytes [打印本页]

作者: 郑全 时间: 2016-9-14 15:19
标题: ORA-20000：ORU-10027：buffer overflow,limit of 2000 bytes
写存储过程时遇到一个问题，执行dbms_output.putline(变量名)的时候，报错
ORA-20000：ORU-10027：buffer overflow,limit of 2000 bytes.

$ oerr ora 20000
20000, 00000, "%s"
// *Cause:  The stored procedure 'raise_application_error'
//       was called which causes this error to be generated.
// *Action: Correct the problem as described in the error message or contact
//       the application administrator or DBA for more information.

应该是变量大小超过了dbms_output.putline的最大值。

解决办法：
SQL>set serveroutput on size 1000000

##2014-07-18添加
解决办法2：
在begin后面加上DBMS_OUTPUT.ENABLE(buffer_size => null) ，表示输出buffer不受限制。

如下面的语句是为了获取创建索引语句
set serveroutput on
declare
  v_sql varchar2(1000);
  v_result varchar2(2000);
begin
  for cur_sql in (select 'select dbms_metadata.get_ddl(''INDEX'',''' ||
                     T.INDEX_NAME || ''',''XXXX'') FROM DUAL' as f_sql
                  from v$object_usage t
               where t.monitoring = 'YES'
                  AND T.USED = 'NO') loop
begin
   DBMS_OUTPUT.ENABLE(buffer_size => null); --表示输出buffer不受限制
   execute immediate cur_sql.f_sql
      into v_result;
   --DBMS_OUTPUT.PUT_LINE(cur_sql.f_sql);
   DBMS_OUTPUT.PUT_LINE(v_result);
end;
  end loop;
end;
/

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)