错误提示：

p_w_picpath

 

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 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 BNZF.BNQUERYLINE 138
ORA-06512: at line 3

查阅了一下文档，这里贴出一些与本错误有关的信息：

DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000)

PUT and PUT_LINE Procedure Exceptions:
ORA-20000, ORU-10027: Buffer overflow, limit of <buf_limit> bytes.
ORA-20000, ORU-10028: Line length overflow, limit of 32767 bytes for each line

Usage Notes
1)You can build a line of information piece by piece by making multiple calls to PUT, or place an entire line of information into the buffer by calling PUT_LINE.
2)When you call PUT_LINE the item 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.
3)If your lines exceeds the line limit, you receive an error message.
4)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, SQL*Plus does 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 SERVEROUTPUT ON
SQL> BEGIN
   2 DBMS_OUTPUT.PUT_LINE (‘hello’);
   3 DBMS_LOCK.SLEEP (10);
   4 END;

      很显然当我们在输出时,控制输出结果的缓冲大小由DBMS_OUTPUT.ENABLE控制,buffer size默认为20000,每行最大的限制是32k,后面的一个例子是用来说明存储过程是在缓存了所有数据后才将结果返回。所以当我们使用游标进行输出时，如果结果很多，将会超过这个值报ORA-20000, ORU-10027: Buffer overflow, limit of <buf_limit> bytes这个错误
解决方法：
在存储过程begin后面加 :  DBMS_OUTPUT.ENABLE (buffer_size=>null)     表示没有限制

例如：

DECLARE
  Message varchar(1000);
BEGIN
  Message:='';
  DBMS_OUTPUT.ENABLE (buffer_size=>null) ;
  FOR C_TAB IN (SELECT TABLE_NAME FROM ALL_TABLES) LOOP
    Message:= C_TAB.TABLE_NAME;
    dbms_output.put_line(Message); 
  END LOOP;

END;