Oracle spool to file 中的最大列宽 [英] Max column width in Oracle spool to file

问题描述

我有一个这样的脚本:

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--

输出应该是一个包含行列表的文件，里面有复杂的 xml，但是当生成的 XML 的长度超过 2000 时，SQLPlus 会修剪到 2000 并转到下一行.

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.

有没有办法强制 SQLPlus 将所有数据写入同一行?

There is a way to force SQLPlus to write all the data in the same line?

推荐答案

只需在 SET 命令之后添加以下行:

Just add the following line right after the SET commands:

COL ColumnName FORMAT A32000

其中 ColumnName 是 SELECT 语句中 XML 列的别名(您需要添加别名).

where ColumnName is a the alias for the XML column in your SELECT statement (you'll need to add an alias).

这会设置该列的最大宽度，默认为 2000 个字符.请注意，虽然您可以将 COL FORMAT 设置为高达 60000 个字符，但实际上您在 sqlplus 的一行中最多得到的是 32767，因为这是 LINESIZE 的上限.

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.

这篇关于Oracle spool to file 中的最大列宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！