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 ?

You may use SQLcl (SQL Developer command line) to simplyfy creation of CSV by specifying set sqlformat csv or by using /*+csv*/ hint. See the reference — astentx
– astentx, Commented Sep 2, 2022 at 7:32

Gnqz · Accepted Answer · 2022-09-02 10:34:42Z

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;

edited Sep 2, 2022 at 10:34

answered Sep 2, 2022 at 7:30

Gnqz

3,3823 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Benoît S Over a year ago

I get the error : ORA-00923: FROM keyword not found where expected

Gnqz Over a year ago

It's because of the AS clauses. IT's considered a single column. Try the adjusted. When you're spooling you will need to make a select for the header row.

Benoît S Over a year ago

Thanks you , but can I use aliases without "AS" ? rec.Code Code ||';'|| A.label number ||';'||

Gnqz Over a year ago

You can just do a dummy select from dual with the lines you want in your header: SELECT 'Code;Number;Syst' FROM dual, you already have SET HEAD OFF`. It solves the issue with the header, and there is no need to make aliases for the columns that way. It's all about what you have in the spool.

Collectives™ on Stack Overflow

Oracle SQL Spool Result file add spaces in columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related