Oracle spool to file 中的最大列宽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4156527/
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
Max column width in Oracle spool to file
提问by gabriele.lb
I have a script like this:
我有一个这样的脚本：
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET WRAP OFF
SET LINESIZE 32000
SET LONG 32000
SET LONGCHUNKSIZE 32000
SET SERVEROUT ON
SPOOL C:\Export.txt
SELECT XMLELEMENT("element1",xmlelement("element2",xmlattributes(.....)))
FROM --TABLENAME--
WHERE --CONDITIONS--
The output should be a file containing a list of rows with the complex xml inside, but when the length of the XML generated is longer than 2000, SQLPlus trims to 2000 and go to the next line.
输出应该是一个包含复杂 xml 行列表的文件，但是当生成的 XML 的长度超过 2000 时，SQLPlus 会修剪为 2000 并转到下一行。
There is a way to force SQLPlus to write all the data in the same line?
有没有办法强制 SQLPlus 将所有数据写入同一行？
回答by MTS
Just add the following line right after the SET commands:
只需在 SET 命令之后添加以下行：
COL ColumnName FORMAT A32000
where ColumnName is a the alias for the XML column in your SELECT statement (you'll need to add an alias).
其中 ColumnName 是 SELECT 语句中 XML 列的别名（您需要添加别名）。
This sets the max width for that column, which is 2000 characters by default. Note that while you can set COL FORMAT as high as 60000 characters, the most you will actually ever get on one line with sqlplus is 32767, as this is the upper limit for LINESIZE.
这将设置该列的最大宽度，默认为 2000 个字符。请注意，虽然您可以将 COL FORMAT 设置为高达 60000 个字符，但实际上您在 sqlplus 的一行中得到的最多是32767，因为这是 LINESIZE 的上限。
回答by technomalogical
Are you on Windows? I was having this same problem and neither of the other two answers helped me (directly, I had to do one more thing). Following the advice of this article on setting up SQL*Plus for Windowsthe author notes:
你在 Windows 上吗？我遇到了同样的问题，其他两个答案都没有帮助我（直接，我不得不再做一件事）。按照本文关于为 Windows 设置 SQL*Plus的建议，作者指出：
[note3]
Set long big_numberso you can see the definition of a complicated trigger or view, or text in any long or clob column.
[note3]
Set long big_number这样您就可以在任何 long 或 clob 列中查看复杂触发器或视图的定义或文本。
I set mine as SET LONG 32000(my longest line was a little over 2000 characters) and that solved the problem for me.
我将我的设置为SET LONG 32000（我最长的一行超过 2000 个字符），这为我解决了问题。
回答by MrDBA
How about using getClobVal() to convert the output to a clob?
如何使用 getClobVal() 将输出转换为 Clob？
SELECT XMLELEMENT("element1",xmlelement("element2",xmlattributes(.....))).getClobVal()
FROM --TABLENAME--
WHERE --CONDITIONS--
