Skip to Main Content
  • Questions
  • CSV scripts worked well with 12c but in 19c, results are differents - spaces are added before and after value !


Question and Answer

Chris Saxon

Thanks for the question, Gaetano.

Asked: September 20, 2021 - 12:16 pm UTC

Last updated: September 20, 2021 - 1:01 pm UTC

Version: 19c

Viewed 100+ times

You Asked

Hi all,
It's first time post.
We are facing a problem in 19c when generating a csv file.
In 12c, results were as expected.
Spaces are added after or before the content of the column.
Please find attached the information about the environment.

-- tables structure
Column Name Type TABLE
----------------- -------- -----------------------------

-- conversion date function
return date is cd_date2 date;
cd_date2 := TO_DATE('14/09/1752','DD/MM/YYYY') + abs(cd_date);
return (cd_date2);

-- script
set heading off pagesize 0 linesize 0
set RECSEP off
set verify off

CREATE TABLE inv_pool_prod_tmp as
select distinct dp.code_portefeuille
from descriptif_comp_portefeuille dcp,
     descriptif_portefeuille dp,
     contenu_ensemble_port cep
where dcp.flag_pool <> ' '
and   dcp.code_portefeuille =  dp.code_portefeuille
and   dp.objectif_portefeuille <> ' '
and   cep.code_portefeuille =dp.code_portefeuille
and   cep.code_ensemble_port <> 'OPCLIQ'
group by dp.code_portefeuille
order by dp.code_portefeuille;

spool $GPFETAT/ctrl_nbr_pool_prod.csv
       convertion_date(max(B.HISTO_DATE_VALORISATION)) as premiere_valo
from inv_pool_prod_tmp a,
     gestion_valorisation b
where a.code_portefeuille = b.code_portefeuille
group by b.code_portefeuille
order by b.code_portefeuille;
spool off;

-- as you can see, saces are added after value into first column and before value into second column.
more ctrl_nbr_pool_prod.csv

100801 ; 31-DEC-2014
100804 ; 31-DEC-2014
100805 ; 31-DEC-2014
100806 ; 31-DEC-2014
100809 ; 31-DEC-2014
100810 ; 31-DEC-2014
100811 ; 14-JUN-2016
100812 ; 14-JUN-2016
100813 ; 14-JUN-2016
100814 ; 14-JUN-2016
100815 ; 30-JUN-2016
100816 ; 30-JUN-2016
100817 ; 01-JUN-2017
126401 ; 10-NOV-2017

Thanks in advance for you reply.

with LiveSQL Test Case:

and Chris said...

It'll be down to the formatting settings of your client. But whatever the reason, there are much better ways to spool delimited data these days.

Both SQL*Plus and SQLcl support returning results as delimited text. Just add the appropriate settings to your script


You want the set markup csv command:

SQL> set markup csv on delimiter ; quote on
SQL> set heading off pagesize 0 linesize 1
SQL> set trimspool on
SQL> set verify off
SQL> select codep, cvdate(datev) as p
  2  from   test;

SQLcl/SQL Developer

Use the set sqlformat delimited option:

SQL> set sqlformat delimited ; " "
SQL> set heading off pagesize 0 linesize 1
SQL> set trimspool on
SQL> set verify off
SQL> SELECT codep, cvdate(datev) as p
  2  from   test;


10 rows selected.

While you're at it, ensure you also either:

- Set the NLS_date_format for the session
- TO_CHAR any date/timestamp columns in the output

Otherwise you're at risk of the format of dates changing if you run the script on a different machine!