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

Breadcrumb

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
----------------- -------- -----------------------------
CODE_PORTEFEUILLE CHAR(6) EXPIT.inv_pool_prod_tmp
HISTO_DATE_VALORISATION NUMBER(6) GP2PROD.gestion_valorisation

-- conversion date function
CREATE FUNCTION "GP2PROD"."CONVERTION_DATE" (cd_date number) 
return date is cd_date2 date;
BEGIN
cd_date2 := TO_DATE('14/09/1752','DD/MM/YYYY') + abs(cd_date);
return (cd_date2);
END;


-- script
set heading off pagesize 0 linesize 0
set TRIMSPOOL ON
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
select B.CODE_PORTEFEUILLE, ';',
       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;
quit;


-- 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 we 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

SQL*Plus

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>
SQL> select codep, cvdate(datev) as p
  2  from   test;
"100801";"31-DEC-14"
"100804";"31-DEC-14"
"100805";"31-DEC-14"
"100806";"31-DEC-14"
"100809";"31-DEC-14"
"100810";"31-DEC-14"
"100811";"14-JUN-16"
"100812";"14-JUN-16"
"100813";"14-JUN-16"
"100814";"14-JUN-16"


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>
SQL> SELECT codep, cvdate(datev) as p
  2  from   test;

"100801";31-DEC-14
"100804";31-DEC-14
"100805";31-DEC-14
"100806";31-DEC-14
"100809";31-DEC-14
"100810";31-DEC-14
"100811";14-JUN-16
"100812";14-JUN-16
"100813";14-JUN-16
"100814";14-JUN-16

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!