oracle 使用 sqlplus 假脱机包含 XMLTYPE 列的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3409546/
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
Spooling a query containing an XMLTYPE column using sqlplus
提问by copaX
I have a query that concatenates (manually by ||) a handful of columns, one of which is an XMLTYPE field (calling getClobVal() on it) . If I spool the query output using sqlplus with the following options set:
我有一个查询（通过 || 手动）连接少数列，其中一个是 XMLTYPE 字段（在其上调用 getClobVal()）。如果我使用带有以下选项集的 sqlplus 假脱机查询输出：
set long 30000;
set pagesize 0;
set feedback off;
I get all of the content as desired, but with cr/lf's after 80 characters, the first line's content stopping after 60 characters with 20 characters of whitespace, and the remaining lines' content stopping after 40 characters with 40 characters of whitespace.
我根据需要获得所有内容，但是在 80 个字符之后使用 cr/lf，第一行的内容在 60 个字符之后停止，其中包含 20 个空格字符，其余行的内容在 40 个字符之后停止，其中包含 40 个空格字符。
If I add:
如果我添加：
set linesize 120;
I get the same results with additional whitespace after the actual content (60 characters of whitespace on line 1, 80 characters of whitespace on remaining lines per row)
我在实际内容之后使用额外的空格得到相同的结果（第 1 行有 60 个空格字符，每行剩余行有 80 个空格字符）
Is it possible to use sqlplus and spooling to create a csv output file for the results of a query containing large amounts of text?
是否可以使用 sqlplus 和假脱机为包含大量文本的查询结果创建 csv 输出文件？
I recognize that doing this by spooling is not the ideal method, but I need to be able to create this csv file in a way that can be executed on the command line or via a batch file.
我认识到通过假脱机执行此操作不是理想的方法，但我需要能够以可在命令行或批处理文件上执行的方式创建此 csv 文件。
Any suggestions?
有什么建议？
回答by APC
We can set linesize to a big number. How big depends on the operating system, although I suspect most OSes will support up to 32K. This is from Windows....
我们可以将 linesize 设置为一个大数字。多大取决于操作系统，尽管我怀疑大多数操作系统最多支持 32K。这是来自Windows....
SQL> set linesize 10000
SQL> set linesize 32767
SQL> set linesize 999999
SP2-0267: linesize option 999999 out of range (1 through 32767)
SQL>
If you have XML text longer than that, well you're out of luck.
如果您有比这更长的 XML 文本，那么您就不走运了。
You don't say why you're using SQL*Plus. Is this because you want the output on a client rather than the database server or because you don't know any other way to write out a file?
您没有说明为什么使用 SQL*Plus。这是因为您想要在客户端而不是数据库服务器上输出，还是因为您不知道写出文件的任何其他方式？
If you can write to the server there are alternatives. UTL_FILE still has a linesize limit of 32767 characters but at least we have do some programmatic wizardry to ensure that the line doesn't get chopped off in mid-tag.
如果您可以写入服务器，则还有其他选择。UTL_FILE 仍然有 32767 个字符的行大小限制，但至少我们做了一些程序化的魔法来确保行不会在中间标签中被切断。
However probably the best solution is to use DBMS_XSLPROCESSOR.CLOB2FILE()which is part of the XMLDB functionality.
然而，最好的解决方案可能是使用DBMS_XSLPROCESSOR.CLOB2FILE()，它是 XMLDB 功能的一部分。
For completeness the related SQL*Plus parameters recommended by copaX's colleague are:
为了完整起见，copaX 的同事推荐的相关 SQL*Plus 参数是：
PAGE- setting this to zero suppresses the headers and prevents page throws in the outputECHO- controls whether the SQL statement is displayed when the script is run
PAGE- 将此设置为零会抑制标题并防止输出中的页面抛出ECHO- 控制在运行脚本时是否显示 SQL 语句
Both PAGE' andECHO` are set to avoid extraneous text in the output. For the same reason the following parameter is often set
两个PAGE' andECHO` 都设置为避免输出中出现无关文本。出于同样的原因，通常设置以下参数
FEEDBACK- controls whether the rowcount is diplayed at the end of the query
FEEDBACK- 控制是否在查询结束时显示行数
LONG- controls the amount of LONG text displayedLONGCHUNKSIZE- controls the amount of LONG text shown before the column wrapsTRIMSPOOL- removes whitespace which rights pads the line to LINESIZEvalue
LONG- 控制显示的 LONG 文本LONGCHUNKSIZE的数量 - 控制在列换行之前显示的 LONG 文本的数量TRIMSPOOL- 删除将行填充到LINESIZE值的 空格
LONGand LONGCHUNKSIZEshoudl be set to the same value to get the whole column displayed on a single output line.
LONG并且LONGCHUNKSIZE应该设置为相同的值以使整个列显示在单个输出行上。
All these paramaters and many more are explained in the documentation.
文档中解释了所有这些参数以及更多参数。
回答by copaX
A coworker who's had more experience with sqlplus came up with the following set block which gave me the outcome I was looking for:
一位对 sqlplus 有更多经验的同事提出了以下设置块，它为我提供了我正在寻找的结果：
set pagesize 0 echo off;
SET LINESIZE 30000 LONG 30000 LONGCHUNKSIZE 30000 Trimspool on;
