spool导出数据文件中字段间空白大问题

Question

如何控制导出数据中字段间的大量空白，难道SPOOL不支持吗？查询了一天都没搞定！麻烦哪位人员指点下如何去掉空白，谢谢??什么方法都试了，就是不行！set echo off ;
set pages 0 ;
set feedback off;
set heading off;
set verify off;
set numwidth 5 ;
set linesize 11;
set trims on;  
spool d:\PROD_OFFER_PRIVILEGE.txt  
select replace(PROD_OFFER_PRIV_ID,chr(32),'')||'|',replace(SEQ,chr(32),'')||'|',replace(PRIVILEGE_ID,chr(32),'')||'|' from PROD_OFFER_PRIVILEGE ; 
spool off;
exit如：
a|  22|  3|  --》如何能变成a|22|3|

Answer 1

--实验了一下没有出现你说的问题
[TEST@orcl] SQL>select * from t1;        ID NAME            SCORE
---------- ---------- ----------
         1 张三              100
         2 李四               50
         3 王五               60[TEST@orcl] SQL>desc t1
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(10)
 SCORE                                              NUMBER(10)[TEST@orcl] SQL>set echo off ;
[TEST@orcl] SQL>set pages 0 ;
[TEST@orcl] SQL>set feedback off;
[TEST@orcl] SQL>set heading off;
[TEST@orcl] SQL>set verify off;
[TEST@orcl] SQL>set numwidth 5 ;
[TEST@orcl] SQL>set linesize 11;
[TEST@orcl] SQL>set trims on;
[TEST@orcl] SQL>spool d:\PROD_OFFER_PRIVILEGE.txt
[TEST@orcl] SQL>select replace(id,chr(32),'')||'|'||replace(name,chr(32),'')||'|'||replace(score,chr(32),'')||'|' from t1 ;
1|张三|100|
2|李四|50|
3|王五|60|
[TEST@orcl] SQL>spool off;
[TEST@orcl] SQL>exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options 断开--下面是文件内容
[TEST@orcl] SQL>select replace(id,chr(32),'')||'|'||replace(name,chr(32),'')||'|'||replace(score,chr(32),'')||'|' from t1 ;
1|张三|100|
2|李四|50|
3|王五|60|
[TEST@orcl] SQL>spool off;

Answer 2

SQL> 
SQL> set echo off ;
SQL> set pages 0 ;
SQL> set feedback off;
SQL> set heading off;
SQL> set verify off;
SQL> set numwidth 5 ;
SQL> set linesize 11;
SQL> --set trims on;
SQL> 
SQL> spool d:\PROD_OFFER_PRIVILEGE.txt
Started spooling to d:\PROD_OFFER_PRIVILEGE.txt
SQL> select replace(PROD_OFFER_PRIV_ID,chr(32),'')||'|',replace(SEQ,chr(32),'')||'|',replace(PRIVILEGE_ID,chr(32),'')||'|' from PROD_OFFER_PRIVILEGE ;
1040|                                                                            0|                                                                               345|
                                                                  347|
1404|                                                                            0|                                                                               343|
1405|                                                                            0|                                                                               344|
1381|                                                                            0|                                                                               345|
1501|                                                                            0|                                                                               358|
1502|                                                                            0|                                                                               358|
1520|                                                                            0|                                                                               358|
1527|                                                                            0|                                                                               358|
1524|                                                                            0|                                                                               358|
1526|                                                                            0|                                                                               347|
SQL> spool off;
Stopped spooling to d:\PROD_OFFER_PRIVILEGE.txt
 
SQL> desc PROD_OFFER_PRIVILEGE
Name               Type        Nullable Default Comments 
------------------ ----------- -------- ------- -------- 
PROD_OFFER_PRIV_ID NUMBER(9)                             
SEQ                NUMBER(3)                             
PRIVILEGE_ID       NUMBER(9)   Y  -------------我这个不行呢，你那个真不会出现空格呀