SQL 如何增加 dbms_output 缓冲区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16476568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to increase dbms_output buffer?
提问by hsuk
I tried to debug my dynamic query via dbms_outputbut seems like the query string is too long for dbms_outputbuffer.
我试图通过调试我的动态查询，dbms_output但似乎查询字符串对于dbms_output缓冲区来说太长了。
I got :
我有 ：
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_OUTPUT", line 148
ORA-06512: at line 1
Any idea how to increase the buffer size ?
知道如何增加缓冲区大小吗？
回答by Gopesh Sharma
You can Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000.
您可以启用 DBMS_OUTPUT 并设置缓冲区大小。缓冲区大小可以在 1 到 1,000,000 之间。
dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
exec dbms_output.enable(1000000);
Check this
检查这个
EDIT
编辑
As per the comment posted by Frank and Mat, you can also enable it with Null
根据 Frank 和 Mat 发布的评论，您也可以使用 Null 启用它
exec dbms_output.enable(NULL);
buffer_size: Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit. The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size (NOT NULL).
buffer_size：上限，以字节为单位，缓冲信息量。将 buffer_size 设置为 NULL 指定应该没有限制。最大大小为 1,000,000，当用户指定 buffer_size (NOT NULL) 时，最小为 2,000。
回答by popats.
When buffer size gets full. There are several options you can try:
当缓冲区大小变满时。您可以尝试多种选择：
1) Increase the size of the DBMS_OUTPUT buffer to 1,000,000
1) 将 DBMS_OUTPUT 缓冲区的大小增加到 1,000,000
2) Try filtering the data written to the buffer - possibly there is a loop that writes to DBMS_OUTPUT and you do not need this data.
2) 尝试过滤写入缓冲区的数据 - 可能存在写入 DBMS_OUTPUT 的循环，而您不需要此数据。
3) Call ENABLE at various checkpoints within your code. Each call will clear the buffer.
3) 在代码中的各个检查点调用 ENABLE。每次调用都会清除缓冲区。
DBMS_OUTPUT.ENABLE(NULL) will default to 20000 for backwards compatibility Oracle documentation on dbms_output
DBMS_OUTPUT.ENABLE(NULL) 将默认为 20000 以实现向后兼容性 dbms_output 上的 Oracle 文档
You can also create your custom output display.something like below snippets
您还可以创建自定义输出显示。类似于下面的代码片段
create or replace procedure cust_output(input_string in varchar2 )
is
out_string_in long default in_string;
string_lenth number;
loop_count number default 0;
begin
str_len := length(out_string_in);
while loop_count < str_len
loop
dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) );
loop_count := loop_count +255;
end loop;
end;
Link -Ref :Alternative to dbms_output.putline@ By: Alexander
链接 -Ref ：替代 dbms_output.putline@ By：Alexander
回答by Деян Добромиров
Here you go:
干得好：
DECLARE
BEGIN
dbms_output.enable(NULL); -- Disables the limit of DBMS
-- Your print here !
END;
