oracle pl/sql 错误：put_line 不能超过 2000 个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1767003/
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
oracle pl/sql bug: can't put_line more than 2000 characters
提问by FrustratedWithFormsDesigner
Has anyone else noticed this phenomenon where dbms_output.put_lineis unable to print more than 2000 characters at a time?
有没有人注意到这种dbms_output.put_line无法一次打印超过2000个字符的现象？
Script is:
脚本是：
set serveroutput on size 100000;
declare
big_str varchar2(2009);
begin
for i in 1..2009 loop
big_str := big_str||'x';
end loop;
dbms_output.put_line(length(big_str));
dbms_output.put_line(big_str);
end;
/
I copied and pasted the output into an editor (Notepad++) which told me there were only 2000 characters, not 2009 which is what I think should have been pasted. This also happens with a few of my test scripts - only 2000 characters get printed.
我将输出复制并粘贴到编辑器 (Notepad++) 中，它告诉我只有 2000 个字符，而不是我认为应该粘贴的 2009。我的一些测试脚本也会发生这种情况 - 仅打印 2000 个字符。
I have a workaround to print like this:
我有一个解决方法来打印这样的：
dbms_output.put_line(length(big_str));
dbms_output.put_line(substr(big_str,1,1999));
dbms_output.put_line(substr(big_str,2000));
This adds new lines to the output, makes it hard to read when the text you're working with is preformatted.
这会在输出中添加新行，当您正在使用的文本已预先格式化时，会使其难以阅读。
Has anyone else noticed this? Is it really a bug or some sort of obscure feature? Is there a better workaround? Is there any other information on this out there?
有没有其他人注意到这一点？它真的是一个错误还是某种晦涩的功能？有更好的解决方法吗？有没有其他关于这方面的信息？
Oracle version is: 10.2.0.3.0, using PL/SQL Developer (from Allround Automation).
Oracle 版本为：10.2.0.3.0，使用 PL/SQL Developer（来自 Allround Automation）。
回答by David
This not a limitation to Oracle or put_line, this is a limitation with your IDE.
这不是 Oracle 或 put_line 的限制，这是您的 IDE 的限制。
From the Oracle documentation : http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#ARPLS036
从 Oracle 文档：http: //download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#ARPLS036
Rules and Limits
- The maximum line size is 32767 bytes.
- The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.
规则和限制
- 最大行大小为 32767 字节。
- 默认缓冲区大小为 20000 字节。最小大小为 2000 字节，最大为无限制。
You can test this using SQL*Plus.
您可以使用 SQL*Plus 对此进行测试。
The fact you are seeing 2000 leads me to believe there's a setting in your IDE that's setting it to the minimum.
您看到 2000 的事实使我相信您的 IDE 中存在将其设置为最小值的设置。
回答by Dan
I don't know any better workaround than splitting it like that. To avoid the new line make every call except the last one dbms_output.putinstead of dbms_output.put_line
我不知道比这样拆分它更好的解决方法。为了避免换行，除了最后一个调用之外的每一个调用，dbms_output.put而不是dbms_output.put_line
回答by Erich Kitzmueller
According to TFM, the maximum line size should be 32767. I propose you use DBMS_OUTPUT.GET_LINE to read the long line; if is truncated, it's a bug in DBMS_OUTPUT. If not, it's a bug in PL/SQL Developer.
根据TFM，最大行大小应为 32767。我建议您使用 DBMS_OUTPUT.GET_LINE 读取长行；如果被截断，则是 DBMS_OUTPUT 中的错误。如果没有，则是 PL/SQL Developer 中的错误。
回答by ajdams
Do dbms_output.enable(1000000);
做 dbms_output.enable(1000000);
Then try...
那就试试...
回答by Jeighmz
Your issue sounds like it is related to the Character Encoding Scheme of the Oracle database you are executing your script against.
您的问题听起来好像与您正在执行脚本的 Oracle 数据库的字符编码方案有关。
Oracle supports single-byte character encoding schemes, which means (1 byte) = (1 character), and multibyte character encoding schemes which means (x bytes) = (1 character).
Oracle 支持单字节字符编码方案，即(1 byte) = (1 character)和多字节字符编码方案，即(x bytes) = (1 character)。
The VARCHAR2 datatype semantics is defaulted to BYTES which means that if the default was not changed on the database then VARCHAR2(2009) means the this datatype holds 2009 BYTES.
VARCHAR2 数据类型语义默认为 BYTES，这意味着如果数据库上的默认值未更改，则 VARCHAR2(2009) 表示此数据类型保存 2009 BYTES。
VARCHAR2 in Oracle has a maxlimit of 4000 byteswhich of course means that if your database is using a single-byte character encoding scheme 4000 bytes = 4000 characters. Since your code is only producing 2000 characters it is probably a safe assumption that your database is using a double-byte character encoding scheme.
Oracle 中的 VARCHAR2最大限制为4000 字节，这当然意味着如果您的数据库使用单字节字符编码方案，则 4000 字节 = 4000 个字符。由于您的代码仅生成 2000 个字符，因此可以安全地假设您的数据库使用双字节字符编码方案。
You can read more about this at http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i1835. This link is for the Oracle 11.1 database but I don't think much has changed when it comes to datatypes.
您可以在http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i1835阅读更多相关信息。此链接适用于 Oracle 11.1 数据库，但我认为在数据类型方面没有太大变化。
I hope you find this information helpful.
希望这些信息对您有所帮助。
回答by Philip Schlump
Try setting the linesize in SqlPlus.
尝试在 SqlPlus 中设置 linesize。
set linesize 4000
You may also want to set the pagesize to a lower value.
您可能还想将页面大小设置为较低的值。
