space in the export file Oracle SQL

Question

i have 1 SQL file as below:

set pages 0
set head off
set feedback off
set echo off
set termout off
set wrap off
spool /osp/tmp/bangdd/dumpthe.dump
select a.SERNUM ||','|| b.AMOUNT||','|| b.TOPPF||','|| a.RFPROF from voucher a, SCRATCHP b where b.ri=a.RFPROF and a.sernum='000010263549703';
spool off

the out put file with cat -A as below.

linus> cat -A dumpthe.dump
000010263549703,20000,020,6                                                     $

you can see, there are many space from 6 to $. it make file size is lager than normal. i can use sed command to remove space in the file but i want to know if any option in Oracle SQL can help that?

when i change my query as below

set pages 0
set head off
set feedback off
set echo off
set termout off
set wrap off
spool /osp/tmp/bangdd/dumpthe.dump
select a.SERNUM ||','|| b.AMOUNT||','|| b.TOPPF||','|| a.RFPROF || ',' from voucher a, SCRATCHP b where b.ri=a.RFPROF and a.sernum='000010263549703';
spool off

the result same as below:

linus> cat -A dumpthe.dump
000010263549703,20000,020,6,                                                    $

Answer 1

According to query you posted:

• 000010263549703 is SERNUM column's value
• 20000 is AMOUNT
• 020 is TOPF
• 6 $ is RFPROF

There's no evidence that query concatenates $ at the end of the output string.

Therefore, you got exactly what you have in that column. Someone put bunch of spaces between 6 and $. Can you verify that by querying that column only?

If I'm right, and you want to get rid of spaces, replace them with an empty string, e.g.

select a.SERNUM ||','|| b.AMOUNT||','|| b.TOPPF||','|| 
--
replace(a.RFPROF, ' ', '')             --> this
--
from voucher a, SCRATCHP b 
where b.ri=a.RFPROF and a.sernum='000010263549703';

If I'm wrong, please, post sample data that illustrate that problem so that we could try to recreate it and see what happens.

Answer 2

after update "set trimspool off" to "set trimspool on". it can help.

set pages 0
set head off
set feedback off
set echo off
set termout off
set trimspool on
spool /osp/tmp/bangdd/dumpthe.dump
select a.SERNUM ||','|| b.AMOUNT||','|| b.TOPPF||','|| a.RFPROF || ',' from voucher a, SCRATCHP b where b.ri=a.RFPROF and a.sernum='000010263549703';
spool off
exit

result:

cat -A dumpthe.dump
000010263549703,20000,020,6,$