ORA-20000:ORU-10027:buffer overflow,limit of 2000 bytes.
在sql*plus 等工具使用 dbms_output函数进行输出时,如若输出的内容很多,会出现ORA-20000:ORU-10027:buffer overflow,limit of 2000 bytes.错误,这是由于输出缓冲区溢出造成的,有两种方法来解决!
1、增加输出缓冲区大小
SET SERVEROUTPUT ON SIZE 1000000;
2、DBMS_OUTPUT缓冲区设置限制
在输出的最开始运行DBMS_OUTPUT.ENABLE(buffer_size => null),说明不启用输出限制
以下为oracle DBMS_OUTPUT.ENABLE的说明
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.
##############################
通往精神的路很多,物质只是其中一种
##############################
http://www.onejava.com/article/oracle/wip/wiptop.htm
https://docs.oracle.com/cd/A60725_05/html/comnls/us/index.htm
http://www.oracle.com/technetwork/cn/developer-tools/apex/getting-started-094884-zhs.html
https://docs.oracle.com/cd/B34956_01/current/html/docset.html
http://www.onejava.com/article/oracle/wip/wiptop.htm
https://docs.oracle.com/cd/A60725_05/html/comnls/us/index.htm
http://www.oracle.com/technetwork/cn/developer-tools/apex/getting-started-094884-zhs.html
https://docs.oracle.com/cd/B34956_01/current/html/docset.html