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

• p3consulting: SQL*Plus has a TRIMSPOOL setting that may solve your problem: SET TRIMS[POOL] [ON|OFF]
• Đặng Đình Bằng: excillent. i can help to resolve my problem.

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.

• Đặng Đình Bằng: RFPROF is 6 only. you see $ because i use cat -A. you will see where is end of line.
• Đặng Đình Bằng: linus> cat -A dumpthe.dump 000010263549703,20000,020,6 $ nis101 linus> this is result with your query. if i change like that. set pages 0 set head off set feedback off set echo off set termout 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, $
• Littlefoot: What is RFPROF column's datatype?
• Đặng Đình Bằng: it is number. RFPROF NUMBER(18)
• Littlefoot: OK; I thought that it might be CHAR (which is then right-padded with spaces up to total column length). After all, it seems that it is TRIMSPOOL you need (as @p3consulting said). Note that - if line length was fixed (apparently, it is not) - SET LINESIZE might perform better.

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