SQLPLUS - ORACLE 12 - Trim extra columns white space

Question

I've a problem with a sqlplus spool (oracle 12c/18c). I want trim extra white space in columns.

This is the expected result

01JHON BROWN  30RED    
02MARIO ROSSI 25WHITE  

this is my result

01    JHON BROWN       30        RED         
02    MARIO ROSSI      25        WHITE    

this is the sql code

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET HEADING OFF
SET PAGESIZE 0 
SET LINESIZE 2000
SET SQLBLANKLINES ON
SET FEEDBACK OFF
SET TIME OFF
SET TIMING OFF
SET COLSEP ''
SET TRIMSPOOL OFF
SET TERMOUT OFF

ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';

spool pippo.txt

SELECT TRIM(NUM), RPAD(NAME,12), TRIM(AGE), RPAD(COLOR,7)
FROM PLUTO;

spool off
exit

THX

Answer 1

That's just how column formatting works with spool - each row's values are padded to the full width of the columns. See this similar question to get an idea of your options.

If you don't want any spaces between column values, you'll generally have to concatenate them into a single column, e.g.

SELECT TRIM(NUM) || RPAD(NAME,12) || TRIM(AGE) || RPAD(COLOR,7)
FROM PLUTO;