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.
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*PlusYou 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 DeveloperUse 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!