SQLPLUS spool导出CSV时去除行间空行及末尾多余空格方法

问题概述 #

使用SQLPLUS 11.2.0.4执行查询、通过spool导出CSV文件时存在两类顽固异常：

• 每行数据后自动生成多余空行
• varchar2(4000)类型的最后一列末尾被追加大量空格
  不同环境下的异常表现：
• Windows环境用Notepad++打开文件，可见行尾大量空格后接CRLF换行符
• Linux环境下打开会出现长字段被强制截断换行、行间插入空行的问题
  此前已尝试调整trimspool、trimout、pagesize 0、heading off、newpage none、space 0、linesize 8000、longchunksize等参数，均未解决问题。

约束要求 #

• 必须使用SQLPLUS工具完成导出
• 导出文件不得包含双引号
• 字段分隔符使用波浪号~
• 不得截断行内任何字符，需移除所有空行与行尾多余空格，输出格式规整的文件

原有问题脚本 #

set termout off
set pagesize 0
set termout off
set pagesize 0
set heading off
set feedback off
set newpage none
set space 0
set linesize 8000
set longchunksize 200000 
/*above was tried step by step - no help*/

spool "G:/gggg/fffff.csv"
PROMT COL1|COL2|COL3

select col1||';'||col2||';'||nvl(col3,'') abc
FROM transactions;
spool off;

预期输出格式 #

aaaa~bbbb~cccc~eeeeeeeeeeeeeeeeeeeeeeeeee
dddd~rrrr~bggggggg~rrrrrrrrrrrrrrrrrrrrrrrrrr
eeee~rrrrrrr~ttttttt~yyyyyyyyyyyyyyyyyyyyyyyyyy

涉及字段类型：col1为integer类型，col2、col3为varchar2(4000 BYTE)类型

修复方案 #

1. 补全核心参数配置 #

原有参数缺失两个关键控制项，且部分参数值设置不合理：

• set recsep off：关闭SQLPLUS默认的记录分隔符输出，从根源消除行间多余空行
• set trimspool on/set trimout on：开启行尾空格截断，注意两个参数必须同时开启
• set linesize 32767：将行宽设置为11g支持的最大值，避免长字段被强制换行，原8000的设置无法覆盖两个varchar2(4000)字段加分隔符的总长度
• 补充set tab off、set echo off、set verify off、set long 32767，屏蔽冗余输出、避免自动转义、支持长字段完整输出
  完整参数配置如下：

set echo off
set termout off
set verify off
set heading off
set feedback off
set newpage none
set pagesize 0
set space 0
set tab off
set recsep off
set trimspool on
set trimout on
set linesize 32767
set long 32767
set longchunksize 32767

2. 修正查询与输出逻辑 #

原有脚本存在拼写错误、分隔符不匹配、长字段未显式去空格的问题：

• 修正拼写错误：PROMT改为PROMPT，表头分隔符统一替换为要求的~
• 字段拼接时分隔符统一使用~，所有字符类型字段用rtrim()显式移除尾部空格——11.2.0.4版本存在trimspool对末尾长varchar2字段不生效的已知问题，显式截断最稳妥
• spool结束后加exit，避免SQLPLUS退出时额外输出空行
  修正后的spool执行段：

spool "G:/gggg/fffff.csv"
PROMPT COL1~COL2~COL3

select col1||'~'||rtrim(col2)||'~'||rtrim(col3) abc
FROM transactions;

spool off
exit

3. 调用注意事项 #

执行脚本时使用SQLPLUS静默模式调用，避免客户端缓冲区导致的自动换行，调用命令示例：

sqlplus -s 用户名/密码@连接串 @导出脚本路径.sql

不要对拼接后的输出字段设置col 字段名 format axxx格式，否则会强制按照设定宽度补全空格。

内容的提问来源于stack exchange，提问作者Matt