oracle spool中空格的处理

select语句中直接把所有字段都合并为1个字段；

col f01 format a10000

select f1 || '|^|' || f2 as f01 from table01

把结尾空格去掉；

SET TRIMSPOOL ON

 

注意导出的时候设置中文：

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LANG=en_US.UTF-8

导出示例：

rm output.txt
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LANG=en_US.UTF-8
sqlplus -S user/pass@db01  << EOF >/dev/null 2>&1
SPOOL output.txt
SET COLSEP '|^|'
SET HEADING OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET SERVEROUTPUT OFF
SET LINESIZE 2920
SET PAGESIZE 0
SET ECHO OFF
COL col01 format a5
                           
SELECT col01
          from table01 
         where
                 rownum = 1;
SPOOL OFF
EXIT
EOF