Oracle SQL Spool Result file add spaces in columns

Question

I am spooling a big result in a file but the spool add a lot of space between columns : one line is more than 6850 characters (mostly empty space), one column take 500 characters, for instance the first one :

 23053607                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ;

Here is the query :

    spool result.txt;

    set pagesize 0
    set linesize 8000
    SET TRIMSPOOL ON
    set heading off
    set space 0
    set trimout off
    set termout off
    set verify off
    set echo off
    set feed off
    set trimspool on
    -- set echo off;
    -- set termout off;
    set colsep ';'
    set feedback off;
    set pages 0;
    spool result.txt;

    select 
    trim(rec.Code) AS Code,
    trim(A.label) AS Number,
    trim(B.text) AS Syst
    FROM record rec
    join tableA A on A.rec_id = rec.rec_id
    join tableB B on B.kla_id = rec.kla_id;

    spool off;

    exit; 

In the database there is no space. How to avoid any spaces ?

Answer 1

Simplest thing to do is to just concatenate the values. Remove colsep and try out:

SELECT rec.Code ||';'||
       A.label  ||';'||
       B.text   
  FROM record rec
       JOIN tableA A ON A.rec_id = rec.rec_id
       JOIN tableB B ON B.kla_id = rec.kla_id;