使用 sqlplus 假脱机包含 XMLTYPE 列的查询
我有一个查询（通过 || 手动连接）一些列，其中之一是 XMLTYPE 字段（在其上调用 getClobVal() ）。如果我使用 sqlplus 并设置以下选项来假脱机查询输出：
set long 30000;
set pagesize 0;
set feedback off;
我会根据需要获取所有内容，但 cr/lf 在 80 个字符后，第一行的内容在 60 个字符后停止，并有 20 个空格字符，其余行' 内容在 40 个字符后停止，并有 40 个空格字符。
如果我添加：
set linesize 120;
我在实际内容之后得到相同的结果，并带有额外的空格（第 1 行上有 60 个空格字符，每行剩余行上有 80 个空格字符）
是否可以使用 sqlplus 和假脱机来创建 csv 输出文件包含大量文本的查询结果？
我认识到通过假脱机执行此操作并不是理想的方法，但我需要能够以可以在命令行或批处理文件上执行的方式创建此 csv 文件。
有什么建议吗？
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:
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.
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)
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?
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.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一位对 sqlplus 有更多经验的同事提出了以下设置块，它给了我我正在寻找的结果：
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:
我们可以将 linesize 设置为一个很大的数字。有多大取决于操作系统，尽管我怀疑大多数操作系统最多支持 32K。这是来自 Windows...
如果您的 XML 文本比这个长，那么您就不走运了。
您没有说明为什么使用 SQL*Plus。这是因为您希望在客户端而不是数据库服务器上输出，还是因为您不知道写出文件的任何其他方法？
如果您可以写入服务器，还有其他选择。 UTL_FILE 仍然有 32767 个字符的行大小限制，但至少我们做了一些编程魔法来确保该行不会在中间标记中被截断。
然而，最好的解决方案可能是使用 DBMS_XSLPROCESSOR。 CLOB2FILE() 这是 XMLDB 功能的一部分。
为了完整起见，copaX 同事推荐的相关 SQL*Plus 参数是：
PAGE- 将其设置为零会抑制标头并防止输出中出现页面抛出ECHO- 控制脚本运行时是否显示 SQL 语句PAGE' 和ECHO` 均设置为避免输出中出现无关文本。出于同样的原因，经常设置以下参数FEEDBACK- 控制是否在查询结束时显示行计数LONG- 控制显示的LONG文本数量LONGCHUNKSIZE- 控制列换行之前显示的 LONG 文本数量TRIMSPOOL- 删除空格，将行填充为LINESIZE值LONG和LONGCHUNKSIZE应设置为相同的值使整个列显示在单个输出行上。所有这些参数以及更多参数均在文档中进行了解释< /a>.
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....
If you have XML text longer than that, well you're out of luck.
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?
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.
However probably the best solution is to use DBMS_XSLPROCESSOR.CLOB2FILE() which is part of the XMLDB functionality.
For completeness the related SQL*Plus parameters recommended by copaX's colleague are:
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 runBoth
PAGE' andECHO` are set to avoid extraneous text in the output. For the same reason the following parameter is often setFEEDBACK- controls whether the rowcount is diplayed at the end of the queryLONG- 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 toLINESIZEvalueLONGandLONGCHUNKSIZEshoudl be set to the same value to get the whole column displayed on a single output line.All these paramaters and many more are explained in the documentation.